34,590
社区成员
发帖
与我相关
我的任务
分享
/* 测试数据
WITH table1(序号,a,日期)AS(
SELECT 1,'x','2011-01-01 11:56' UNION ALL
SELECT 2,'y','2011-01-01 12:12' UNION ALL
SELECT 3,'z','2011-01-01 12:33'
)*/
SELECT COUNT(*) 个数,
DATEPART(hour,日期) 小时
FROM table1
GROUP BY DATEPART(hour,日期)
WITH /* 测试数据
table1(序号,a,日期)AS(
SELECT 1,'x','2011-01-01 11:56' UNION ALL
SELECT 2,'y','2011-01-01 12:12' UNION ALL
SELECT 3,'z','2011-01-01 12:33'
),*/
a AS(
SELECT COUNT(*) 个数,
(DATEPART(hour,日期)*60+DATEPART(minute,日期))/20 i
FROM table1
GROUP BY (DATEPART(hour,日期)*60+DATEPART(minute,日期))/20
)
,b AS (
SELECT number AS i,
CONVERT(varchar(5),DATEADD(minute,number*20,0),8)+'-'+
CONVERT(varchar(5),DATEADD(minute,number*20+19,0),8) 时间段
FROM master..spt_values
WHERE type = 'p'
AND number < 72
)
SELECT a.个数,
b.时间段
FROM a
JOIN b
ON a.i = b.i
个数 时间段
----------- -----------
1 11:40-11:59
1 12:00-12:19
1 12:20-12:39
/* 测试数据
WITH table1(序号,a,日期)AS(
SELECT 1,'x','2011-01-01 11:56' UNION ALL
SELECT 2,'y','2011-01-01 12:12' UNION ALL
SELECT 3,'z','2011-01-01 12:33'
)*/
SELECT COUNT(*) 个数,
(DATEPART(hour,日期)*60+DATEPART(minute,日期))/20 二十分钟
FROM table1
GROUP BY (DATEPART(hour,日期)*60+DATEPART(minute,日期))/20
个数 二十分钟
----------- -----------
1 35
1 36
1 37