我有个问题，
我的桌子有以下数据：
userID, startTime, EndTime ————————————— 101, 04/11/2013 11:00:00, 04/11/2013 11:55:00 102, 04/11/2013 11:00:00, 04/11/2013 11:24:00 103, 04/11/2013 11:20:00, 04/11/2013 11:45:00 104, 04/11/2013 11:30:00, 04/11/2013 11:35:00 105, 04/11/2013 11:40:00, 04/11/2013 11:55:00 我可以每10分钟使用视图来显示备份状态吗？
我不知道结果如下：
time, count —————————— 04/11/2013 11:00:00, 2 04/11/2013 11:10:00, 2 04/11/2013 11:20:00, 3 04/11/2013 11:30:00, 3 04/11/2013 11:40:00, 3 04/11/2013 11:50:00, 2 04/11/2013 12:00:00, 0 04/11/2013 11:00:00 – 04/11/2013 11:09:59 have 2 jobs, 101 & 102 04/11/2013 11:10:00 – 04/11/2013 11:19:59 have 2 jobs, 101 & 102 04/11/2013 11:20:00 – 04/11/2013 11:29:59 have 3 jobs, 101 & 102 & 103 … 04/11/2013 11:50:00 – 04/11/2013 11:59:59 have 2 jobs, 101 & 105 04/11/2013 12:00:00 – 04/11/2013 12:09:59 have 0 job
我想知道你能否给我一个帮助……非常感谢
不是真正的基于代码的解决scheme的地方（见堆栈溢出），但我会咬：
首先，我假设一个名为login的表格，其中包含以下数据…
IF OBJECT_ID('tempdb..logins') IS NOT NULL DROP TABLE logins; GO CREATE TABLE logins (userID INT, startTime DATETIME, endTime DATETIME) GO INSERT INTO logins (userID, startTime, endTime) VALUES (101, '2013-11-04T11:00:00', '2013-11-04T11:00:00') ,(102, '2013-11-04T11:00:00', '2013-11-04T11:24:00') ,(103, '2013-11-04T11:20:00', '2013-11-04T11:45:00') ,(104, '2013-11-04T11:30:00', '2013-11-04T11:35:00') ,(105, '2013-11-04T11:40:00', '2013-11-04T11:55:00'); GO
如果你只是想知道10分钟的login时间，一个很好的简单（虽然有点神奇）的方法是使用零date…
SELECT period_start = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, l.startTime) / 10 * 10, 0), l.* FROM logins l GO
…所以简单的计数，基于期限…
WITH starts AS ( SELECT period_start = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, l.startTime) / 10 * 10, 0), l.* FROM logins l ) SELECT period_start, login_count = COUNT(1) FROM starts GROUP BY period_start GO
但是，如果你想显示0计数的差距，你需要一个函数来获取所有的date，然后左键连接到它…
-- periods: a table-function to get a series of date/times intervals between two dates -- (tally approach taken from Itzik Ben-Gan's article http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers) IF OBJECT_ID('tempdb..periods') IS NOT NULL DROP FUNCTION periods; GO CREATE FUNCTION periods( @start_date DATETIME2, @end_date DATETIME2 = GETDATE, @incr_mins INT = 10 ) RETURNS TABLE AS RETURN ( WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), -- 10^1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 10^2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 10^4 or 10,000 rows E8(N) AS (SELECT 1 FROM E4 a, E4 b), -- 10^8 or 100,000,000 rows E16(N) AS (SELECT 1 FROM E8 a, E8 b), -- 10^16 or 10,000,000,000,000,000 rows E32(N) AS (SELECT 1 FROM E16 a, E16 b), -- 10^32 or 100,000,000,000,000,000,000,000,000,000,000 rows E64(N) AS (SELECT 1 FROM E32 a, E32 b), -- 10^64 or 10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 rows starts AS ( SELECT TOP (CEILING(ABS(DATEDIFF(MINUTE, @start_date, @end_date)) / @incr_mins)) period_start = DATEADD(MINUTE, @incr_mins * (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), @start_date) FROM E64 ), ends AS ( SELECT period_start, period_end = DATEADD(MINUTE, @incr_mins, period_start) FROM starts ) SELECT period_start, period_end FROM ends ) GO
现在，您可以使用该function来统计login到期间…
SELECT p.period_start, login_count = SUM(CASE WHEN l.userID IS NULL THEN 0 ELSE 1 END) FROM periods('2013-11-04T00:00:00', '2013-11-05T00:00:00', 10) p LEFT JOIN logins l ON l.startTime >= p.period_start AND l.startTime < p.period_end GROUP BY p.period_start GO
请注意，调用函数有点贵，所以最好填充一个索引良好的“日历”表，预先计算所有的期间，然后join。
希望这可以帮助。
Ĵ