1樓：匿名使用者
create table 庫存表 as select b.品名 as 品名,a.suma as 出數量,b.
sumb as 進數量,(b.sumb-a.suma) as 庫存 from (select 品名,sum(數量) as suma from 出貨表 group by 品名) as a right join (select 品名,sum(數量) as sumb from 進貨表 group by 品名) as b on a.
品名=b.品名;
直接執行上面的sql語句，就可以獲得新表：庫存表。
2樓：我tm不管
select 品名,sum(出數量) as 出數量,sum(進數量) as 進數量,sum(進數量)-sum(出數量) as 庫存 into 庫存表
(select 品名,sum(數量) as 出數量,0 as 進數量 from 出貨表 group by 品名
union all
select 品名,0 as 出數量,sum(數量) as 進數量 from 進貨表 group by 品名
) a group by 品名
上面是sqlserver的語法：
careate table 庫存表 as
select 品名,sum(出數量) as 出數量,sum(進數量) as 進數量,sum(進數量)-sum(出數量) as 庫存
(select 品名,sum(數量) as 出數量,0 as 進數量 from 出貨表 group by 品名
union all
select 品名,0 as 出數量,sum(數量) as 進數量 from 進貨表 group by 品名
) a group by 品名
上面是oracle的語法
3樓：匿名使用者
你的庫存表是已經存在的嗎？如果不是要這樣寫 database.dbo.into 庫存表 就可以新建一個庫存表並插入資料了
select b.品名 as 品名,a.suma as 出數量,b.sumb as 進數量,(b.sumb-a.suma) as 庫存
into databasename.dbo.into 庫存表 from (select 品名,sum(數量) as suma from 出貨表 group by 品名) as a right join (select 品名,sum(數量) as sumb from 進貨表
group by 品名) as b on a.品名=b.品名測試過了，在sqlserver 2005裡結果和你想要的一樣。
4樓：匿名使用者
select a.品名,a.出數量 ,b.進數量,(a.出數量-b.進數量) as 庫存
from
(select 品名, sum(數量) as 出數量from 出貨表
group by 品名) a,
(select 品名, sum(數量) as 進數量from 進貨表
group by 品名) b
where a.品名 = b.品名
SQL連線查詢，sql 連線查詢跟多表查詢的區別
select a.name,substr max sys connect by path b.definition,2 prize from a,b,select rn,prize id,decode rn,1,0,instr prize,1,rn 1 1 sp,decode instr prize...
關於sql查詢，想從很多表中查詢欄位值
select q.條碼 case when isnull a.a站點,then t else f end 是否經過a站點 case when isnull b.b站點,then t else f end 是否經過b站點 from 條碼錶 q left join a a on a.條碼 q.條碼lef...
sql連線查詢跟多表查詢的區別
這倆沒區別 就是寫法不同 這個就如同表1裡有a,b倆欄位一樣 select a,b from 表1 select from 表1 是一樣的 順便給你擴充套件下吧 在資料庫裡還有left join,right join,full join等 當這樣的時候用 select from table1,tab...