SQLserver 实现分组统计查询

复制代码 代码如下:–create database
dbTemp use dbTemp create table test ( Pid int identity(1,1) not null
primary key, Years datetime, IsFirstSixMonths int default(0),
–0表示上半年1表示下半年– TotalCome int ) insert test select
‘2007-1-1’,0,50 union select ‘2007-3-1’,0,60 union select
‘2007-12-1’,1,80 union select ‘2008-1-1’,0,100 union select
‘2008-12-1’,1,100 select * from test select convert(char(4),Years,120)
as ‘year’, IsFirstSixMonths=case when IsFirstSixMonths=0 then ‘上半年’
when IsFirstSixMonths=1 then ‘下半年’ END , sum(totalcome) as ‘sum’ from
test group by IsFirstSixMonths,convert(char(4),Years,120) select
convert(char(4),Years,120) as ‘year’, IsFirstSixMonths=case when
IsFirstSixMonths=0 then ‘上半年’ ELSE ‘下半年’ END , sum(totalcome) as
‘sum’ from test group by IsFirstSixMonths,convert(char(4),Years,120)
–DROP DATABASE dbtemp结果如下: 复制代码
代码如下:2007 上半年 110 2007 下半年 80 2008 上半年 100 2008 下半年 100

设置AccessCount字段可以根据需求在特定的时间范围内如果是相同IP访问就在AccessCount上累加。
复制代码 代码如下:Create table Counter (
CounterID int identity(1,1) not null, IP varchar(20), AccessDateTime
datetime, AccessCount int )
该表在这儿只是演示使用,所以只提供了最基本的字段 现在往表中插入几条记录
insert into Counter select ‘127.0.0.1’,getdate(),1 union all select
‘127.0.0.2’,getdate(),1 union all select ‘127.0.0.3’,getdate(),1 1
根据年来查询,以月为时间单位 通常情况下一个简单的分组就能搞定 复制代码 代码如下:select
convert(varchar(7),AccessDateTime,120) as Date, sum(AccessCount) as
[Count] from Counter group by convert(varchar(7),AccessDateTime,120)
像这样分组后没有记录的月份不会显示,如下:
这当然不是我们想要的,所以得换一种思路来实现,如下: 复制代码 代码如下:declare @Year int set
@Year=2009 select m as [Date], sum( case when
datepart(month,AccessDateTime)=m then AccessCount else 0 end ) as
[Count] from Counter c, ( select 1 m union all select 2 union all
select 3 union all select 4 union all select 5 union all select 6 union
all select 7 union all select 8 union all select 9 union all select 10
union all select 11 union all select 12 ) aa where
@Year=year(AccessDateTime) group by m 查询结果如下:2
根据天来查询,以小时为单位。这个和上面的类似,代码如下:
复制代码 代码如下:declare @DateTime datetime
set @DateTime=getdate() select right(100+a,2)+ ‘:00 – ‘+right(100+b,2)+
‘:00 ‘ as DateSpan, sum( case when datepart(hour,AccessDateTime) =a and
datepart(hour,AccessDateTime) b then AccessCount else 0 end ) as
[Count] from Counter c , (select 0 a,1 b union all select 1,2 union
all select 2,3 union all select 3,4 union all select 4,5 union all
select 5,6 union all select 6,7 union all select 7,8 union all select
8,9 union all select 9,10 union all select 10,11 union all select 11,12
union all select 12,13 union all select 13,14 union all select 14,15
union all select 15,16 union all select 16,17 union all select 17,18
union all select 18,19 union all select 19,20 union all select 20,21
union all select 21,22 union all select 22,23 union all select 23,24 )
aa where datediff(day,@DateTime,AccessDateTime)=0 group by
right(100+a,2)+ ‘:00 – ‘+right(100+b,2)+ ‘:00 ‘ 查询结果如下图:

–示例

–示例数据
create table tb(ID int,Time datetime)
insert tb select 1,’2005/01/24 16:20′
union all select 2,’2005/01/23 22:45′
union all select 3,’2005/01/23 0:30′
union all select 4,’2005/01/21 4:28′
union all select 5,’2005/01/20 13:22′
union all select 6,’2005/01/19 20:30′
union all select 7,’2005/01/19 18:23′
union all select 8,’2005/01/18 9:14′
union all select 9,’2005/01/18 18:04′
go

–查询处理:
select     case when grouping(b.Time)=1 then ‘Total’ else b.Time end,
    [Mon]=sum(case a.week when 1 then 1 else 0 end),
    [Tue]=sum(case a.week when 2 then 1 else 0 end),
    [Wed]=sum(case a.week when 3 then 1 else 0 end),
    [Thu]=sum(case a.week when 4 then 1 else 0 end),
    [Fri]=sum(case a.week when 5 then 1 else 0 end),
    [Sat]=sum(case a.week when 6 then 1 else 0 end),
    [Sun]=sum(case a.week when 0 then 1 else 0 end),
    [Total]=count(a.week)
from(
    select Time=convert(char(5),dateadd(hour,-1,Time),108)
            –时间交界点是1am,所以减1小时,避免进行跨天处理
        ,week=(@@datefirst+datepart(weekday,Time)-1)%7
            –考虑@@datefirst对datepart的影响
    from tb
)a right join(
    select id=1,a=’16:00′,b=’19:59′,Time='[5pm – 9pm)’ union all
    select id=2,a=’20:00′,b=’23:59′,Time='[9pm – 1am)’ union all
    select id=3,a=’00:00′,b=’02:59′,Time='[1am – 4am)’ union all
    select id=4,a=’03:00′,b=’07:29′,Time='[4am – 8:30am)’ union all
    select id=5,a=’07:30′,b=’11:59′,Time='[8:30am – 1pm)’ union all
    select id=6,a=’12:00′,b=’15:59′,Time='[1pm – 5pm)’
澳门金沙vip,)b on a.Time>=b.a and a.Time<b.b
group by b.id,b.Time with rollup
having grouping(b.Time)=0 or grouping(b.id)=1
go