MySQL从两个现有的表计算中创建一个表

Question

I'm making a php web page with Yii and MySQL. My problem is in database part.

I have two tables, "stock" and "given". They are like:

stock = id (primary key), name, amount (this is integer), details

and

given = id (from stock), name (from stock), amount (given amount, thus not same with stock), ...

I want to create a table "leftStock" which is the same format with "stock" but has different amount. It will be the number left in stock after given some. Since in different times, different amounts of the same item may be given, the given table's id and name is not unique. Thus i'll use "sum(given.amount)" but its not definite for me.

I thought i can use "create table...select" style coding but i could not construct its structure. Can anybody help me?

(Also can i use "view" in a Yii web page? Because i won't make any direct change on "leftStock" table, its being view may be feasible either.)

Answer 1

It should be done by creating a query too.

Try this, I dont test it yet:

SELECT 
    (SELECT SUM(a.amount - b.amount) 
    FROM stock AS a, given AS b 
    WHERE a.id = b.id AND a.id = id) AS `itemleft`, *
FROM stock