有一张 表A 结构如下
ReasonId Reason
1 A
2 B
3 C
4 D
5 E
6 F别一张 表B 结构如下StatId ReasonId FNum
1 1 4
2 3 5
3 1 6
4 4 8
5 3 5
6 2 6现在想得到如下的结果应该怎么做?(ReasonId和FNum的类型都为int)
StatId A B C D E F
1 4 6 5 8 0 0急!!!!!!!!!!!!!!!!!!!!
ReasonId Reason
1 A
2 B
3 C
4 D
5 E
6 F别一张 表B 结构如下StatId ReasonId FNum
1 1 4
2 3 5
3 1 6
4 4 8
5 3 5
6 2 6现在想得到如下的结果应该怎么做?(ReasonId和FNum的类型都为int)
StatId A B C D E F
1 4 6 5 8 0 0急!!!!!!!!!!!!!!!!!!!!
set @sql='select statId'
select @sql=@sql+','''+rtrim(Reason)+'''=sum(case ReasonId when '''+rtrim(ReasonId)+''' then FNum else 0 end)'
from
(select statid,reason,b.reasonid,fnum from temptab a,temptabrel b where a.reasonid=b.reasonid) c
group by reason,reasonid order by reasonidset @sql=@sql+' from temptabrel group by statId,ReasonId'
exec(@sql)
create table A(ReasonId int,Reason varchar(200))
insert into A
select 1,'A' 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'create table B(StatId int, ReasonId int, FNum int)
insert into B
select 1,1,4 union all
select 2,3,5 union all
select 3,1,6 union all
select 4,4,8 union all
select 5,3,5 union all
select 6,2,6--测试
--1《用静态方法》
select isnull(C.StatID,1) StatID
,sum(case when A.Reason='A' then isnull(FNum,0) else 0 end) 'A'
,sum(case when A.Reason='B' then isnull(FNum,0) else 0 end) 'B'
,sum(case when A.Reason='C' then isnull(FNum,0) else 0 end) 'C'
,sum(case when A.Reason='D' then isnull(FNum,0) else 0 end) 'D'
,sum(case when A.Reason='E' then isnull(FNum,0) else 0 end) 'E'
,sum(case when A.Reason='F' then isnull(FNum,0) else 0 end) 'F'
from A left join
(
select 1 StatID,ReasonID,min(FNum) FNum from B--用分组方法,取B里的FNum最少值
group by ReasonID
) C
on A.ReasonID=C.ReasonID
group by isnull(C.StatID,1)
--2用动态方法
declare @vSQLString varchar(4000)
set @vSQLString='select isnull(C.StatID,1) StatID'+char(13)
select @vSQLString=@vSQLString
+',sum(case when A.Reason='''+A.Reason+''' then isnull(FNum,0) else 0 end) '''+A.Reason+''''+char(13)
from A
set @vSQLString=@vSQLString+char(13)+'from A left join'
+' (
select 1 StatID,ReasonID,min(FNum) FNum from B--用分组方法,取B里的FNum最少值
group by ReasonID
) C'
+' on A.ReasonID=C.ReasonID'
+' group by isnull(C.StatID,1)'
--print @vSQLString
exec(@vSQLString)
--测试结果
StatID A B C D E F
1 4 6 5 8 0 0
--删除测试环境
drop table A
drop table B