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
按时间段进行统计
最新推荐文章于 2022-06-03 10:07:05 发布