如何比较SQL中两个连续的行值

Question

我在下面的一个表中有与Employees Branch更改日志相关的数据。

UniqueID  UID  Branch_Id Created_On
18791  173  31  2-Jul-2020  
18411  173  31  15-May-2020 
17867  173  31  23-Mar-2020 
14614  173  27  25-Jul-2019 
12958  173  27  11-May-2019 
11783  173  27  7-Mar-2019  
11780  173  27  7-Mar-2019  
9719   173  27  14-Nov-2018 
9304   173  27  18-Oct-2018 
9103   173  27  9-Oct-2018  
7958   173  27  17-Sep-2018 
4549   173  27  15-Mar-2018 
3272   173  27  9-Jan-2018  
2844   173  20  6-Dec-2017  
2481   173  20  8-Nov-2017  
58     173  27  4-Jul-2013

我需要将上述数据显示如下:

UniqueId  UID    Branch_Id  Created_On     
17867   173     31        23-Mar-2020      
3272    173     27        9-Jan-2018        
2481    173     20        8-Nov-2017       
58      173     27        4-Jul-2013  

What I have try

SELECT *
FROM LogTable
INNER JOIN (
SELECT Min(UniqueId) as MinUniqueId
FROM LogTable
GROUP BY Branch_Id
) AS Filtered ON LogTable.UniqueId = Filtered.MinUniqueId

请指导我如何做到这一点?

谢谢。

Answer 1

标记每个Branch_Id更改并选择标记项

SELECT UniqueId, UID, Branch_Id, Created_On
FROM (
SELECT *,
case lag(branch_id) over(partition by uid order by created_on) when branch_id then 0 else 1 end flag
FROM LogTable  
) t
WHERE flag = 1
ORDER BY Created_On DESC;

Answer 2

我建议如下查询:

WITH
a AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY Created_on)-
ROW_NUMBER() OVER(PARTITION BY Branch_Id ORDER BY Created_on) as grp
FROM LogTable
),
b AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY Branch_Id, grp ORDER BY Created_on) as rn
FROM a
)
SELECT UniqueId, UID, Branch_Id, Created_On
FROM b
WHERE rn = 1;

结果:

+----------+-----+-----------+------------+
| UniqueId | UID | Branch_Id | Created_On |
+----------+-----+-----------+------------+
|     2481 | 173 |        20 | 2017-11-08 |
|       58 | 173 |        27 | 2013-07-04 |
|     3272 | 173 |        27 | 2018-01-09 |
|    17867 | 173 |        31 | 2020-03-23 |
+----------+-----+-----------+------------+

,db&lt的在小提琴

更简洁的版本:

WITH
a AS (
SELECT *,
LAG(Branch_Id) OVER(ORDER BY Created_on) Prev_Branch_Id
FROM LogTable
)
SELECT UniqueID, UID, Branch_Id, Created_On
FROM a
WHERE
Prev_Branch_Id IS NULL OR
Branch_ID != Prev_Branch_Id;

,db&lt的在小提琴