SQL-'聚合查询'-按10元为一个档次，查询单价各档次的数量
0
悬赏园豆：20
[已解决问题]
解决于 2010-09-02 15:39
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 加以判断， 所以小弟在这求根据问题标题的要求写出结果一样的，比较简洁的语句，注意是聚合查询。
问题补充：
楼下的大哥们。不要再用case when then 语句啊·~！有么其它能实现查询结果的~~？
最佳答案
0
通常学校的题目都不会要求行列转换的。
“当遇到1000条” 你们老师说这句话的目的是不是让您不要将分类放在同一行上而是放在列上？
两个版本：
with titles(price) as (
select OBJECT_ID%100 from sys.objects
),
--- start here
tmp02(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_count
from tmp02 where lvl<3
union all
select '>30',sum(the_count)
from tmp02
where lvl >= 3
lvl the_count
------------------------------------------------------------- -----------
0-10 5
10-20 7
20-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
) t
pivot
(
sum(the_count) for lvl in ([0-10],[10-20],[20-30],[>30])
)
as pvt
0-10 10-20 20-30 >30
----------- ----------- ----------- -----------
5 7 8 54
收获园豆：10
谢谢！ 此题已经解决~！
其他回答(3)
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
你好· 谢谢你大哥·~！
这个语句不能在用 这个 条件判断写了·~！
http://ajava.org/readbook/java/javafabd/9241.html
可以看下这个，通常只能通过游标来实现，比较复杂
你好；这个问题 穿越自己的知识层面了·~再次谢谢你的关注·~！
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 all
select 'b' as b,sum(price) from title where price>10 and price<=20
union all
select 'c' as b,sum(price) from title where price>20 and price<=30
union all
select 'd' as b,sum(price) from title where price>30
) PRICE
pivot
(
sum(sumprice) for flag in ([a],[b],[c],[d])
)
as PVT
请把sum改成count...
收获园豆：10
大哥·~~我自己学都没有学过i、哈·· 如果你的语句能实现。且简洁，要不你加我 Q 方便向大哥你学习下 ·~！
暂时还没有想到很好的解决方案. 我写的这个也不是太好,比较的复杂
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 all
select 'b' as b,sum(price) from title where price>10 and price<=20
union all
select 'c' as b,sum(price) from title where price>20 and price<=30
union all
select 'd' as b,sum(price) from title where price>30
) PRICE
pivot
(
sum(sumprice) for flag in ([a],[b],[c],[d])
)
as PVT
还需要在寻求比较优化的方法,谢谢!
谢谢
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以上'
这种比上面二种速度要快一半。
测试数据：8198382条记录
查询结果：
0-10 10-20 20-30 30以上
988647 9264 2832 7197639
用时2s
运行环境:MSSQL2005
你好大哥！谢谢你的关注，你的结果是对的，但是你这样的效果与case when 语句差不多，
