请求编写SQL语句实现基于Snap_Id的Result字段计算需求

开发者阿泽

2026-6-16

需求：编写SQL实现指定业务逻辑

业务逻辑规则：

若某行的Snap_Id不为null，则其前一行的Result字段值为Yes
其余行的Result字段值为No

原始数据表格

Id	CaseId	User	CaseStatus	DateCreated	Snap_Id	row_num
11	100	Raj	3	2023-04-27	null	1
22	100	Mike	3	2023-06-22	null	2
33	100	RJ	5	2023-06-30	33	3
44	100	Mike	3	2023-07-04	null	4
55	100	RJ	5	2023-07-04	55	5

期望输出表格

Id	CaseId	User	CaseStatus	DateCreated	Snap_Id	row_num	Result
11	100	Raj	3	2023-04-27	null	1	No
22	100	Mike	3	2023-06-22	null	2	Yes
33	100	RJ	5	2023-06-30	33	3	No
44	100	Mike	3	2023-07-04	null	4	Yes
55	100	RJ	5	2023-07-04	55	5	No

SQL解决方案

利用LEAD()窗口函数获取下一行的Snap_Id值，通过判断该值是否非空来确定当前行的Result：

SELECT 
    Id,
    CaseId,
    User,
    CaseStatus,
    DateCreated,
    Snap_Id,
    row_num,
    CASE 
        WHEN LEAD(Snap_Id) OVER (PARTITION BY CaseId ORDER BY row_num) IS NOT NULL 
        THEN 'Yes' 
        ELSE 'No' 
    END AS Result
FROM 
    your_table_name;