T_A (记录不确定,即可以加下去,如加 7,g. 并TheName可改.所以可能要按ID来计算)ID TheName
1 aa (比如之前是 a)
2 b
3 c
4 d
5 e
6 f
T_BID TID TAID TATheName M
1 1 1 a 130
2 1 2 b 10
3 1 5 e 150
4 2 1 a 300
5 2 3 c 100
6 2 1 a 50
7 2 6 f 80
8 3 1 a 20
9 3 1 a 210
10 4 1 aa 60
11 5 2 b 105
12 5 4 d 200
想要结果为TID M_ALL aa b c d e f
1 290 130 10 0 0 150 0
2 440 350 0 100 0 0 80
3 230 230 0 0 0 0 0
4 60 60 0 0 0 0 0
5 305 0 105 0 200 0 0
declare @sql varchar(8000)
set @sql = 'select b.TID,sum(M) M_ALL '
select @sql = @sql + ' , max(case b.TATheName when ''' + TheName + ''' then M else 0 end) [' + TheName + ']'
from (select distinct TheName from t_a) as t
set @sql = @sql + ' from t_b b , t_a a where b.TAID = a.id group by b.TID'
exec(@sql)
insert into t_a values(1 , 'aa')
insert into t_a values(2 , 'b')
insert into t_a values(3 , 'c')
insert into t_a values(4 , 'd')
insert into t_a values(5 , 'e')
insert into t_a values(6 , 'f')
create table T_B (ID int,TID int,TAID int,TATheName varchar(10), M int)
insert into t_b values(1 , 1 , 1 , 'a' , 130)
insert into t_b values(2 , 1 , 2 , 'b' , 10)
insert into t_b values(3 , 1 , 5 , 'e' , 150)
insert into t_b values(4 , 2 , 1 , 'a' , 300)
insert into t_b values(5 , 2 , 3 , 'c' , 100)
insert into t_b values(6 , 2 , 1 , 'a' , 50)
insert into t_b values(7 , 2 , 6 , 'f' , 80)
insert into t_b values(8 , 3 , 1 , 'a' , 20)
insert into t_b values(9 , 3 , 1 , 'a' , 210)
insert into t_b values(10 , 4 , 1 , 'aa' , 60)
insert into t_b values(11 , 5 , 2 , 'b' , 105)
insert into t_b values(12 , 5 , 4 , 'd' , 200)
godeclare @sql varchar(8000)
set @sql = 'select b.TID,sum(M) M_ALL '
select @sql = @sql + ' , max(case b.TATheName when ''' + TheName + ''' then M else 0 end) [' + TheName + ']'
from (select distinct TheName from t_a) as t
set @sql = @sql + ' from t_b b , t_a a where b.TAID = a.id group by b.TID'
exec(@sql) drop table t_a , t_b/*
TID M_ALL aa b c d e f
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 290 0 10 0 0 150 0
2 530 0 0 100 0 0 80
3 230 0 0 0 0 0 0
4 60 60 0 0 0 0 0
5 305 0 105 0 200 0 0*/
go
if object_id('test.dbo.T_A') is not null drop table T_A
-- 创建数据表
create table T_A
(
ID int,
TheName char(3)
)
go
--插入测试数据
insert into T_A select 1,'aa'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'
union all select 5,'e'
union all select 6,'f'
go
if object_id('test.dbo.T_B') is not null drop table T_B
-- 创建数据表
create table T_B
(
ID int,
TID int,
TAID int,
TATheName char(2),
M int
)
go
--插入测试数据
insert into T_B select 1,1,1,'a',130
union all select 2,1,2,'b',10
union all select 3,1,5,'e',150
union all select 4,2,1,'a',300
union all select 5,2,3,'c',100
union all select 6,2,1,'a',50
union all select 7,2,6,'f',80
union all select 8,3,1,'a',20
union all select 9,3,1,'a',210
union all select 10,4,1,'aa',60
union all select 11,5,2,'b',105
union all select 12,5,4,'d',200
go
--代码实现declare @sql varchar(max)
select @sql=isnull(@sql+',','')+rtrim(TheName)+'=sum(case when TheName='''+rtrim(TheName)+''' then M else 0 end)' from T_A
print @sql
exec('select TID,M_ALL=sum(M),'+@sql+' from T_A a join T_B b on a.ID=b.TAID group by TID')/*测试结果TID M_ALL aa b c d e f
---------------------------------------------------
1 290 130 10 0 0 150 0
2 440 350 0 100 0 0 80
3 230 230 0 0 0 0 0
4 60 60 0 0 0 0 0
5 305 0 105 0 200 0 0(5 行受影响)
*/
27360.00 0.00 0.00 0.00 300.00 0.00 0.00 7260.00 0.00 0.00
1500.00 0.00 0.00 0.00 300.00 0.00 0.00 0.00 0.00 0.00
12400.00 0.00 0.00 0.00 300.00 0.00 0.00 0.00 0.00 10120.00