请赐教一SQL

Question

我再说详细一点,
表tb有5个字段
t1 t2 t3 t4   t5
1  5  6  7    2008-01-02 
3  4  5  5    2008-01-03 
3  4  5  8    2008-01-04 
2  5  7  8    2008-01-05 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 
我们给出:1 2 3  三个数
则查出: 
2  5  7  8    2008-01-05 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 
给出1  2  5 三个数
则查出: 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 给出1 2两个数
查出:
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 注意,规则有三个条件:记录中不包含给出中的任何一个数字,最近的时间(也就是连续有几天没出现过的)

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(7),date 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 '2,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查询如下:DECLARE @str VARCHAR(20);
SET @str='1,2,5';SELECT *,CAST(NULL AS INT) AS flag INTO #T 
FROM @T AS T
WHERE NOT EXISTS(
          SELECT * 
          FROM (
              SELECT
                  SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
              FROM master.dbo.spt_values
              WHERE type='p'
                  AND SUBSTRING(','+@str,number,1)=','
          ) AS A
          WHERE CHARINDEX(','+s+',',','+T.data+',')>0
      )
      DECLARE @min_dt DATETIME;SET @min_dt=(SELECT MIN(date) FROM #T)-1;DECLARE @flag INT;
SET @flag=0;UPDATE #T SET
    @flag=CASE WHEN @min_dt=date-1 THEN @flag ELSE @flag+1 END,
    @min_dt=@min_dt+1,
    flag=@flag;
    
SELECT B.data,B.date
FROM (SELECT MIN(flag) AS flag FROM #T) AS A
    JOIN #T AS B
 ON A.flag=B.flag;DROP TABLE #T;

Answer 2

改一下5楼的..
---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (data VARCHAR(7),date 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 '2,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查询如下:DECLARE @str VARCHAR(20);
SET @str='1,2';SELECT *,CAST(NULL AS INT) AS flag INTO #T 
FROM @T AS T
WHERE NOT EXISTS(
          SELECT * 
          FROM (
              SELECT
                  SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number) AS s
              FROM master.dbo.spt_values
              WHERE type='p'
                  AND SUBSTRING(','+@str,number,1)=','
          ) AS A
          WHERE CHARINDEX(','+s+',',','+T.data+',')>0
      )
      DECLARE @min_dt DATETIME;SET @min_dt=(SELECT MIN(date) FROM #T)-1;DECLARE @flag INT;
SET @flag=0;UPDATE #T SET
    @flag=CASE WHEN @min_dt=date-1 THEN @flag ELSE @flag+1 END,
    @min_dt=date,
    flag=@flag;
    
SELECT B.data,B.date
FROM (SELECT MAX(flag) AS flag FROM #T) AS A
    JOIN #T AS B
 ON A.flag=B.flag;
DROP TABLE #T;

Answer 3

liangCK ,HEROWANG 
很不好意思,多出了个2
是我写错了.
我的规则应该大家明白吧.再调整一下:
我再说详细一点, 
表tb有5个字段 
t1 t2 t3 t4  t5 
1  5  6  7    2008-01-02 
3  4  5  5    2008-01-03 
3  4  5  8    2008-01-04 
4  5  7  8    2008-01-05 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 
我们给出:1 2 3  三个数 
则查出: 
4  5  7  8    2008-01-05 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 
给出1  2  5 三个数 
则查出: 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 给出1 2两个数 
查出: 
6  7  8  9    2008-01-06 
6  7  8  9    2008-01-07 注意,规则有三个条件:记录中不包含给出中的任何一个数字,最近的时间(也就是连续有几天没出现过的)

Answer 4

谢谢,liangCK 
我这个'6,7,8,9'是4个字段.
不用去拆.

Answer 5

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (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查询如下:DECLARE @str VARCHAR(20);
SET @str='1,2,3';SELECT *,CAST(NULL AS INT) AS flag INTO #T 
FROM @T AS T
WHERE CHARINDEX(','+RTRIM(t1)+',',','+@str+',')<=0
    AND CHARINDEX(','+RTRIM(t2)+',',','+@str+',')<=0
    AND CHARINDEX(','+RTRIM(t3)+',',','+@str+',')<=0
    AND 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 #T) AS A
    JOIN #T AS B
 ON A.flag=B.flag;
 
 
DROP TABLE #T;

Answer 6

t1          t2          t3          t4          t5                      
----------- ----------- ----------- ----------- ----------------------- 
3           4           5           5           2008-01-03 00:00:00.000 
3           4           5           8           2008-01-04 00:00:00.000 
4           5           7           8           2008-01-05 00:00:00.000 --------------
这几条记录也不满足吗?..没出现过数字..日期也连续..

Answer 7

最后查的时候.再改改..
SELECT B.*
FROM (SELECT MAX(flag) AS flag FROM #T GROUP BY flag HAVING COUNT(*)>1) AS A
    JOIN #T AS B
 ON A.flag=B.flag;
 

Answer 8

当1,2的时候..返回是
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'请问这几条记录..不最近..且不连续吗?

Answer 9

---------------------------------
--  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 T
WHERE CHARINDEX(','+RTRIM(t1)+',',','+@str+',')<=0
    AND CHARINDEX(','+RTRIM(t2)+',',','+@str+',')<=0
    AND CHARINDEX(','+RTRIM(t3)+',',','+@str+',')<=0
    AND 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 #T GROUP BY flag HAVING COUNT(*)>1) AS A
    JOIN #T AS B
 ON A.flag=B.flag;DROP TABLE #T;
GOEXEC p '1,2,3'EXEC p '1,2,5'EXEC p '1,2'
 
GODROP 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 行受影响)*/

Answer 10

---------------------------------
--  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)
AS
SELECT *,CAST(NULL AS INT) AS flag INTO #T 
FROM T AS T
WHERE CHARINDEX(','+RTRIM(t1)+',',','+@str+',')<=0
    AND CHARINDEX(','+RTRIM(t2)+',',','+@str+',')<=0
    AND CHARINDEX(','+RTRIM(t3)+',',','+@str+',')<=0
    AND 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;
GOEXEC p '1,2,3'EXEC p '1,2,5'EXEC p '1,2'
 
GODROP TABLE T
DROP PROC p/*
t1          t2          t3          t4          t5                      flag
----------- ----------- ----------- ----------- ----------------------- -----------
6           7           8           9           2008-01-07 00:00:00.000 1
6           7           8           9           2008-01-08 00:00:00.000 1(2 行受影响)t1          t2          t3          t4          t5                      flag
----------- ----------- ----------- ----------- ----------------------- -----------
6           7           8           9           2008-01-07 00:00:00.000 0
6           7           8           9           2008-01-08 00:00:00.000 0(2 行受影响)
t1          t2          t3          t4          t5                      flag
----------- ----------- ----------- ----------- ----------------------- -----------
6           7           8           9           2008-01-07 00:00:00.000 1
6           7           8           9           2008-01-08 00:00:00.000 1(2 行受影响)*/

Answer 11

liangCK 太感谢你了
我刚才测试一下你的代码发现有个问题基础数据
t1,t2,t3,t4,t5
1,5,6,7,2008-01-02 00:00:00.000
3,4,5,5,2008-01-03 00:00:00.000
3,4,5,8,2008-01-04 00:00:00.000
4,5,7,8,2008-01-05 00:00:00.000
6,7,8,9,2008-01-06 00:00:00.000
6,7,8,9,2008-01-07 00:00:00.000执行:
SELECT*FROM T
EXEC p '1,6'查出的数据:
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（所影响的行数为 3 行）很明显,基础数据中最近日期(6,7,8,9,2008-01-07 00:00:00.000)中存在一个 6 
我们查来的记录应该为空才对的

Answer 12

t1,t2,t3,t4,t5 
1,5,6,7,2008-01-02 00:00:00.000     
3,4,5,5,2008-01-03 00:00:00.000 
3,4,5,8,2008-01-04 00:00:00.000 
4,5,7,8,2008-01-05 00:00:00.000 
6,7,8,9,2008-01-06 00:00:00.000 
6,7,8,9,2008-01-07 00:00:00.000 最近也就是说,
如果要查的数字5,那么,第4条记录中有出现5,我们就要从那条记录算起往后查.
满足的就:
6,7,8,9,2008-01-06 00:00:00.000 
6,7,8,9,2008-01-07 00:00:00.000 如果要查的数字1,那么,第1条记录中有出现1,我们就要从那条记录算起往后查.
3,4,5,5,2008-01-03 00:00:00.000 
3,4,5,8,2008-01-04 00:00:00.000 
4,5,7,8,2008-01-05 00:00:00.000 
6,7,8,9,2008-01-06 00:00:00.000 
6,7,8,9,2008-01-07 00:00:00.000 如果要查的数字9,那么,第6条(最后一条)记录中有出现9,我们就要从那条记录算起往后查.不存在记录
---------------------------
其实我们可以想像为我们去买采票 
我们现在要选几个码,但我想知道最近多少期没开过这些码. 
这样可能更好理解

Answer 13

---------------------------------
--  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;
GOEXEC p '1,2,5',2 --后两天没出现过1,2,5
 
GODROP TABLE T
DROP PROC p

Answer 14

---------------------------------
--  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;
GOEXEC p '1,2,3'EXEC p '1,2,5'EXEC p '1,2'EXEC p '1,6'
 
GODROP 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 行受影响)*/

Answer 15

39楼完全正确了.
谢谢liangCK 今天的热心帮忙.但现在还有个问题.
我的数据库是 access
你看能否让其在access
也能运行?

Answer 16

Access不能运行这个...对Access不太了解..

Answer 17

以下代码，经过测试，没有问题。
楼上有的是把前面的字符串分成了几个字段。事实是就只有这一个字段，解决方法如下：--创建测试数据
if object_id('test') is not null drop table test  
create table test (vName varchar(20), vDate smalldatetime) --插入数据
insert into test (vName, vDate)
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 '2,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'  union all 
select '4,7,8,9', '2008-01-10'  union all
select '5,7,8,9', '2008-01-11'   --设置匹配字符串(如1,2,3)
Declare @str  varchar(20)
Select @str = '1,2,3' --************************操作步骤
--1.将初始数据放入临时表
Select vName,vDate into #Result From  test --2.先提取满足要求的记录
   Declare @i int ,@Mark varchar(10) 
   Select @i = 0 ,@Mark = '' 
   while (@i < len(@str) + 1)  
   Begin
      if (substring(@str,@i,1) = ',')
      Begin
          select @i =  @i + 1 
      End
      Else 
      Begin
          Select @Mark = substring(@str,@i,1) 
          if (@Mark <> '') 
          Begin
          --删除在里面已经存在的记录
          Delete From  #Result  Where vName like '%' + @Mark + '%'  
          End 
         select @i =  @i + 1 
      End
   End
--3.在结果中筛选连续日期
  select * From 
  (
  Select LB.vName, LB.vDate  
  From  
  (
  Select  LO.vName,     LO.vDate , (Select min(vDate) From  #Result where vDate >LO.vDate) as fNextDate 
  From   #Result LO ) LB 
  where datediff(d,LB.vDate,LB.fNextDate) = 1    
  union all 
  Select LC.vName, LC.vDate 
  From 
  (
  Select  LO.vName,     LO.vDate , (Select max(vDate) From  #Result where vDate <LO.vDate) as fLastDate 
  From   #Result LO  ) LC 
  where datediff(d,LC.vDate,LC.fLastDate) = -1 
) AC order by AC.vDate  
   
 
 
--删除测试数据 
drop table #Result 
drop table test 
 

Answer 18

以上代码在ACCESS中肯定可以运行！