求一句SQL

[kingnew]

有两个表
表A(领料控制表):
ctl_ID(表号) res_ID(物料ID) ctl_Num(控制数量)
1 1 80
1 2 50
1 3 60
2 1 34
2 2 49
3 2 56
表B(领料单):
lld_ID(领料单号) ctl_ID(表号) res_ID(物料ID) res_Num(控制数量)
1 2 1 6
1 2 2 6
1 2 4 56
2 2 1 20
2 2 3 15

一张控制表可以分多次领用(领用总数可超过控制数，也可领用控制表中没有的物料)

要求查询出一个表C：(例：查询控制表2的领用情况)
ctl_ID(表号) res_ID(物料ID) ctl_Num(控制数量) res_Num1(领用单1) res_Num2(领用单2)...
2 1 34 6 20
2 2 49 6 NULL
2 3 NULL NULL 15
2 4 NULL 56 NULL

[kingnew]

不好意思，表B(领料单): res_Num(控制数量)应为：领用数量

[hevin]

是不是对任何控制表只可能用两个领料单号1和2，如果不是的话，可能就只能用动态SQL了。

[梅青松]

--建立测试环境
Create Table A(ctl_ID int,res_ID int,ctl_Num int)
Create Table B(lld_ID int,ctl_ID int,res_ID int,res_Num int)
--插入数据
insert into A
select 1,1,80 union
select 1,2,50 union
select 1,3,60 union
select 2,1,34 union
select 2,2,49 union
select 3,2,56
select * from A

insert into B
select 1,2,1,6 union
select 1,2,2,6 union
select 1,2,4,56 union
select 2,2,1,20 union
select 2,2,3,15
select * from B
--测试语句

declare @sql varchar(8000)
set @sql = 'select B.ctl_ID, B.res_ID, max(A.ctl_Num) ctl_Num'
select @sql = @sql + ',sum(case lld_ID when ' + cast(lld_ID as varchar(5)) + ' then res_Num else null end) '
+ quotename('res_Num' + rtrim(lld_ID))
from (select distinct lld_ID from B) as c
select @sql = @sql + ' from B left join A on B.ctl_ID = A.ctl_ID and A.res_ID = B.res_ID where B.ctl_ID = 2 group by B.ctl_ID, B.res_ID'
--print @sql
exec(@sql)

--删除测试环境
Drop Table A
Drop Table B

--结果
2 1 34 6 20
2 2 49 6 NULL
2 3 NULL NULL 15
2 4 NULL 56 NULL

[duanduan1122]

mark