SQL问题 ：在一张表里头过滤数据

Question

在一张表里头过滤数据，里头有个ID和时间  ID不唯一   要求过滤掉同一个ID的数据5分钟之类的记录，sql实现 
如：
ID time
1  8:00
1  8:01
1  8:03
1  8:04
1  8:05
1  8:06
2  8:03
2  8:04
2  8:05
2  8:06
2  8:07
查出来就只有1  8:00  1  8:06 2  8:03

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,time DATETIME)
INSERT INTO @T
SELECT 1,'8:00' UNION ALL
SELECT 1,'8:01' UNION ALL
SELECT 1,'8:03' UNION ALL
SELECT 1,'8:04' UNION ALL
SELECT 1,'8:05' UNION ALL
SELECT 1,'8:06' UNION ALL
SELECT 2,'8:03' UNION ALL
SELECT 2,'8:04' UNION ALL
SELECT 2,'8:05' UNION ALL
SELECT 2,'8:06' UNION ALL
SELECT 2,'8:07'--SQL查询如下:DECLARE @min INT
SET @min=5SELECT
    ID,time
FROM (
    SELECT
        ID,time,
        DATEDIFF(minute,0,time)/(@min+1) AS gid
    FROM @T
)  AS A
WHERE NOT EXISTS(
           SELECT *
           FROM (
               SELECT
                  ID,time,
                  DATEDIFF(minute,0,time)/(@min+1) AS gid
               FROM @T
           ) AS B
           WHERE A.ID=ID AND A.gid=gid AND time<A.time
      )/*
ID          time
----------- -----------------------
1           1900-01-01 08:00:00.000
1           1900-01-01 08:06:00.000
2           1900-01-01 08:03:00.000
2           1900-01-01 08:06:00.000(4 行受影响)*/

Answer 2

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,time DATETIME)
INSERT INTO @T
SELECT 1,'8:00' UNION ALL
SELECT 1,'8:01' UNION ALL
SELECT 1,'8:03' UNION ALL
SELECT 1,'8:04' UNION ALL
SELECT 1,'8:05' UNION ALL
SELECT 1,'8:06' UNION ALL
SELECT 2,'8:03' UNION ALL
SELECT 2,'8:04' UNION ALL
SELECT 2,'8:05' UNION ALL
SELECT 2,'8:06' UNION ALL
SELECT 2,'8:07'--SQL查询如下:DECLARE @min INT
SET @min=5SELECT
    ID,time
FROM (
    SELECT
        ID,time,
        DATEDIFF(minute,(SELECT MIN(time)
                         FROM @T
                         WHERE A.ID=ID),time)/(@min+1) AS gid
    FROM @T AS A
)  AS A
WHERE NOT EXISTS(
           SELECT *
           FROM (
               SELECT
                   ID,time,
                   DATEDIFF(minute,(SELECT MIN(time)
                                    FROM @T
                                    WHERE A.ID=ID),time)/(@min+1) AS gid
               FROM @T AS A
           ) AS B
           WHERE A.ID=ID AND A.gid=gid AND time<A.time
      )/*
ID          time
----------- -----------------------
1           1900-01-01 08:00:00.000
1           1900-01-01 08:06:00.000
2           1900-01-01 08:03:00.000(3 行受影响)*、

Answer 3

IF NOT OBJECT_ID('TB') IS NULL  DROP TABLE TB
GO
CREATE TABLE TB([ID] INT,[TIME] DATETIME)
INSERT TB
SELECT 1,'8:00' UNION ALL
SELECT 1,'8:01' UNION ALL
SELECT 1,'8:03' UNION ALL
SELECT 1,'8:04' UNION ALL
SELECT 1,'8:05' UNION ALL
SELECT 1,'8:06' UNION ALL
SELECT 2,'8:03' UNION ALL
SELECT 2,'8:04' UNION ALL
SELECT 2,'8:05' UNION ALL
SELECT 2,'8:06' UNION ALL
SELECT 2,'8:07'
GOSELECT ID,MIN(TIME) FROM TB  GROUP BY  (DATEDIFF(minute,'1900-01-01 08:00:00.000',TIME)-1)/5,ID
/*
ID                                                                 
----------- ------------------------------------------------------ 
1           1900-01-01 08:00:00.000
1           1900-01-01 08:06:00.000
2           1900-01-01 08:03:00.000
2           1900-01-01 08:06:00.000（所影响的行数为 4 行）
*/

Answer 4

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,time DATETIME)
INSERT INTO @T
SELECT 1,'8:00' UNION ALL
SELECT 1,'8:01' UNION ALL
SELECT 1,'8:03' UNION ALL
SELECT 1,'8:04' UNION ALL
SELECT 1,'8:05' UNION ALL
SELECT 1,'8:06' UNION ALL
SELECT 2,'8:03' UNION ALL
SELECT 2,'8:04' UNION ALL
SELECT 2,'8:05' UNION ALL
SELECT 2,'8:06' UNION ALL
SELECT 2,'8:07'--SQL查询如下:DECLARE @min INT
SET @min=5SELECT
    ID,
    CONVERT(VARCHAR(5),time,108) AS time
FROM (
    SELECT
        ID,time,
        DATEDIFF(minute,(SELECT MIN(time)
                         FROM @T
                         WHERE A.ID=ID),time)/(@min+1) AS gid
    FROM @T AS A
)  AS A
WHERE NOT EXISTS(
           SELECT *
           FROM (
               SELECT
                   ID,time,
                   DATEDIFF(minute,(SELECT MIN(time)
                                    FROM @T
                                    WHERE A.ID=ID),time)/(@min+1) AS gid
               FROM @T AS A
           ) AS B
           WHERE A.ID=ID AND A.gid=gid AND time<A.time
      )/*
ID          time
----------- -----
1           08:00
1           08:06
2           08:03(3 行受影响)*/

Answer 5

IF NOT OBJECT_ID('TB') IS NULL  DROP TABLE TB
GO
CREATE TABLE TB([ID] INT,[TIME] DATETIME)
INSERT TB
SELECT 1,'8:00' UNION ALL
SELECT 1,'8:01' UNION ALL
SELECT 1,'8:03' UNION ALL
SELECT 1,'8:04' UNION ALL
SELECT 1,'8:05' UNION ALL
SELECT 1,'8:06' UNION ALL
SELECT 2,'8:03' UNION ALL
SELECT 2,'8:04' UNION ALL
SELECT 2,'8:05' UNION ALL
SELECT 2,'8:06' UNION ALL
SELECT 2,'8:07'
GOSELECT A.ID,MIN(A.TIME) AS MINTIME FROM TB A
LEFT JOIN (
SELECT ID,MIN(TIME)AS TIME FROM TB  GROUP BY ID)B
 ON A.ID=B.ID
GROUP BY (DATEDIFF(MINUTE,B.TIME,A.TIME)-1)/5,A.ID
/*
ID          MINTIME                                                
----------- ------------------------------------------------------ 
1           1900-01-01 08:00:00.000
1           1900-01-01 08:06:00.000
2           1900-01-01 08:03:00.000（所影响的行数为 3 行）*/