向大家求一组不简单的MS SQL语句
要做一张难度较大的报表，请大家帮写组MS SQL语句，情况如下
Info 表（说明：Info 表记录为动态添加Code字段内容不会重复，可能还会添加更多）
Code IncDes
1009 天染公司
1005 海丰公司
1023 采诗公司
MaInfo 表（说明：MaInfo 表为库存表）
Code Barcode MaterialsCn number
1009 1111111 可口可乐 10
1009 1111112 芬达可乐 12
1009 1111113 花生果 15
1005 1111112 芬达可乐 20
1005 1111114 出前一丁 30
1023 1111112 芬达可乐 22
1005 1111113 花生果 40
SaleDetail 表（说明：SaleDetail 表为零售表）
Code Barcode MaterialsCn sellnum1
1009 1111111 可口可乐 2
1005 1111114 出前一丁 3
1023 1111112 芬达可乐 1
SaleOrder 表（说明：SaleOrder 表为销售表）
Code Barcode MaterialsCn sellnum2
1009 1111111 可口可乐 2
1023 1111112 芬达可乐 1
要的结果如下
天染公司 海丰公司 采诗公司 .....(公司是动态的)
总销量 库存 总销量 库存 总销量 库存 销量合计 库存合计
1111111 可口可乐 4 10 0 0 0 0 4 10
1111112 芬达可乐 0 12 0 20 2 22 2 54
1111113 花生果 0 15 0 40 0 0 0 55
1111114 出前一丁 0 0 3 30 0 0 3 30
结果说明：（如在此看结果排列的不整齐，麻烦大家把结果COPY到记事本看）以MaInfo 库存表的Barcode为唯一依据，重复的商品不列出，并以MaInfo 库存表的Barcode和Code为唯一关系SaleDetail 表和SaleOrder 表，Info 表的Code关联其它三张表，由于过程有点复杂，麻烦大伙看清结果帮写组MS SQL语句，3Q
[解决办法]
--创建测试环境,假设下列表中的数据都已经按照公司、产品汇总，即公司、产品是主键。
create table Info(Code int,IncDes varchar(10))
create table MaInfo(Code int,Barcode varchar(10),MaterialsCn varchar(10),number int)
create table SaleDetail(Code int,Barcode varchar(10),MaterialsCn varchar(10),sellnum1 int)
create table SaleOrder(Code int,Barcode varchar(10),MaterialsCn varchar(10),sellnum2 int)
--插入测试数据
insert Info(Code,IncDes)
select '1009 ', '天染公司 ' union all
select '1005 ', '海丰公司 ' union all
select '1023 ', '采诗公司 '
insert MaInfo(Code,Barcode,MaterialsCn,number)
select '1009 ', '1111111 ', '可口可乐 ', '10 ' union all
select '1009 ', '1111112 ', '芬达可乐 ', '12 ' union all
select '1009 ', '1111113 ', '花生果 ', '15 ' union all
select '1005 ', '1111112 ', '芬达可乐 ', '20 ' union all
select '1005 ', '1111114 ', '出前一丁 ', '30 ' union all
select '1023 ', '1111112 ', '芬达可乐 ', '22 ' union all
select '1005 ', '1111113 ', '花生果 ', '40 '
insert SaleDetail(Code,Barcode,MaterialsCn,sellnum1)
select '1009 ', '1111111 ', '可口可乐 ', '2 ' union all
select '1005 ', '1111114 ', '出前一丁 ', '3 ' union all
select '1023 ', '1111112 ', '芬达可乐 ', '1 '
insert SaleOrder(Code,Barcode,MaterialsCn,sellnum2)
select '1009 ', '1111111 ', '可口可乐 ', '2 ' union all
select '1023 ', '1111112 ', '芬达可乐 ', '1 '
--求解过程
select _m.code,_i.IncDes,_m.barcode,_m.MaterialsCn
,min(number) as number,isnull(sum(num),0) as sellnum
into tmp
from MaInfo _m
full join(
select Code,Barcode,MaterialsCn,sellnum1 as num
from SaleDetail
union all
select Code,Barcode,MaterialsCn,sellnum2
from SaleOrder
) _s on _s.code = _m.code and _s.barcode = _m.barcode
join info _i on _i.code = _m.code
group by _m.code,_m.barcode,_m.MaterialsCn,_i.IncDes
declare @sql varchar(8000) set @sql = 'select barcode,MaterialsCn '
select @sql = @sql + ',sum(case when IncDes = ' ' ' + IncDes
+ ' ' ' then sellnum else 0 end) as ' + IncDes + '销量 '
+ ',sum(case when IncDes = ' ' ' + IncDes
+ ' ' ' then number else 0 end) as ' + IncDes + '库存 '
from info
select @sql = @sql + ',sum(sellnum) as 总销量,sum(number) as 总库存
from tmp group by barcode,MaterialsCn order by barcode '
exec(@sql)
--删除测试环境
drop table Info,MaInfo,SaleDetail,SaleOrder,tmp
/*--测试结果
barcode MaterialsCn 天染公司销量 天染公司库存 海丰公司销量 海丰公司库存 采诗公司销量 采诗公司库存 总销量 总库存
1111111 可口可乐 4 10 0 0 0 0 4 10
1111112 芬达可乐 0 12 0 20 2 22 2 54
1111113 花生果 0 15 0 40 0 0 0 55
1111114 出前一丁 0 0 3 30 0 0 3 30
*/
[解决办法]
if object_id( 'pubs..Info ') is not null
drop table Info
go
create table Info(Code varchar(10),IncDes varchar(10))
insert into Info(Code,IncDes) values( '1009 ', '天染公司 ')
insert into Info(Code,IncDes) values( '1005 ', '海丰公司 ')
insert into Info(Code,IncDes) values( '1023 ', '采诗公司 ')
go
if object_id( 'pubs..MaInfo ') is not null
drop table MaInfo
go
create table MaInfo(Code varchar(10),Barcode varchar(10),MaterialsCn varchar(10),number int)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1009 ', '1111111 ', '可口可乐 ', 10)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1009 ', '1111112 ', '芬达可乐 ', 12)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1009 ', '1111113 ', '花生果 ', 15)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1005 ', '1111112 ', '芬达可乐 ', 20)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1005 ', '1111114 ', '出前一丁 ', 30)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1023 ', '1111112 ', '芬达可乐 ', 22)
insert into MaInfo(Code,Barcode,MaterialsCn,number) values( '1005 ', '1111113 ', '花生果 ', 40)
go
if object_id( 'pubs..SaleDetail ') is not null
drop table SaleDetail
go
create table SaleDetail(Code varchar(10),Barcode varchar(10),MaterialsCn varchar(10),sellnum1 int)
insert into SaleDetail(Code,Barcode,MaterialsCn,sellnum1) values( '1009 ', '1111111 ', '可口可乐 ', 2)
insert into SaleDetail(Code,Barcode,MaterialsCn,sellnum1) values( '1005 ', '1111114 ', '出前一丁 ', 3)
insert into SaleDetail(Code,Barcode,MaterialsCn,sellnum1) values( '1023 ', '1111112 ', '芬达可乐 ', 1)
go
if object_id( 'pubs..SaleOrder ') is not null
drop table SaleOrder
go
create table SaleOrder(Code varchar(10),Barcode varchar(10),MaterialsCn varchar(10),sellnum2 int)
insert into SaleOrder(Code,Barcode,MaterialsCn,sellnum2) values( '1009 ', '1111111 ', '可口可乐 ', 2)
insert into SaleOrder(Code,Barcode,MaterialsCn,sellnum2) values( '1023 ', '1111112 ', '芬达可乐 ', 1)
go
select info.incdes,o.* into test from info,
(
select isnull(m.Code,n.code) code , isnull(m.Barcode,n.barcode) barcode, isnull(m.MaterialsCn,n.MaterialsCn) MaterialsCn ,isnull(m.number,0) number ,isnull(n.sellnum,0) sellnum from
(
select Code , Barcode , MaterialsCn , sum(number) number from MaInfo
group by Code , Barcode , MaterialsCn
) m
full join
(
select code , barcode , materialscn , sum(sellnum) sellnum from
(
select SaleDetail.Code,SaleDetail.Barcode,SaleDetail.MaterialsCn,SaleDetail.sellnum1 sellnum from SaleDetail
union all
select SaleOrder.Code,SaleOrder.Barcode,SaleOrder.MaterialsCn,SaleOrder.sellnum2 sellnum from SaleOrder
) t
group by code , barcode , materialscn
) n
on m.code = n.code and m.Barcode = n.Barcode and m.MaterialsCn = n.MaterialsCn
) o
where info.code = o.code
--select * from test
declare @sql varchar(8000)
set @sql = 'select Barcode,MaterialsCn '
select @sql = @sql + ' , sum(case when IncDes = ' ' ' + IncDes + ' ' ' then sellnum else 0 end) [ ' + IncDes + '总销量 ' + '] '+
' , sum(case when IncDes = ' ' ' + IncDes + ' ' ' then number else 0 end) [ ' + IncDes + '库存 ' + '] '
from (select distinct IncDes from test) as a
set @sql = @sql + ' ,sum(sellnum) 销量合计 , sum(number) 库存合计 from test group by Barcode,MaterialsCn order by barcode,materialscn '
exec(@sql)
drop table Info,MaInfo,SaleDetail,SaleOrder,test
Barcode MaterialsCn 采诗公司总销量 采诗公司库存 海丰公司总销量 海丰公司库存 天染公司总销量 天染公司库存 销量合计 库存合计
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1111111 可口可乐 0 0 0 0 4 10 4 10
1111112 芬达可乐 2 22 0 20 0 12 2 54
1111113 花生果 0 0 0 40 0 15 0 55
1111114 出前一丁 0 0 3 30 0 0 3 30