原表及结果表如下图所示
做法:
- 通过表自关联转换行列。
- 根据NAME,FROM_DATE,STATUS使用开窗函数取最小的TO_DATE
with T1 AS (
SELECT 'A' AS NAME,'2020-01-01' AS [DATE],'active' AS STATUS
UNION
SELECT 'A' AS NAME,'2020-02-01' AS [DATE],'sleep' AS STATUS
UNION
SELECT 'A' AS NAME,'2020-02-26' AS [DATE],'active' AS STATUS
UNION
SELECT 'B' AS NAME,'2020-12-30' AS [DATE],'active' AS STATUS
)
,T2 AS (
SELECT A.NAME
,A.DATE AS FROM_DATE
,COALESCE(B.DATE,'9999-12-31') AS TO_DATE
,A.STATUS
,row_number() OVER (PARTITION BY A.NAME,A.DATE,A.STATUS ORDER BY COALESCE(B.DATE,'9999-12-31') ASC) AS RN
FROM T1 A
LEFT JOIN T1 B ON A.NAME = B.NAME AND A.DATE < B.DATE)
SELECT NAME,FROM_DATE,TO_DATE,STATUS
FROM T2
WHERE RN = 1
也可以直接使用子查询,不要用CTE了
SELECT NAME,FROM_DATE,TO_DATE,STATUS
FROM (
SELECT A.NAME
,A.DATE AS FROM_DATE
,COALESCE(B.DATE,'9999-12-31') AS TO_DATE
,A.STATUS
,row_number() OVER (PARTITION BY A.NAME,A.DATE,A.STATUS ORDER BY COALESCE(B.DATE,'9999-12-31') ASC) AS RN
FROM T1 A
LEFT JOIN T1 B ON A.NAME = B.NAME AND A.DATE < B.DATE) T
WHERE RN = 1