# SQL-'聚合查询'-按10元为一个档次，查询单价各档次的数量

0

select sum(a.a) '0-10',sum(a.b) '10-20',sum(a.c) '20-30',sum(a.d) '30以上'
from
(select case when price>0 and price<=10 then 1 end as a,
case when price>10 and price<=20 then 1 end as b,
case when price>20 and price<=30 then 1 end as c,
case when price>30 then 1 end as d
from titles) as a

这是我用  CASE when  条件判断。老师说虽然说结果对，但是这个写着太复杂了，当遇到1000条。 是那我就要写几百 几千条CASE when 加以判断， 所以小弟在这求根据问题标题的要求写出结果一样的，比较简洁的语句，注意是聚合查询。

sj3136456 | 初学一级 | 园豆：35

0

“当遇到1000条” 你们老师说这句话的目的是不是让您不要将分类放在同一行上而是放在列上？

`with titles(price) as (    select OBJECT_ID%100 from sys.objects),--- start heretmp02(lvl,the_count) as (    select price/10 lvl,COUNT(*) the_count     from titles     group by price/10) select     cast(lvl*10 as varchar)+ '-' + cast( (lvl+1)*10 as varchar) lvl,    the_countfrom tmp02 where lvl<3union allselect '>30',sum(the_count)from tmp02 where lvl >= 3lvl                                                           the_count------------------------------------------------------------- -----------0-10                                                          510-20                                                         720-30                                                         8>30                                                           54`

`with titles(price) as (    select OBJECT_ID%100 from sys.objects),--- start here tmp(lvl,the_count) as (    select price/10 lvl,COUNT(*) the_count     from titles     group by price/10) select * from (    select         cast(lvl*10 as varchar)+ '-' + cast( (lvl+1)*10 as varchar) lvl,        the_count    from tmp where lvl<3    union all    select '>30',sum(the_count)    from tmp     where lvl >= 3) tpivot (    sum(the_count) for lvl in ([0-10],[10-20],[20-30],[>30]))as pvt0-10        10-20       20-30       >30----------- ----------- ----------- -----------5           7           8           54`

killkill | 小虾三级 |园豆：992 | 2010-08-29 17:04

sj3136456 | 园豆：35 (初学一级) | 2010-09-02 15:38

0

`select sum(case when price>0 and price<=10 then 1 end ) as '0-10',sum(case when price>10 and price<=20 then 1 end ) as '10-20',sum(case when price>20 and price<=30 then 1 end )as '20-30',sum(case when price>30 then 1 end) as '30以上'from titles`

jowo | 园豆：2834 (老鸟四级) | 2010-08-27 14:43

0

`select [a] as '0-10',[b] as '10-20',[c] as '20-30',[d] as '>30' from(select 'a' as flag,sum(price) as sumprice from title where price>0 and price<=10 union allselect 'b' as b,sum(price) from title where price>10 and price<=20 union allselect 'c' as b,sum(price) from title where price>20 and price<=30 union allselect 'd' as b,sum(price) from title where price>30 ) PRICEpivot(sum(sumprice) for flag in ([a],[b],[c],[d]))as PVT`

changbluesky | 园豆：854 (小虾三级) | 2010-08-27 15:04

0

sj3136456的SQL第一次运行用时36s第二次4s和jowo一样

select
(select count(UserID) as num from UC_User where Logins>=0 and Logins<=10) as '0-10',
(select count(UserID) as num from UC_User where Logins>10 and Logins<=20) as '10-20',
(select count(UserID) as num from UC_User where Logins>20 and Logins<=30) as '20-30',
(select count(UserID) as num from UC_User where Logins>30) as  '30以上'

0-10　　　10-20　20-30　30以上

988647    9264    2832    7197639

您需要登录以后才能回答，未注册用户请先注册