22,301
社区成员




---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
CREATE TABLE T(t1 INT,t2 INT,t3 INT,t4 INT,t5 DATETIME)
INSERT INTO T
SELECT 1,5,6,7,'2008-01-02' UNION ALL
SELECT 3,4,5,5,'2008-01-03' UNION ALL
SELECT 3,4,5,8,'2008-01-04' UNION ALL
SELECT 4,5,7,8,'2008-01-05' UNION ALL
SELECT 6,7,8,9,'2008-01-06' UNION ALL --这里改改
SELECT 6,7,8,9,'2008-01-07'
--SQL查询如下:
GO
CREATE PROC p @str VARCHAR(20)
AS
SELECT *,CAST(NULL AS INT) AS flag INTO #T
FROM T AS A
WHERE CHARINDEX(','+RTRIM(t1)+',',','+@str+',')<=0
AND CHARINDEX(','+RTRIM(t2)+',',','+@str+',')<=0
AND CHARINDEX(','+RTRIM(t3)+',',','+@str+',')<=0
AND CHARINDEX(','+RTRIM(t4)+',',','+@str+',')<=0
AND NOT EXISTS(
SELECT * FROM T
WHERE t5>A.t5
AND (CHARINDEX(','+RTRIM(t1)+',',','+@str+',')>0
OR CHARINDEX(','+RTRIM(t2)+',',','+@str+',')>0
OR CHARINDEX(','+RTRIM(t3)+',',','+@str+',')>0
OR CHARINDEX(','+RTRIM(t4)+',',','+@str+',')>0)
)
DECLARE @min_dt DATETIME;
SET @min_dt=(SELECT MIN(t5) FROM #T)-1;
DECLARE @flag INT;
SET @flag=0;
UPDATE #T SET
@flag=CASE WHEN @min_dt=t5-1 THEN @flag ELSE @flag+1 END,
@min_dt=t5,
flag=@flag;
SELECT B.*
FROM (
SELECT MAX(flag) AS flag
FROM (
SELECT MAX(flag) AS flag FROM #T GROUP BY flag HAVING COUNT(*)>1
) AS T
) AS A
JOIN #T AS B
ON A.flag=B.flag;
DROP TABLE #T;
GO
EXEC p '1,2,3'
EXEC p '1,2,5'
EXEC p '1,2'
EXEC p '1,6'
GO
DROP TABLE T
DROP PROC p
/*
t1 t2 t3 t4 t5 flag
----------- ----------- ----------- ----------- ----------------------- -----------
4 5 7 8 2008-01-05 00:00:00.000 0
6 7 8 9 2008-01-06 00:00:00.000 0
6 7 8 9 2008-01-07 00:00:00.000 0
(3 行受影响)
t1 t2 t3 t4 t5 flag
----------- ----------- ----------- ----------- ----------------------- -----------
6 7 8 9 2008-01-06 00:00:00.000 0
6 7 8 9 2008-01-07 00:00:00.000 0
(2 行受影响)
t1 t2 t3 t4 t5 flag
----------- ----------- ----------- ----------- ----------------------- -----------
3 4 5 5 2008-01-03 00:00:00.000 0
3 4 5 8 2008-01-04 00:00:00.000 0
4 5 7 8 2008-01-05 00:00:00.000 0
6 7 8 9 2008-01-06 00:00:00.000 0
6 7 8 9 2008-01-07 00:00:00.000 0
(5 行受影响)
t1 t2 t3 t4 t5 flag
----------- ----------- ----------- ----------- ----------------------- -----------
(0 行受影响)
*/
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
CREATE TABLE T(t1 INT,t2 INT,t3 INT,t4 INT,t5 DATETIME)
INSERT INTO T
SELECT 1,5,6,7,'2008-01-02' UNION ALL
SELECT 3,4,5,5,'2008-01-03' UNION ALL
SELECT 3,4,5,8,'2008-01-04' UNION ALL
SELECT 4,5,7,8,'2008-01-05' UNION ALL
SELECT 6,7,8,9,'2008-01-07' UNION ALL --这里改改
SELECT 6,7,8,9,'2008-01-08'
--SQL查询如下:
GO
CREATE PROC p @str VARCHAR(20),@day INT --后几天
AS
SELECT *,CAST(NULL AS INT) AS flag INTO #T
FROM T AS A
WHERE CHARINDEX(','+RTRIM(t1)+',',','+@str+',')<=0
AND CHARINDEX(','+RTRIM(t2)+',',','+@str+',')<=0
AND CHARINDEX(','+RTRIM(t3)+',',','+@str+',')<=0
AND CHARINDEX(','+RTRIM(t4)+',',','+@str+',')<=0
AND NOT EXISTS(
SELECT *
FROM T AS B
WHERE t5 > DATEADD(day,@day-1,A.t5)
AND (CHARINDEX(','+RTRIM(t1)+',',','+@str+',')>0
OR CHARINDEX(','+RTRIM(t2)+',',','+@str+',')>0
OR CHARINDEX(','+RTRIM(t3)+',',','+@str+',')>0
OR CHARINDEX(','+RTRIM(t4)+',',','+@str+',')>0)
)
DECLARE @min_dt DATETIME;
SET @min_dt=(SELECT MIN(t5) FROM #T)-1;
DECLARE @flag INT;
SET @flag=0;
UPDATE #T SET
@flag=CASE WHEN @min_dt=t5-1 THEN @flag ELSE @flag+1 END,
@min_dt=t5,
flag=@flag;
SELECT B.*
FROM (
SELECT MAX(flag) AS flag
FROM (
SELECT MAX(flag) AS flag FROM #T GROUP BY flag HAVING COUNT(*)>1
) AS T
) AS A
JOIN #T AS B
ON A.flag=B.flag;
DROP TABLE #T;
GO
EXEC p '1,2,5',2 --后两天没出现过1,2,5
GO
DROP TABLE T
DROP PROC p