如果ID在n行中是连续的，如何标记一组重复项? [英] How to tag a group of repeating items if the ids are consecutive for n rows?

问题描述

在测试中为连续ID进行构建非常困难，除非将其分解成多个部分或使用我想避免的游标.

Building in the test for consecutive ids is proving difficult without breaking it down into parts or using a cursor which I'd like to avoid.

伪查询-

SELECT all 
FROM table with the same description on multiple adjacent rows for >= 4 rows 
and set tag = 'y' and order by id 

(id,description, tag),
(1, 'xxx', 'n'),
(2, 'xxx', 'n'),
(3, 'xxx', 'n'),
(7, 'xxx', 'n'),
(5, 'xxx', 'n'),
(8, 'xxx', 'n'), 
(4, 'xxx', 'n'), 
(6, 'zzz', 'n') 

所需结果

(1, 'xxx', 'y') 
(2, 'xxx', 'y') 
(3, 'xxx', 'y') 
(4, 'xxx', 'y') 
(5, 'xxx', 'y') 

推荐答案

这被称为缺口和孤岛问题.这样的事情应该起作用

This is called as gaps and island problem. Something like this should work

;with cte as
(SELECT id, 
       description, 
       tag = 'y' ,
       cnt = Count(*)over(partition by description, grp)
FROM  (SELECT *, 
              grp = Sum(CASE WHEN prev_description = description THEN 0 ELSE 1 END)Over(Order by id)
       FROM   (SELECT *, 
                      prev_description = Lag(description) OVER(ORDER BY id) 
               FROM   Yourtable) a) b 
GROUP  BY id, description, grp 
)
Select * from cte 
Where cnt >= 4

使用Row_Number

;with cte as
(SELECT id, 
       description, 
       tag = 'y' ,
       cnt = Count(*)over(partition by description, grp)
FROM  (select Grp = row_number()over(order by id) - 
             row_number()over(partition by description order by id), *
       from Yourtable) b 
GROUP  BY id, description, grp)
Select * from cte 
Where cnt >= 4

这篇关于如果ID在n行中是连续的，如何标记一组重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！