一句五个select结合起来的SQL 请进

[crazyceo]

1、

select sum(Length) as length_total1,time
 from T_union20100908 where Class=1 and Speed<=20 group by time

2、

select sum(Length) as length_total1,time
 from T_union20100908 where Class=1 and Speed>20 and Speed<=35 group by time

3、

select sum(Length) as length_total1,time
 from T_union20100908 where Class=1 and Speed>35 and Speed<=50 group by time

4、

select sum(Length) as length_total1,time
 from T_union20100908 where Class=1 and Speed>50 and Speed<=65 group by time

5、

select sum(Length) as length_total1,time
 from T_union20100908 where Class=1 and Speed>65 group by time

以上五个SQL都能查询一个表来

但是我想把他们查询出来的结果都合到一起，成为下表：

time length_total1 length_total2 length_total3 length_total4 length_total5
00:00
00:05
00:10
00:15


求教高手！！！

[delphilrj]

学习！

[kimpkswm]

Union All

[yigerendeC]

union all ,sum(case..)
学习了

[hao1hao2hao3]

select [time],
sum(case when Class=1 and Speed<=20  then length else 0 end) a,
sum(case when Class=1 and Speed>20 and Speed<=35   then length else 0 end) b,
sum(case when Class=1 and Speed>35 and Speed<=50   then length else 0 end) c,
sum(case when Class=1 and Speed>50 and Speed<=65   then length else 0 end) d,
sum(case when Class=1 and Speed>65   then length else 0 end) e
from T_union20100908 
group by [time]

[华夏小卒]

 select time,
 length_total1=sum(case when Speed<=20 then Length else 0 end),
 length_total2=sum(case when Speed>20 and Speed<=35 then Length else 0 end), 
 length_total3=sum(case when Speed>35 and Speed<=50 then Length else 0 end), 
 length_total4=sum(case when Speed>50 and Speed<=65 then Length else 0 end), 
 length_total5=sum(case when Speed>65 then Length else 0 end)
from tb 
where class=1 
group by time 

[jamk]

用CASE语句便可解决~！

[ask_chang]

select time,
length_total1=sum(case when Speed<=20 then Length else 0 end),
length_total2=sum(case when Speed>20 and Speed<=35 then Length else 0 end),
length_total3=sum(case when Speed>35 and Speed<=50 then Length else 0 end),
length_total4=sum(case when Speed>50 and Speed<=65 then Length else 0 end),
length_total5=sum(case when Speed>65 then Length else 0 end)
from tb
where class=1
group by time

支持,楼主测试一下。
是否正确。

[njlywy]

case

[haitao]

2种办法，一种上面说了，sum(case ...)
一种是：

           select time, sum(...), 0,0,0 from ... where ... group by ...
union all select time, 0,sum(...), 0,0 from ... where ... group by ...
union all select time, 0,0,sum(...), 0 from ... where ... group by ...
union all select time, 0,0,0,sum(...)  from ... where ... group by ...