有table1,table2,table3,table4,table5 5个表。
每个表的结构相同,但是数据不同
Table1(期初库存):
Item Month Qty1(期初库存)
A 1 20
A 2 35
B 1 10
Table2(期末库存):
Item Month Qty2(期末库存)
B 1 5
C 1 10
C 2 1Table3(期间作过移动库存):
Item Month Qty3(期间移动库存)
B 1 5
E 1 10
F 2 13
table4和table5 类似
希望得到的结果是
Item Month Qty1 Qty2 Qty3 Qty4 Qty5
A 1 20 0 0
A 2 35 0 0
B 1 10 5 5
C 1 0 10 0
C 2 0 1 0
E 1 0 0 10
F 2 0 0 13
数值为0的地方为null也行
每个表的结构相同,但是数据不同
Table1(期初库存):
Item Month Qty1(期初库存)
A 1 20
A 2 35
B 1 10
Table2(期末库存):
Item Month Qty2(期末库存)
B 1 5
C 1 10
C 2 1Table3(期间作过移动库存):
Item Month Qty3(期间移动库存)
B 1 5
E 1 10
F 2 13
table4和table5 类似
希望得到的结果是
Item Month Qty1 Qty2 Qty3 Qty4 Qty5
A 1 20 0 0
A 2 35 0 0
B 1 10 5 5
C 1 0 10 0
C 2 0 1 0
E 1 0 0 10
F 2 0 0 13
数值为0的地方为null也行
insert into @t select 'A',1,20
union all select 'A',2,35
union all select 'B',1,10declare @a table(Item varchar(10),[Month] int,Qty2 int)
insert into @a select 'B',1,5
union all select 'C',1,10
union all select 'C',2,1declare @b table(Item varchar(10),[Month] int,Qty3 int)
insert into @b select 'B',1,5
union all select 'E',1,10
union all select 'F',2,13declare @c table(Item varchar(10),[Month] int,Qty4 int)
insert into @c select 'B',1,5
union all select 'C',1,10
union all select 'C',2,1declare @d table(Item varchar(10),[Month] int,Qty5 int)
insert into @d select 'B',1,5
union all select 'E',1,10
union all select 'F',2,13select Item,
[Month],
Qty1=sum(case lb when 1 then Qty1 else 0 end),
Qty2=sum(case lb when 2 then Qty1 else 0 end),
Qty3=sum(case lb when 3 then Qty1 else 0 end),
Qty4=sum(case lb when 4 then Qty1 else 0 end),
Qty5=sum(case lb when 5 then Qty1 else 0 end)
from
(select *,1 as lb from @t
union all
select *,2 as lb from @a
union all
select *,3 as lb from @b
union all
select *,4 as lb from @c
union all
select *,5 as lb from @d)a
group by
Item,[Month]
order by
Item,[Month]
View呢,不知道该怎么怎么做呢?谢谢老大
create table t1(Item varchar(10),[Month] int,Qty1 int)
insert into t1 select 'A',1,20
union all select 'A',2,35
union all select 'B',1,10create table t2(Item varchar(10),[Month] int,Qty2 int)
insert into t2 select 'B',1,5
union all select 'C',1,10
union all select 'C',2,1create table t3(Item varchar(10),[Month] int,Qty3 int)
insert into t3 select 'B',1,5
union all select 'E',1,10
union all select 'F',2,13create table t4(Item varchar(10),[Month] int,Qty4 int)
insert into t4 select 'B',1,5
union all select 'C',1,10
union all select 'C',2,1create table t5(Item varchar(10),[Month] int,Qty5 int)
insert into t5 select 'B',1,5
union all select 'E',1,10
union all select 'F',2,13
go--建立视图
create view [test]
asselect Item,
[Month],
Qty1=sum(case lb when 1 then Qty1 else 0 end),
Qty2=sum(case lb when 2 then Qty1 else 0 end),
Qty3=sum(case lb when 3 then Qty1 else 0 end),
Qty4=sum(case lb when 4 then Qty1 else 0 end),
Qty5=sum(case lb when 5 then Qty1 else 0 end)
from
(select *,1 as lb from t1
union all
select *,2 as lb from t2
union all
select *,3 as lb from t3
union all
select *,4 as lb from t4
union all
select *,5 as lb from t5)a
group by
Item,[Month]
go--查询
select * from test order by Item,[Month]
go--删除测试环境
drop view test
drop table t1,t2,t3,t4,t5