日期:2014-05-18  浏览次数:20705 次

区间的数据统计该怎么实现?

是个销售的数据库,该表的结构如下  
spbh   --表示商品编号
dj       --表示该商品的单价
sl       --销售数量
rq       --发生该销售的日期
想统计各个价格区间在某个月的销售量,一次得出结果
比如:想一次统计出价格在0-10,11-20,21-30,31-40,> 40的商品销售量。

请教各位高手,这个该怎么做,用GROUP   BY   似乎行不通啊  




------解决方案--------------------

select spbh,定价=case when dj between 0 and 10 then '0-10 '
when dj between 11 and 20 then '11-20 '
when dj between 21 and 30 then '21-30 '
else '> 40 ' end,[销售数量]=sum(sl)
from 表
group by spbh,case when dj between 0 and 10 then '0-10 '
when dj between 11 and 20 then '11-20 '
when dj between 21 and 30 then '21-30 '
else '> 40 ' end,[销售数量]=sum(sl)
order by 定价 asc

------解决方案--------------------
--try

select spbh,
[0-10]=sum(case when dj between 0 and 10 then sl else 0 end),
[11-20]=sum(case when dj between 11 and 20 then sl else 0 end),
[21-30]=sum(case when dj between 21 and 30 then sl else 0 end),
[31-40]=sum(case when dj between 31 and 40 then sl else 0 end),
[> 40]=sum(case when dj> 40 then sl else 0 end)
from tbName
group by spbh