按时间段进行统计

USE [Book]
GO
/****** 对象:  Table [dbo].[tt]    脚本日期: 06/15/2009 09:31:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tt](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [prive] [int] NULL,
    [DTime] [smalldatetime] NULL,
    [Classes] [int] NULL,
 CONSTRAINT [PK_tt] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


insert into tt(title,prive,Dtime,Classes)
values('aaaaa',20,'2009-06-12 08:00:00',1)

insert into tt(title,prive,Dtime,Classes)
values('bbbbb',30,'2009-06-12 08:11:00',1)
insert into tt(title,prive,Dtime,Classes)
values('cccccc',20,'2009-06-12 08:32:00',1)
insert into tt(title,prive,Dtime,Classes)
values('ddddd',30,'2009-06-12 08:41:00',1)
insert into tt(title,prive,Dtime,Classes)
values('aaaaa',20,'2009-06-12 08:00:00',2)
insert into tt(title,prive,Dtime,Classes)
values('bbbbb',30,'2009-06-12 08:11:00',2)
insert into tt(title,prive,Dtime,Classes)
values('cccccc',20,'2009-06-12 08:32:00',2)
insert into tt(title,prive,Dtime,Classes)
values('ddddd',30,'2009-06-12 08:41:00',2)

/*
这里我是按三十分钟,如果想按别时间段分段统计，看到下面的30了吗。改成你要的数
*/
select dateadd(mi,(datediff(mi,convert(varchar(10),DTime,112),DTime)/30+1)*30,
convert(varchar(10),Dtime,112)) as 时间段,
count(*) as 行数,sum(prive) as 总金额
from tt
group by dateadd(mi,(datediff(mi,convert(varchar(10),DTime,112),DTime)/30+1)*30,
convert(varchar(10),DTime,112)),classes