SQL的分段统计查询语句

SQL的分段统计查询语句
我们在数据的查询中经常会遇到这样的情况，查询每个月的记录的数量，而在数据库中并没有存在这样的字段，只是有一个日期的字段。例如下面的简单数据库：
--创建测试表test1
create table test1
(
id int identity(1,1),--编号，自动生成
name varchar(10),--记录名称
dates datetime--记录时间
)

我们假设有如下的记录：
ID NAME DATES
1 测试1 2007-12-20
2 测试2 2007-12-21
3 测试3 2007-12-22
4 测试4 2007-11-20
5 测试5 2007-11-21
6 测试6 2007-11-22
7 测试7 2007-10-20

我们想实现这样的效果：
年度 1月份 2月份 3月份 4月份 5月份 6月份 7月份 8月份 9月份 10月份 11月份 12月份
2007 0 0 0 0 0 0 0 0 0 1 0 0
2007 0 0 0 0 0 0 0 0 0 0 3 0
2007 0 0 0 0 0 0 0 0 0 0 0 3

我们可以这样来写SQL语句
select years as 年度,
case when months=1 then counts else 0 end 1月份 ,
case when months=2 then counts else 0 end 2月份 ,
case when months=3 then counts else 0 end 3月份 ,
case when months=4 then counts else 0 end 4月份 ,
case when months=5 then counts else 0 end 5月份 ,
case when months=6 then counts else 0 end 6月份 ,
case when months=7 then counts else 0 end 7月份 ,
case when months=8 then counts else 0 end 8月份 ,
case when months=9 then counts else 0 end 9月份 ,
case when months=10 then counts else 0 end 10月份 ,
case when months=11 then counts else 0 end 11月份 ,
case when months=12 then counts else 0 end 12月份
from
(
select year(dates) as years,month(dates) s,count(name)as counts
from test1 group by year(dates),month(dates)
) as test2
这样就可以达到我们想要的效果了。
看了上面的内容，下面来说一下原理，其实很简单的。首先，我们要找出数据库中每年每个月的记录的个数，那么，需要使用函数进行分组统计，分组的条件当然是年份和月份，所以还要使用时间函数，因此我们写出如下的SQL语句：
select year(dates) as years,month(dates) s,count(name)as counts
from test1 group by year(dates),month(dates)
这样，我们就可以把各年各月的记录的个数，统计出来了，怎样显示成我们需要的样式呢？使用case when来解决这个问题。
语法 CASE WHEN exrp1 THEN exrp2 [ELSE exrp3] END
其中，
exrp1为条件，我们这里是想把各个月份的数据都统计出来，那么首先我们要找到月份为1的数据，然后找到月份为2的数据，....以此类推，所以，我们要写 months=1,months=2,....
exrp2 为符合条件时显示的数据，则，如果月份为1，则显示1月份的数据counts
exrp3 为可选参数，当条件不成立时，显示的数据，如果此处不写，那么当条件不成立时，数据值为NULL。
这样我们就可以把各个月份的数据统计出来了。最后不要忘记起别名，将以上的内容进行总结，则得到我们前面给出的SQL语句了

评论

2 楼 wodesunday 2015-07-24  

:idea:

1 楼 wodesunday 2015-07-24  

引用