获取下一个并扫描同一表中的记录

Nawaf 发表于 Dev

纳瓦夫

我有下表

数据可在此处访问

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=18e9473c82deb4e7e955a0b0cb6f5014

我想找到一个雇员，如果他有一个城市='London'，并且在该城市之后的下一行<>'London'。之后，我应该扫描其余行并捕获第一行='London'，否则我应该忽略整行。结果应该如下所示

我的代码和结果：

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=cc7863dbe59f80d1058f34a8f7ad0562

代码有效，如果

first city = London
Second city != London
third city = London

但是，如果说伦敦而不是第三城市，那么说第四或第五将是行不通的。代码应为：

first city = London 
Second city <> London
third city = London if not check next
fourth city = London if not check next
...
to last row for the employee if not find London, skip the entire row.

戈登·利诺夫

我认为LEAD()以某种CASE逻辑和IGNORE NULLs可以实现您想要的：

SELECT * 
FROM (SELECT t.*,
             LEAD(CITY) OVER (PARTITION BY EMP_ID ORDER BY DATETIME) as city_2,
             LEAD(datetime) OVER (PARTITION BY EMP_ID ORDER BY DATETIME) as datetime_2,
             LEAD(CASE WHEN CITY = 'London' THEN CITY END IGNORE NULLS) OVER (PARTITION BY EMP_ID ORDER BY DATETIME) as city_3,
             LEAD(CASE WHEN CITY = 'London' THEN DATETIME END IGNORE NULLS) OVER (PARTITION BY EMP_ID ORDER BY DATETIME) as datetime_3
      FROM MY_TABLE t
     ) t
WHERE CITY = 'London' AND CITY_2 <> 'London' AND CITY_3 = 'London';

这是db <>小提琴。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。