标题: sql 月统计,真累啊!(一句sql折腾一下午) [打印本页] 作者: 陈振兴 时间: 2012-7-17 18:45 标题: sql 月统计,真累啊!(一句sql折腾一下午) 月统计数据,效率上有区别!
每个要sum明显效率低,主要是个钱数不好算
select convert(varchar(7),regtime,120) 月份,sum(case when (ybbz='1' or ybbz='2') then 1 else 0 end) 医保,
sum(case when ybbz='' then 1 else 0 end) 非医保,sum(hj) money
from registration where disabled=0 and regtime between '2012-1-1' and '2012-12-1'
group by convert(varchar(7),regtime,120)
order by convert(varchar(7),regtime,120)
这种相对就比较简单,也比较方便
select case ybbz when '' then '非医保' else '医保' end as ybbz,count(*) count,sum(hj) money
from dbo.registration where regtime between '2012-1-1' and '2012-2-1'
and disabled=0
group by case ybbz when '' then '非医保' else '医保' end
在给大家给一个convert
select convert(varchar(11),regtime+26,120) from registration
group by convert(varchar(11),regtime+26,120)
order by convert(varchar(11),regtime+26,120) desc
共同学习啊!
大家如果有更好的建议可以提出来学习学习啊!呵呵