sqlserver统计2

???????? 接上一篇，数据统计一般都会按照日期进行统计。目标格式基本上如下：

???????? 这样会涉及到时间的获取，以及行列的转换，首先时间问题。将yyyy-MM-dd hh:mm:ss这种在数据库里面保存的格式，转化为天。再进行统计，得到一期目标数据格式如下：

?

?

???????? 得到一期目标格式，就是只需要日期处理，直接上sql.

select sl.service,CONVERT(varchar(10),sl.access_date,23) adate , count(*) as  num from dbo.service_log  sl where sl.access_date between '2011-04-01' and '2011-04-03' group by CONVERT(varchar(10),sl.access_date,23),sl.service

??????? 下一步就是要在这上面得到目标格式，基础知识可以参见《sql cookbook》的第12章"报表和数据仓库运算"，在上一步的基础上，利用以下sql可以得到目标格式。

select s.service, max(case adate when '2011-04-01' then num else 0 end) as '2011-04-01',max(case adate when '2011-04-02' then num else 0 end) as '2011-04-02',max(case adate when '2011-04-03' then num else 0 end) as '2011-04-03'from (select sl.service,CONVERT(varchar(10),sl.access_date,23) adate , count(*) as  num from dbo.service_log  sl where sl.access_date between '2011-04-01' and '2011-04-30' group by CONVERT(varchar(10),sl.access_date,23),sl.service) s group by s.service

????? 还有其他的处理方式，在sqlserver中是pivot,其他数据库也有。关于pivot的基础知识可以参见《sql cookbook》第14章"若干另类目标"，利用pivot的sql如下：

select service,[2011-04-01],[2011-04-02],[2011-04-03] from(select sl.service,CONVERT(varchar(10),sl.access_date,23) adate , count(*) as  num from dbo.service_log  sl where sl.access_date between '2011-04-01' and '2011-04-30' group by CONVERT(varchar(10),sl.access_date,23),sl.service) spivot (sum(s.num) for s.adate in ([2011-04-01],[2011-04-02],[2011-04-03])) as t

?