姓名字段(names) 购买日期(adddate)
葛* 2010-04-02 23:53:18.837
张* 2010-04-09 00:12:43.980
陶* 2010-04-03 00:34:53.190
*** 2010-04-07 03:53:55.253
闫* 2010-04-03 08:25:54.857
王* 2010-05-05 09:34:32.750
杨* 2010-04-03 10:13:44.130
周* 2010-04-03 11:26:09.110
李* 2010-02-03 22:54:57.620
斯* 2010-04-03 23:34:39.160
赖* 2010-01-04 02:00:50.377
张* 2010-04-04 12:14:06.240 想统计出这样的结果(利用小时段进行汇总,比如这个时间 2010-04-02 23:53:18.837 取的汇总关键字就是 小时:23点,统计出23点从00分到59分内的所有count(*)数据)查询结果如下:(时间段:购买人数)00:2
02:1
03:1
08:1
09:1
10:1
......
......
......
23:2
葛* 2010-04-02 23:53:18.837
张* 2010-04-09 00:12:43.980
陶* 2010-04-03 00:34:53.190
*** 2010-04-07 03:53:55.253
闫* 2010-04-03 08:25:54.857
王* 2010-05-05 09:34:32.750
杨* 2010-04-03 10:13:44.130
周* 2010-04-03 11:26:09.110
李* 2010-02-03 22:54:57.620
斯* 2010-04-03 23:34:39.160
赖* 2010-01-04 02:00:50.377
张* 2010-04-04 12:14:06.240 想统计出这样的结果(利用小时段进行汇总,比如这个时间 2010-04-02 23:53:18.837 取的汇总关键字就是 小时:23点,统计出23点从00分到59分内的所有count(*)数据)查询结果如下:(时间段:购买人数)00:2
02:1
03:1
08:1
09:1
10:1
......
......
......
23:2
go
create table [tb]([names] varchar(3),[adddate] datetime)
insert [tb]
select '葛*','2010-04-02 23:53:18.837' union all
select '张*','2010-04-09 00:12:43.980' union all
select '陶*','2010-04-03 00:34:53.190' union all
select '***','2010-04-07 03:53:55.253' union all
select '闫*','2010-04-03 08:25:54.857' union all
select '王*','2010-05-05 09:34:32.750' union all
select '杨*','2010-04-03 10:13:44.130' union all
select '周*','2010-04-03 11:26:09.110' union all
select '李*','2010-02-03 22:54:57.620' union all
select '斯*','2010-04-03 23:34:39.160' union all
select '赖*','2010-01-04 02:00:50.377' union all
select '张*','2010-04-04 12:14:06.240'
goselect datepart(dd,adddate)时间段,count(*)购买人数
from tb group by datepart(dd,adddate)
/**
时间段 购买人数
----------- -----------
2 1
3 6
4 2
5 1
7 1
9 1(6 行受影响)
**/
select datepart(hh,adddate)时间段,count(*)购买人数
from tb group by datepart(hh,adddate)
/**
时间段 购买人数
----------- -----------
0 2
2 1
3 1
8 1
9 1
10 1
11 1
12 1
22 1
23 2(10 行受影响)**/
select convert(varchar(2),adddate,14)+':'+ltrim(count(*)) [时间段:购买人数]
from tb group by convert(varchar(2),adddate,14)/*时间段:购买人数
---------------
00:2
02:1
03:1
08:1
09:1
10:1
11:1
12:1
22:1
23:2*/
drop table table1
go
create table table1
(
[name] varchar(20),
addate datetime
)
go
insert into table1([name],addate)
(
select '葛*','2010-04-02 23:53:18.837' union
select '张*','2010-04-09 00:12:43.980' union
select '陶*','2010-04-03 00:34:53.190' union
select '闫*','2010-04-03 08:25:54.857' union
select '王*','2010-05-05 09:34:32.750' union
select '杨*','2010-04-03 10:13:44.130' union
select '周*','2010-04-03 11:26:09.110' union
select '李*','2010-02-03 22:54:57.620' union
select '斯*','2010-04-03 23:34:39.160' union
select '赖*','2010-01-04 02:00:50.377' union
select '张*','2010-04-04 12:14:06.240'
)
go
select Convert(varchar(10),datepart(HH,addate))+':'+ltrim(count(*)) [时间段:购买人数] from table1 group by datepart(HH,addate)
go
from tb group by datepart(day,adddate),datepart(hour,adddate)
from tb group by datename(hh,adddate)