当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - How to find consecutive data in SQL

问题描述:

I have the following data in a SQL Table:



I want to find 3 Consecutive data by No and group with ID. Result are


How to write query.please help.

网友答案:

Here is query which select only rows where actually 3 consecutive rows are:

SELECT a.*
FROM
TABLE as a
inner join
TABLE as b on (a.no+1=b.no and a.id=b.id)
inner join
TABLE as c on (a.no+2=c.no and a.id=c.id)
order by a.id, a.no

for your data it will provide:

4   a1  4
5   a1  3
1   a2  2
2   a2  4
3   a3  2
4   a3  3

rows (6,a1,1), (3,a2,5) and (5,a3,4) are not selected, as there are no (8,a1) (5,a2) and (7,a3)

网友答案:
DECLARE @temp TABLE (NO int,ID VARCHAR(2),QTY int)
INSERT INTO @temp
SELECT 1,'A1',5 UNION ALL
SELECT 4,'A1',4 UNION ALL
SELECT 5,'A1',3 UNION ALL
SELECT 6,'A1',1 UNION ALL
SELECT 7,'A1',0 UNION ALL
SELECT 9,'A1',5 UNION ALL
SELECT 12,'A1',3 UNION ALL
SELECT 1,'A2',2 UNION ALL
SELECT 2,'A2',4 UNION ALL
SELECT 3,'A2',5 UNION ALL
SELECT 4,'A2',1 UNION ALL
SELECT 7,'A2',4 UNION ALL
SELECT 9,'A2',5 UNION ALL
SELECT 1,'A3',0 UNION ALL
SELECT 3,'A3',2 UNION ALL
SELECT 4,'A3',3 UNION ALL
SELECT 5,'A3',4 UNION ALL
SELECT 6,'A3',2;

WITH tmpa AS
(
    SELECT *
         , NO - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS grp
    FROM @temp
)
, tmpb AS
(
    SELECT *
         , COUNT(*) OVER(PARTITION BY ID,grp) AS grpcount
    FROM tmpa
)
SELECT NO,ID,QTY FROM tmpb WHERE grpcount>1;

Result are

4   A1  4
5   A1  3
6   A1  1
7   A1  0
1   A2  2
2   A2  4
3   A2  5
4   A2  1
3   A3  2
4   A3  3
5   A3  4
6   A3  2

I found this query from this link. Find n consecutive free numbers from table

http://sqlfiddle.com/#!1/a2633/2

Answer Credit by

分享给朋友:
您可能感兴趣的文章:
随机阅读: