表内容如下 ----------------------------- ID LogTime 1 2008/10/10 10:00:00 1 2008/10/10 10:03:00 1 2008/10/10 10:09:00 2 2008/10/10 10:10:00 2 2008/10/10 10:11:00 -----------------------------
如何查询登陆时间间隔不超过5分钟的所有记录
CREATE TABLE #t (id INT ,LogTime DATETIME) INSERT INTO #t SELECT 1,' 2008/ 10/ 10 10: 00: 00' UNION SELECT 1,' 2008/ 10/ 10 10: 03: 00' UNION SELECT 1,' 2008/ 10/ 10 10: 09: 00' UNION SELECT 2 ,' 2008/ 10/ 10 10: 10: 00' UNION SELECT 2 ,' 2008/ 10/ 10 10: 11: 00' SELECT id , logtime FROM ( SELECT * , ( SELECT DATEDIFF(n, a.LogTime, b.LogTime) FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS iid , * FROM #t ) B WHERE a.iid = b.iid - 1 AND DATEDIFF(n, a.LogTime, b.LogTime) <= 5 -- ) AS diff FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS iid , * FROM #t ) A ) B WHERE diff IS NOT NULL