有表A,B
A:
id qty
1 12
2 13
3 14
4 15
5 16B:
id dept qty
1 a 11
1 b 12
1 b 12
2 a 13
4 c 14
求结果:
id qty dept_A_qty detp_B_qty dept_C_qty
1 12 11 24(12+12) 0
2 13 13 0 0
3 14 0 0 0
4 15 0 0 14
5 16 0 0 0其中表B中的dept可能的会很多,有n种类型,就要在结果中多添加n个字段
A:
id qty
1 12
2 13
3 14
4 15
5 16B:
id dept qty
1 a 11
1 b 12
1 b 12
2 a 13
4 c 14
求结果:
id qty dept_A_qty detp_B_qty dept_C_qty
1 12 11 24(12+12) 0
2 13 13 0 0
3 14 0 0 0
4 15 0 0 14
5 16 0 0 0其中表B中的dept可能的会很多,有n种类型,就要在结果中多添加n个字段
insert a select 1,12
union all select 2,13
union all select 3,14
union all select 4,15
union all select 5,16create table B(id int,dept varchar(20),qty int)
insert b select 1,'a',11
union all select 1,'b',12
union all select 1,'b',12
union all select 2,'a',13
union all select 4,'c',14declare @s varchar(8000)
set @s='select a.id,a.qty'
select @s=@s+',[dept_'+b.dept+'_qty]=sum(case b.dept when '''+b.dept+''' then b.qty else 0 end)' from b group by b.dept
exec(@s+' from a left join b on a.id=b.id group by a.id,a.qty')
1 2 11 12(不对) 0
2 3 13 0 0
3 4 0 0 0
4 5 0 0 14
5 6 0 0 0
有个小地方不对。应该为24。同一个ID,和Dept 可能会有多条记录
insert a select 1,12
union all select 2,13
union all select 3,14
union all select 4,15
union all select 5,16create table B(id int,dept varchar(20),qty int)
insert b
select 1,'a',11
union all select 1,'b',12
union all select 1,'b',12
union all select 2,'a',13
union all select 4,'c',14
DECLARE @f VARCHAR(1024),@f1 VARCHAR(1024)
SET @f=''
SET @f1=''SELECT @f=@f + ',dept_' + dept + '_qty=(SELECT SUM(s.qty) FROM B s WHERE s.dept=b.dept and s.dept=''' + dept + ''' and s.id=a.id)' FROM B GROUP BY dept
SELECT @f1=@f1 + ',ISNULL(MAX(dept_' + dept +'_qty),0)' FROM B GROUP BY dept
SELECT @f='SELECT a.id id,a.qty qty' + @f + ' FROM A a LEFT JOIN B b ON a.id=b.id'
SELECT @f='SELECT id,qty' + RTRIM(@f1) + 'FROM (' + @f + ') base GROUP BY id,qty'
EXEC (@f)DROP TABLE A
DROP TABLE Bresult:1 12 11 24 0
2 13 13 0 0
3 14 0 0 0
4 15 0 0 14
5 16 0 0 0
在计算值时用CASE WHEN写法会简便很多,我是直写的,有点烦琐,另外效率上也不是很高,楼主自行改一下吧