固定行列转换加分段统计

--1、办公  2、餐饮  3、商业  4、酒店  5、其他
select (case when (ci_area>=0 and ci_area<=500) then '1-500' when (ci_area>=501 and ci_area<=1000) then '500-1000' else '一千以上' end) a,
sum(case when ci_leixing=1 then 1 else 0 end) as '办公',
sum(case when ci_leixing=2 then 1 else 0 end) as '餐饮',
sum(case when ci_leixing=3 then 1 else 0 end) as '商业',
sum(case when ci_leixing=4 then 1 else 0 end) as '酒店',
sum(case when ci_leixing=5 then 1 else 0 end) as '其他'
from zab_clientInfo 
where ci_designerid=60
group by 
case when (ci_area>=0 and ci_area<=500) then '1-500' when (ci_area>=501 and ci_area<=1000) then '500-1000' else '一千以上' end  
order by a

 

参考方法:1.用sql语句实现年龄分段统计

select case when (age >=10 and age <=20) then '10-20' when (age >=21 and age <=30) then '21-30'  else '30-' end 'eag_layer', count(*) emps from emp

group by 

case when (age >=10 and age <=20) then '10-20' when (age >=21 and age <=30) then '21-30'  else '30-' end

order by 1

2.查询列数固定sql

select sname as 姓名,

max(case course when '语文' then score else 0 end) 语文,

max(case course when '数学' then score else 0 end) 数学,

max(case course when '物理' then score else 0 end) 物理,

from scores

group by sname

posted @ 2013-09-11 11:41  Mr.Thanks  阅读(168)  评论(0编辑  收藏  举报