SELECT t1.编号,t1.名称,isnull(t1.进,0)-isnull(t2.出,0) as 库存
FROM
(select 编号,名称,sum(进) as 进 from inventory group by 编号,名称) t1
left join
(select 编号,sum(出) as 出 from inventory group by 编号) t2 on t1.编号=t2.编号表编号 名称 进 出
1 A 1 0
1 A 2 0
1 A 0 1
2 B 1 0想得到这个汇总的结果编号 名称 进 出 库存
1 A 3 1 2
2 B 1 0 1上面那个SQL语句不行,老是有同样的一行多出来了高分在线等!!
FROM
(select 编号,名称,sum(进) as 进 from inventory group by 编号,名称) t1
left join
(select 编号,sum(出) as 出 from inventory group by 编号) t2 on t1.编号=t2.编号表编号 名称 进 出
1 A 1 0
1 A 2 0
1 A 0 1
2 B 1 0想得到这个汇总的结果编号 名称 进 出 库存
1 A 3 1 2
2 B 1 0 1上面那个SQL语句不行,老是有同样的一行多出来了高分在线等!!
from tb group by 编号,名称
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([编号] int,[名称] nvarchar(1),[进] int,[出] int)
Insert #A
select 1,N'A',1,0 union all
select 1,N'A',2,0 union all
select 1,N'A',0,1 union all
select 2,N'B',1,0
Go
select 编号,名称,sum(进) as 进,sum(出) AS 出,sum(进)-sum(出) AS 庫存 from #A group by 编号,名称/*
编号 名称 进 出 庫存
1 A 3 1 2
2 B 1 0 1
*/
编号,名称,
sum(进) as 进,sum(出) AS 出,
isnull(sum(进),0)-isnull(sum(出),0) as 库存
from
inventory
group by
编号,名称
insert into tb select 1,'A',1, 0
insert into tb select 1,'A',2,0
insert into tb select 1,'A',0,1
insert into tb select 2,'B',1,0
go
select 编号,名称,SUM(进) as 进,SUM(出) as 出,SUM(进)-SUM(出) as 库存 from tb group by 编号,名称
/*
编号 名称 进 出 库存
----------- ---------- ----------- ----------- -----------
1 A 3 1 2
2 B 1 0 1(2 行受影响)*/
go
drop table tb
FROM
(select 编号,名称,sum(进) as 进 from inventory group by 编号,名称) t1
left join
(select 编号,sum(出) as 出 from inventory group by 编号) t2 on t1.编号=t2.编号
group by t1.编号,t1.名称
这样试试
group by 编号,名称有时我的名称改变了,比如
编号 名称 进 出
1 A 1 0
1 A1 2 0
1 A 0 1
2 B 1 0这样就多了两行出来了,但实际A1,和A是同一个GROUP
from tb group by 编号