mssql 筛选出 一个表中连续重复三次以及以上的记录

[hzf960920876]

[hzf960920876]

[繁花尽流年]

SELECT a.* 
FROM tablename a
INNER JOIN (
	SELECT resu,matcode 
	FROM tablename
	GROUP BY resu,matcode HAVING COUNT(*)>=3
) b ON a.resu=b.resu AND a.matcode=b.matcode

[hzf960920876]

引用 2 楼 zengertao 的回复:

SELECT a.* 
FROM tablename a
INNER JOIN (
	SELECT resu,matcode 
	FROM tablename
	GROUP BY resu,matcode HAVING COUNT(*)>=3
) b ON a.resu=b.resu AND a.matcode=b.matcode

这个还是不行哦，没有得到正确的结果

[繁花尽流年]

你把样本数据贴出来，再把结果也贴出来，方便别人测试

[RINK_1]

SELECT * 
FROM table A
WHERE EXISTS (SELECT 1 FROM table WHERE matcode=A.matcode and resu=2 group by matcode having COUNT(1)>3)
and resu=2

[小灰狼]

select * from tablename t1 where exists ( select * from tablename t2 where t1.matcode = t2.matcode and t1.resu = t2.resu and t1.id <> t2.id and exists( select * from tablename t3 where t2.matcode = t3.matcode and t2.resu = t3.resu and t1.id <> t2.id and t2.id <> t3.id ) );

[小灰狼]

select t1.*
from 
    tablename t1
    left join (
    	select matcode, resu, count(Id) as cnt
    	from tablename 
    	group by matcode, resu
    ) t2 on t1.matcode = t2.matcode and t1.resu = t2.resu
where t2.cnt >= 3;

[hzf960920876]

先发上样本数据

create table #table
(
	id int,
	resu int,
	memo varchar(20)
)

insert into #table values(1,2,'不合格')
insert into #table values(2,2,'不合格')
insert into #table values(3,2,'不合格')
insert into #table values(4,1,'合格')
insert into #table values(5,2,'不合格')
insert into #table values(6,2,'不合格')
insert into #table values(7,2,'不合格')
insert into #table values(8,1,'合格')
insert into #table values(9,2,'不合格')
insert into #table values(10,2,'不合格')
insert into #table values(11,1,'合格')

select * from #table

[hzf960920876]

需要得到结果：筛选出不连续不合格数大于等于三次的数据，查询结果如下

[RINK_1]

with cte
as 
(select *,ROW_NUMBER() over (order by id) as seq_1 from #T)

select * 
from
(select *,COUNT(1) over (partition by resu,seq_1-seq_2) as count_qty
from
(select *,
ROW_NUMBER() over (partition by resu,memo order by seq_1) as seq_2
from cte) as A
where memo='不合格') as A
where count_qty>=3

[繁花尽流年]

 SELECT * 
 FROM (
	 SELECT COUNT(t.id-t.diff) AS times,MIN(t.id) AS min_id,MAX(id) AS max_id 
	 FROM (
		 SELECT a.*,ROW_NUMBER() OVER(ORDER BY a.id-(SELECT MIN(id) FROM #table)) AS diff
		 FROM #table a
		 WHERE a.resu=2
	 ) t
	 GROUP BY (t.id-t.diff)
 ) s
 WHERE s.times>=3

times min_id max_id ----------- ----------- ----------- 3 1 3 3 5 7

[吉普赛的歌]

IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table
--
create table #table
(
    id int,
    resu int,
    memo varchar(20)
)
--
insert into #table values(1,2,'不合格')
insert into #table values(2,2,'不合格')
insert into #table values(3,2,'不合格')
insert into #table values(4,1,'合格')
insert into #table values(5,2,'不合格')
insert into #table values(6,2,'不合格')
insert into #table values(7,2,'不合格')
insert into #table values(8,1,'合格')
insert into #table values(9,2,'不合格')
insert into #table values(10,2,'不合格')
insert into #table values(11,1,'合格')

--加序号，防止id打乱的情况
;WITH cte AS(
	SELECT ROW_NUMBER() OVER (ORDER BY id) AS rid,* from #table
)
,cte2 AS(
	SELECT a.* FROM cte AS a 
		INNER JOIN cte AS b ON a.rid=b.rid-1 AND a.memo='不合格' AND b.memo='不合格'
		INNER JOIN cte AS c ON a.rid=c.rid-2 AND a.memo='不合格' AND c.memo='不合格'
)
SELECT * FROM cte WHERE rid IN (
	SELECT rid FROM cte2
	UNION ALL
	SELECT rid+1 FROM cte2
	UNION ALL
	SELECT rid+2 FROM cte2
)
/*
rid	id	resu	memo
1	1	2	不合格
2	2	2	不合格
3	3	2	不合格
5	5	2	不合格
6	6	2	不合格
7	7	2	不合格
*/

[二月十六]

试试这个：

;WITH cte
AS (SELECT *,
           id - ROW_NUMBER() OVER (ORDER BY memo) AS rn
    FROM #table
    WHERE resu = 2
   )
SELECT *
FROM cte
WHERE cte.rn IN (
                    SELECT rn FROM cte GROUP BY rn HAVING COUNT(1) >= 3
                );

[听雨停了]

;WITH cte AS (
          SELECT *,
                 id -ROW_NUMBER() OVER(PARTITION BY resu, memo ORDER BY id) AS 
                 rn
          FROM   #table
          WHERE  memo = '不合格'
      ),cte2 AS (
          SELECT rn
          FROM   cte
          GROUP BY
                 rn
          HAVING COUNT(1) >= 3
      )
 
 SELECT id,
        resu,
        memo
 FROM   cte              AS a
        INNER JOIN cte2  AS b
             ON  a.rn = b.rn


id          resu        memo
----------- ----------- --------------------
1           2           不合格
2           2           不合格
3           2           不合格
5           2           不合格
6           2           不合格
7           2           不合格

(6 行受影响)