是否可以(在有序表中)按顺序查找多行

Question

如果我有一个按ID排序的表，比如：

|---------------------|------------------|
|          ID         |        Key       |
|---------------------|------------------|
|          1          |        Foo       |
|---------------------|------------------|
|          2          |        Bar       |
|---------------------|------------------|
|          3          |        Test      |
|---------------------|------------------|
|          4          |        Test      |
|---------------------|------------------|

有没有一种方法可以检测与where子句顺序匹配的两行？

例如，在上表中，我想看看连续的两行中是否有一个Key为"test"。

这在SQL中可能吗？

Answer 1

另一个选项是缺口和孤岛的变体

示例

Declare @YourTable Table ([ID] int,[Key] varchar(50))
Insert Into @YourTable Values 
(1,'Foo')
,(2,'Bar')
,(3,'Test')
,(4,'Test')
Select ID_R1 = min(ID)
,ID_R2 = max(ID)
,[Key]
From  (
Select *
,Grp = ID-Row_Number() over(Partition By [Key] Order by ID) 
From @YourTable
) A 
Group By [Key],Grp
Having count(*)>1

退货

ID_R1   ID_R2   Key
3       4       Test

编辑-以防ID不是连续的

Select ID_R1 = min(ID)
,ID_R2 = max(ID)
,[Key]
From  (
Select *
,Grp = Row_Number() over(Order by ID)
-Row_Number() over(Partition By [Key] Order by ID) 
From @YourTable
) A 
Group By [key],Grp
Having count(*)>1

Answer 2

您可以尝试使用ROW_NUMBER窗口函数检查间隙。

SELECT [Key]
FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY ID) -
ROW_NUMBER() OVER(PARTITION BY [Key] ORDER BY ID) grp
FROM T 
)t1
GROUP BY [Key]
HAVING COUNT(grp) = 2

Answer 3

您可以作为进行自联接

CREATE TABLE T(
ID INT,
[Key] VARCHAR(45)
);

INSERT INTO T VALUES
(1, 'Foo'),
(2, 'Bar'),
(3, 'Test'),
(4, 'Test');
SELECT MIN(T1.ID) One,
MAX(T2.ID) Two,
T1.[Key] OnKey
FROM T T1 JOIN T T2
ON T1.[Key] = T2.[Key]
AND
T1.ID <> T2.ID
GROUP BY T1.[Key];

或CROSS JOIN作为

SELECT MIN(T1.ID) One,
MAX(T2.ID) Two,
T1.[Key] OnKey
FROM T T1 CROSS JOIN T T2
WHERE T1.[Key] = T2.[Key]
AND
T1.ID <> T2.ID
GROUP BY T1.[Key]

演示

Answer 4

您可以使用LEAD()窗口函数，如：

with 
x as (
select
id, [key], 
lead(id) over(order by id) as next_id,
lead([key]) over(order by id) as next_key
from my_table
)
select id, next_id from x where [key] = 'test' and next_key = 'test'