汇总？请大家帮助。

Question

代码表：Depository_organize
organize_code  organize_name  organize_spec
1001001        刀库           BT-OTF
1001002        刀库           BT-OTE
1001002        油石           B12
入库表：Goods_lairage
lairage_time lairage_code  lairage_name  lairage_paiclamount  lairage_money
2007-07-08    1001001      刀库          10                   100
2007-07-08    1001002      刀库          30                   600
出库表：Goods_Leave_lairage
lairage_time lairage_code  lairage_name  lairage_paiclamount  lairage_money
2007-07-08    1001001      刀库          10                   100
2007-07-08    1001002      刀库          15                   300
求：代码表中各个产品：2007-07-08这一天 入库数量，出库数量，剩余数量
结果为：
代码           名称   型号    入库数量   出库数量  剩余数量
1001001        刀库   BT-OTF   10        10        0       
1001002        刀库   BT-OTE   30        15        15         
1001002        油石   B12      0         0         0       

Answer 1

SELECT 
a.organize_code as 代码,
a.organize_name as 名称,
a.organize_spec as 型号,
入库数量 = b.lairage_paiclamount,
出库数量 = c.lairage_paiclamount,
剩余数量 = b.lairage_paiclamount - c.lairage_paiclamount
FROM Depository_organize AS a
LEFT JOIN 
(select lairage_code,sum(lairage_paiclamount) as lairage_paiclamount 
from Goods_lairage where lairage_time = '2007-07-08' group by lairage_code)AS b 
ON a.organize_code = b.lairage_code
LEFT JOIN
(select lairage_code,sum(lairage_paiclamount) as lairage_paiclamount 
from Goods_Leave_lairage where lairage_time = '2007-07-08' group by lairage_code)AS c
ON a.organize_code = c.lairage_code

Answer 2

最好加上isnull做判斷SELECT 
a.organize_code as 代码,
a.organize_name as 名称,
a.organize_spec as 型号,
入库数量 = IsNull(b.lairage_paiclamount, 0),
出库数量 = IsNull(c.lairage_paiclamount, 0), 
剩余数量 = IsNull(b.lairage_paiclamount, 0) - IsNull(c.lairage_paiclamount, 0)
FROM Depository_organize AS a
LEFT JOIN 
(select lairage_code,sum(lairage_paiclamount) as lairage_paiclamount 
from Goods_lairage where lairage_time = '2007-07-08' group by lairage_code)AS b 
ON a.organize_code = b.lairage_code
LEFT JOIN
(select lairage_code,sum(lairage_paiclamount) as lairage_paiclamount 
from Goods_Leave_lairage where lairage_time = '2007-07-08' group by lairage_code)AS c
ON a.organize_code = c.lairage_code

Answer 3

--建立测试环境
create table depository_organize(organize_code int,organize_name varchar(10),organize_spec varchar(10))
insert depository_organize(organize_code,organize_name,organize_spec)
select '1001001','刀库','bt-otf' union all
select '1001002','刀库','bt-ote' union all
select '1001002','油石','b12'
go
create table goods_lairage(lairage_time smalldatetime,lairage_code int,lairage_name varchar(10),lairage_paiclamount int,lairage_money int)
insert goods_lairage(lairage_time,lairage_code,lairage_name,lairage_paiclamount,lairage_money)
select '2007-07-08','1001001','刀库','10','100' union all
select '2007-07-08','1001002','刀库','30','600'
go
create table goods_leave_lairage(lairage_time smalldatetime,lairage_code int,lairage_name varchar(10),lairage_paiclamount int,lairage_money int)
insert goods_leave_lairage(lairage_time,lairage_code,lairage_name,lairage_paiclamount,lairage_money)
select '2007-07-08','1001001','刀库','10','100' union all
select '2007-07-08','1001002','刀库','15','300'
go
--执行测试语句
select a.organize_code as 代码,a.organize_name as 名称,a.organize_spec as 型号
      ,isnull(b.lairage_paiclamount, 0) as 入库数量
      ,isnull(c.lairage_paiclamount, 0)as 出库数量 
      ,isnull(b.lairage_paiclamount, 0) - isnull(c.lairage_paiclamount, 0)as 剩余数量
from depository_organize as a
left join (
      select lairage_code,sum(lairage_paiclamount) as lairage_paiclamount 
      from goods_lairage 
      where lairage_time = '2007-07-08' group by lairage_code
) b on a.organize_code = b.lairage_code
left join (
      select lairage_code,sum(lairage_paiclamount) as lairage_paiclamount 
      from goods_leave_lairage 
      where lairage_time = '2007-07-08' group by lairage_code
) c on a.organize_code = c.lairage_code
go
--删除测试环境
drop table depository_organize,goods_lairage,goods_leave_lairage
go
/*--测试结果
代码          名称         型号         入库数量        出库数量        剩余数量        
----------- ---------- ---------- ----------- ----------- ----------- 
1001001     刀库         bt-otf     10          10          0
1001002     刀库         bt-ote     30          15          15
1001002     油石         b12        30          15          15(3 row(s) affected)*/

Answer 4

--借用楼上兄台mengmou()mengmou() 数据,另代码有重复,改为10011003.
create table depository_organize(organize_code int,organize_name varchar(10),organize_spec varchar(10))
insert depository_organize(organize_code,organize_name,organize_spec)
select '1001001','刀库','bt-otf' union all
select '1001002','刀库','bt-ote' union all
select '1001003','油石','b12'
go
create table goods_lairage(lairage_time smalldatetime,lairage_code int,lairage_name varchar(10),lairage_paiclamount int,lairage_money int)
insert goods_lairage(lairage_time,lairage_code,lairage_name,lairage_paiclamount,lairage_money)
select '2007-07-08','1001001','刀库','10','100' union all
select '2007-07-08','1001002','刀库','30','600'
go
create table goods_leave_lairage(lairage_time smalldatetime,lairage_code int,lairage_name varchar(10),lairage_paiclamount int,lairage_money int)
insert goods_leave_lairage(lairage_time,lairage_code,lairage_name,lairage_paiclamount,lairage_money)
select '2007-07-08','1001001','刀库','10','100' union all
select '2007-07-08','1001002','刀库','15','300'
goselect organize_code 代码, organize_name 名称,organize_spec 型号,isnull(t1.lairage_paiclamount , 0) 入库数量,isnull(t2.lairage_paiclamount , 0) 出库数量,isnull(t1.lairage_paiclamount , 0) - isnull(t2.lairage_paiclamount , 0) 剩余数量 from depository_organize
left join
(
  select lairage_code, sum(lairage_paiclamount) lairage_paiclamount from goods_lairage where lairage_time = '2007-07-08' group by lairage_code
) t1 on depository_organize.organize_code = t1.lairage_code
left join 
(
  select lairage_code, sum(lairage_paiclamount) lairage_paiclamount from goods_leave_lairage where lairage_time = '2007-07-08' group by lairage_code
) t2 on depository_organize.organize_code = t2.lairage_codedrop table depository_organize,goods_lairage,goods_leave_lairage
/*
代码        名称       型号       入库数量    出库数量    剩余数量        
----------- ---------- ---------- ----------- ----------- ----------- 
1001001     刀库       bt-otf     10          10          0
1001002     刀库       bt-ote     30          15          15
1001003     油石       b12        0           0           0（所影响的行数为 3 行）
*/