首页 新闻 赞助 找找看

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 语句啊·~!有么其它能实现查询结果的~~?
sj3136456的主页 sj3136456 | 初学一级 | 园豆:35
提问于:2010-08-27 14:18
< >
分享
最佳答案
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
killkill | 小虾三级 |园豆:1192 | 2010-08-29 17:04
谢谢! 此题已经解决~!
sj3136456 | 园豆:35 (初学一级) | 2010-09-02 15:38
其他回答(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

 

jowo | 园豆:2834 (老鸟四级) | 2010-08-27 14:43
你好· 谢谢你大哥·~! 这个语句不能在用 这个 条件判断写了·~!
支持(0) 反对(0) sj3136456 | 园豆:35 (初学一级) | 2010-08-27 14:45
http://ajava.org/readbook/java/javafabd/9241.html 可以看下这个,通常只能通过游标来实现,比较复杂
支持(0) 反对(0) jowo | 园豆:2834 (老鸟四级) | 2010-08-27 15:02
你好;这个问题 穿越自己的知识层面了·~再次谢谢你的关注·~!
支持(0) 反对(0) sj3136456 | 园豆:35 (初学一级) | 2010-08-27 15:08
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
changbluesky | 园豆:854 (小虾三级) | 2010-08-27 15:04
大哥·~~我自己学都没有学过i、哈·· 如果你的语句能实现。且简洁,要不你加我 Q 方便向大哥你学习下 ·~!
支持(0) 反对(0) sj3136456 | 园豆:35 (初学一级) | 2010-08-27 15:07
暂时还没有想到很好的解决方案. 我写的这个也不是太好,比较的复杂 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) 反对(0) changbluesky | 园豆:854 (小虾三级) | 2010-08-27 15:37
谢谢
支持(0) 反对(0) sj3136456 | 园豆:35 (初学一级) | 2010-08-28 11:41
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

熊哥 | 园豆:682 (小虾三级) | 2010-08-28 00:20
运行环境:MSSQL2005
支持(0) 反对(0) 熊哥 | 园豆:682 (小虾三级) | 2010-08-28 00:22
你好大哥!谢谢你的关注,你的结果是对的,但是你这样的效果与case when 语句差不多,
支持(0) 反对(0) sj3136456 | 园豆:35 (初学一级) | 2010-08-28 11:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册