求SQL语句 - 编程论坛

q1231262007-12-10 17:38

select No,
大=sum(case when f=1 then Area else 0 end),
中=sum(case when f=2 then Area else 0 end),
小=sum(case when f=3 then Area else 0 end)
(select No,Area,Kind from Table where Kind='大',1 as f
from Table
union all
select No,Area,Kind from Table where Kind='中',2 as f
from Table
union all
select No,Area,Kind from Table where Kind='小',3 as f
from Table)a
group by No,Area,Kind
Order By No
随手写的，调试看看

[[italic] 本帖最后由 q123126 于 2007-12-10 17:43 编辑 [/italic]]

purana2007-12-10 17:49

declare @t table(No char(3),Area int,Kind nchar(1))
insert @t select '123',10,N'大'
insert @t select '123',20,N'中'
insert @t select '123',30,N'小'

select No,max(case when Kind=N'大' then Area end) as [大],
          max(case when Kind=N'中' then Area end) as [中],
          max(case when Kind=N'小' then Area end) as [小]
from @t
group by No

/*
No   大           中           小
---- ----------- ----------- -----------
123  10          20          30

（所影响的行数为 1 行）

*/