選擇“A1”後跟“A2”的行

我有一組條目，例如：

Id ColA   

1 A1   
2 A2   
3 B   
4 A2   
5 A1   
6 C   
7 A1   
8 A1   
9 A2   
10 D

在第 1、2 和 8、9 對中，第一行在 colA 中有 A1，下一行在 colA 中有 A2。如何選擇這兩對？

由於您沒有提及任何特定的 DBMS，因此我首先假設沒有視窗函式：

select A.Id, A.ColA, B.Id, B.ColA
from T as A
join T as B
   on A.Id + 1 = B.Id
where A.ColA = 'A1'
 and B.ColA = 'A2'

假設視窗函式確實存在：

select Id, ColA, Lead_id, LeadColA
from (
   select Id, ColA
        , Lead(id) over ( order by Id ) as Lead_Id
        , Lead(ColA) over ( order by Id ) as Lead_ColA
   from T
)
where Id + 1 = Lead_id
 and ColA = 'A1'
 and Lead_ColA = 'A2'

所有未經測試，但你應該明白

@Vérace 建議的更表格格式的替代方法是使用橫向交叉連接來轉置結果。IE：

select X.Id, X.ColA
from T as A
join T as B
   on A.Id + 1 = B.Id 
cross join lateral ( values (A.Id, A.ColA)
                         , (B.Id, B.ColA) 
                  ) X (id, ColA)
where A.ColA = 'A1'
 and B.ColA = 'A2'

對於 SQL server，我相信cross apply可以以同樣的方式使用。DB2、Oracle、Postgres 都實現了LATERAL，MySQL 沒有。同樣的技術也可以應用於視窗函式：

select x.Id, x.ColA
from (
   select Id, ColA
        , Lead(id) over ( order by Id ) as Lead_Id
        , Lead(ColA) over ( order by Id ) as Lead_ColA
   from T
) as Y cross join lateral ( 
              values (y.id, y.cola)
                   , (y.lead_id, y.lead_cola) 
) as X (id, cola)
where y.Id + 1 = y.Lead_id
 and y.ColA = 'A1'
 and y.Lead_ColA = 'A2'

Lennart 的第一個查詢產生這樣的結果（即並排）：

id|cola|id|cola
1|A1|2|A2
8|A1|9|A2

如果您想要如下所示的結果（即採用更“表格”的格式），哪一種可能更適合您？

id|cola
1|A1
2|A2
8|A1
9|A2

然後使用這個查詢：

SELECT
t1.id, t1.cola
FROM tab1 t1
JOIN tab1 t2 ON t1.id + 1 = t2.id
WHERE t1.cola = 'A1' AND t2.cola = 'A2'
UNION
SELECT
t1.id, t1.cola
FROM tab1 t1
JOIN tab1 t2 ON t1.id - 1 = t2.id
WHERE t1.cola = 'A2' AND t2.cola = 'A1'

有點笨拙，但這可能是您想要的結果？

等效的 LEAD() windows 函式查詢將是（儘管這非常笨拙！-也許那裡的一些天才可以提出更好的解決方案？

select xx.id,xx.cola
from (
   select id, cola, Lead(id) over ( order by id ) as lead_id, Lead(cola) over ( order by Id ) as lead_cola
   from tab1
) AS xx
where Id + 1 = Lead_id
 and cola = 'A1'
 and lead_cola = 'A2'
UNION
select yy.id, yy.cola
from (
   select id, cola, lag(id) over ( order by id ) as lead_id, lag(cola) over ( order by Id ) as lead_cola
   from tab1
   -- ORDER BY id DESC
) AS yy
where Id - 1 = Lead_id
 and cola = 'A2'
 and lead_cola = 'A1'

引用自：https://dba.stackexchange.com/questions/145018