表1:品名 入库数
a 200
b 90
表2:品名 出库数
a 100
a 70
c 60结果:品名 入库数 出库数 余数
a 200 100
a 0 70
合计 a 200 170 30
b 90 0
合计 b 90 0 90
c 0 60
合计 c 0 60 -60要实现以上功能,如何写语句呢?希望大家给予指导,感谢!
a 200
b 90
表2:品名 出库数
a 100
a 70
c 60结果:品名 入库数 出库数 余数
a 200 100
a 0 70
合计 a 200 170 30
b 90 0
合计 b 90 0 90
c 0 60
合计 c 0 60 -60要实现以上功能,如何写语句呢?希望大家给予指导,感谢!
-->生成测试数据
declare @tb table([品名] nvarchar(1),[入库数] int)
Insert @tb
select N'a',200 union all
select N'b',90
--Select * from @tbdeclare @tbB table([品名] nvarchar(1),[出库数] int)
Insert @tbB
select N'a',100 union all
select N'a',70 union all
select N'c',60
--Select * from @tbB
select * from (
select isnull(a.[品名],b.[品名]) as [品名],
isnull(a.[入库数],0) as [入库数],
isnull(b.[出库数],0) as [出库数] from @tb a
full join @tbB b on b.[品名] = a.[品名]
union all
select * from
(
select isnull(a.[品名],b.[品名]) as [品名],
isnull(a.[入库数],0) as [入库数],
isnull(b.[出库数],0) as [出库数]
from @tb a
full join (select [品名],sum([出库数]) as [出库数] from @tbB group by [品名]) b on b.[品名] = a.[品名]
) t
) t2
order by 1
/*
品名 入库数 出库数
---- ----------- -----------
a 200 100
a 200 70
a 200 170
b 90 0
b 90 0
c 0 60
c 0 60
*/
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int identity(1,1),品名 varchar(1),入库数 int)
Go
Insert into ta(品名 ,入库数)
select 'a',200 union all
select 'b',90
Go
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(id int identity(1,1),品名 varchar(1),出库数 int)
Go
Insert into tb(品名 ,出库数)
select 'a',100 union all
select 'a',70 union all
select 'c',60
Go
--Start
select case when grouping(p) = 0 and grouping(px) = 1 then '合计 '+ p else p end as p,sum(r) as r,sum(c) as c
,case when grouping(p) = 0 and grouping(px) = 1 then sum(y) else 0 end as y
from (
select isnull(b.px,c.px) as px,isnull(b.品名,c.品名) as p,
sum(isnull(b.入库数,0)) as r ,sum(isnull(c.出库数,0)) as c, y = sum(isnull(b.入库数,0)) -sum(isnull(c.出库数,0))
from (
Select px = (select count(1) from ta where a.品名 = 品名 and id < = a.id),*
from ta a) b full join(
Select px = (select count(1) from tb where a.品名 = 品名 and id < = a.id),*
from tb a) c on b.品名 = c.品名 and c.px = b.px
group by b.px,c.px,b.品名,c.品名) d
group by p,px
with rollup
having (grouping(p) = 0)
order by replace(p,'合计 ','')
--Result:
/*
p r c y
------ ----------- ----------- -----------
a 200 100 0
a 0 70 0
合计 a 200 170 30
b 90 0 0
合计 b 90 0 90
c 0 60 0
合计 c 0 60 -60(所影响的行数为 7 行)*/
--End
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(id int identity(1,1),品名 varchar(1),入库数 int)
Go
Insert into ta(品名 ,入库数)
select 'a',200 union all
select 'b',90 union all
select 'd',190
Go
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(id int identity(1,1),品名 varchar(1),出库数 int)
Go
Insert into tb(品名 ,出库数)
select 'a',100 union all
select 'a',70 union all
select 'c',60 union all
select 'd',60 union all
select 'e',60
Go
--Start
select case when grouping(p) = 0 and grouping(px) = 1 then '合计 '+ p else p end as p,sum(r) as r,sum(c) as c
,case when grouping(p) = 0 and grouping(px) = 1 then sum(y) else 0 end as y
from (
select isnull(b.px,c.px) as px,isnull(b.品名,c.品名) as p,
sum(isnull(b.入库数,0)) as r ,sum(isnull(c.出库数,0)) as c, y = sum(isnull(b.入库数,0)) -sum(isnull(c.出库数,0))
from (
Select px = (select count(1) from ta where a.品名 = 品名 and id < = a.id),*
from ta a) b full join(
Select px = (select count(1) from tb where a.品名 = 品名 and id < = a.id),*
from tb a) c on b.品名 = c.品名 and c.px = b.px
group by b.px,c.px,b.品名,c.品名) d
group by p,px
with rollup
having (grouping(p) = 0)
order by replace(p,'合计 ','')
--Result:
/*
p r c y
------ ----------- ----------- -----------
a 200 100 0
a 0 70 0
合计 a 200 170 30
b 90 0 0
合计 b 90 0 90
c 0 60 0
合计 c 0 60 -60
d 190 60 0
合计 d 190 60 130
e 0 60 0
合计 e 0 60 -60(所影响的行数为 11 行)*/
--End
但是你给出的结构只有品名字段,而没有本品的出入库次数,
你应该给出类似于下边的结构:
表1:
品名 入库数 入库次数
a 200 1
b 90 1
表2:
品名 出库数 出库次数
a 100 1
a 70 2
c 60 1
若是这种结构的话,用下边的语句
select
case when grouping (c.cs)=1 then
(case when grouping(c.pm)=1 then
'合计'
else
'合计'+ c.pm
end )
else
c.pm
end 品名,
sum(c.rk) 入库量,
sum(c.ck) 出库量,
case when grouping (c.cs)=1 then
sum(c.rk)-sum(c.ck)
else
0
end 余额
from (select
isnull(a.品名,isnull(b.品名,'')) pm,
isnull(a.入库量,0) rk,
isnull(b.出库量,0) ck,
isnull(a.入库次数,isnull(b.出库次数,0)) cs
from
表1 a
FULL OUTER join 表2 b
on (a.品名=b.品名 and a.入库次数=b.出库次数)
) c
group by
c.pm,c.cs
with rollup若实在没有,就用存储过程吧,
把你的数据插入到类似于我给你的结构的临时表中,在用上边的语句查询,返回查询结果