MSSQL中如何查询出连续相关的的记录

[tianwei76]

表格如下所示


ID 期数 成绩

1 34 不合格
2 35 合格
3 36 合格
4 37 不合格
5 38 不合格
6 39 不合格
7 40 不合格
8 41 合格
11 42 合格


我要查询出最大连续不合格期数大于3的记录。

[tianwei76]

4     37      不合格
5     38      不合格
6     39      不合格
7     40     不合格

查出如上面的结果

[吉普赛的歌]

DECLARE @t TABLE ([ID] INT,[期数] int,[成绩] NVARCHAR(20))
INSERT INTO @t VALUES (1,34,'不合格')
INSERT INTO @t VALUES (2,35,'合格')
INSERT INTO @t VALUES (3,36,'合格')
INSERT INTO @t VALUES (4,37,'不合格')
INSERT INTO @t VALUES (5,38,'不合格')
INSERT INTO @t VALUES (6,39,'不合格')
INSERT INTO @t VALUES (7,40,'不合格')
INSERT INTO @t VALUES (8,41,'合格')
INSERT INTO @t VALUES (11,42,'合格')
--以上为测试数据
--SELECT * FROM @t
;WITH c AS
(
    SELECT [期数],
        ROW_NUMBER()OVER(ORDER BY [期数]) AS rownum
    FROM   @t  WHERE 成绩='合格'
)
, ran AS(
SELECT 
cur.[期数] + 1  AS startRange
,nxt.[期数] -1   AS endRange
FROM   c           AS cur
       JOIN c      AS nxt
            ON  nxt.rownum = cur.rownum + 1
WHERE  nxt.[期数] -cur.[期数] > 1
)
SELECT [ID],[期数],[成绩] FROM @T t cross apply ran 
WHERE t.[期数] between ran.startRange and ran.endRange

[吉普赛的歌]

[吉普赛的歌]

#2改成：

DECLARE @t TABLE ([ID] INT,[期数] int,[成绩] NVARCHAR(20))
INSERT INTO @t VALUES (1,34,'不合格')
INSERT INTO @t VALUES (2,35,'合格')
INSERT INTO @t VALUES (3,36,'合格')
INSERT INTO @t VALUES (4,37,'不合格')
INSERT INTO @t VALUES (5,38,'不合格')
INSERT INTO @t VALUES (6,39,'不合格')
INSERT INTO @t VALUES (7,40,'不合格')
INSERT INTO @t VALUES (8,41,'合格')
INSERT INTO @t VALUES (11,42,'合格')
--以上为测试数据
--SELECT * FROM @t
;WITH c AS
(
    SELECT [期数],
        ROW_NUMBER()OVER(ORDER BY [期数]) AS rownum
    FROM   @t  WHERE 成绩='合格'
)
, ran AS(
SELECT 
cur.[期数] + 1  AS startRange
,nxt.[期数] -1   AS endRange
FROM   c           AS cur
       JOIN c      AS nxt
            ON  nxt.rownum = cur.rownum + 1
WHERE  nxt.[期数] -cur.[期数] > 1
)
SELECT [ID],[期数],[成绩] FROM @T t cross apply ran 
WHERE t.[期数] between ran.startRange and ran.endRange
AND ran.endRange-ran.startRange>=3

[二月十六]

试试这个：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[期数] int,[成绩] nvarchar(23))
Insert #T
select 1,34,N'不合格' union all
select 2,35,N'合格' union all
select 3,36,N'合格' union all
select 4,37,N'不合格' union all
select 5,38,N'不合格' union all
select 6,39,N'不合格' union all
select 7,40,N'不合格' union all
select 8,41,N'合格' union all
select 11,42,N'合格'
Go
--测试数据结束
;WITH ctea AS (
Select *,ROW_NUMBER()OVER(ORDER BY ID) num1 from #T
),cteb AS (
SELECT *,num1-ROW_NUMBER()OVER(PARTITION BY 成绩 ORDER BY 成绩) AS num2 FROM ctea
)
SELECT  ID ,
        期数 ,
        成绩
FROM    cteb
WHERE   num2 IN ( SELECT    num2
                  FROM      cteb
                  GROUP BY  num2
                  HAVING    COUNT(1) >= 4 )

[道素]

select * from (
    select *,count(0)over(partition by rid) as cnt from (
        select *,ID-row_number()over(order by ID) as rid  from @T where [成绩]=N'不合格'
    ) as a
) as b where b.cnt>3

  	ID	期数	成绩	rid	cnt
1	4	37	不合格	2	4
2	5	38	不合格	2	4
3	6	39	不合格	2	4
4	7	40	不合格	2	4