如何使用tSQL实现按Case、CaseFlow分组匹配关联Note的查询

实现思路 #

1. 由于Case是SQL Server保留关键字，查询时需要用[]转义
2. 先关联Case表和CaseFlow表拿到全量案件的流程节点，再左连接Note表，匹配规则为同CaseID且CreatedOn落在StartDate和EndDate的日期范围内
3. 用窗口函数判断同一案件、同一流程的首行，非首行对应字段置空，实现层级折叠的展示效果

完整T-SQL查询语句 #

WITH JoinData AS (
    -- 关联三张表，完成Note归属匹配
    SELECT
        c.CaseID,
        cf.CaseFlowID,
        n.NoteID,
        -- 标记同一Case下的第一行
        ROW_NUMBER() OVER(PARTITION BY c.CaseID ORDER BY cf.StartDate, n.NoteID) AS CaseRn,
        -- 标记同一CaseFlow下的第一行
        ROW_NUMBER() OVER(PARTITION BY cf.CaseFlowID ORDER BY n.NoteID) AS FlowRn
    FROM [Case] c
    INNER JOIN CaseFlow cf ON c.CaseID = cf.CaseID
    LEFT JOIN Note n ON c.CaseID = n.CaseID 
        AND n.CreatedOn BETWEEN cf.StartDate AND cf.EndDate
)
SELECT
    -- 仅同一Case的首行显示CaseID
    CASE WHEN CaseRn = 1 THEN CaseID ELSE '' END AS [Case],
    -- 仅同一CaseFlow的首行显示CaseFlowID
    CASE WHEN FlowRn = 1 THEN CaseFlowID ELSE '' END AS [Flows],
    NoteID AS [Notes]
FROM JoinData
ORDER BY CaseID, CaseFlowID, NoteID

场景适配调整 #

• 如果日期字段带时间精度，可将匹配条件调整为n.CreatedOn >= cf.StartDate AND n.CreatedOn < DATEADD(DAY, 1, cf.EndDate)，避免时间部分导致同一天的记录匹配失败
• 如果需要把同一流程的所有Note合并为一行展示，可使用STRING_AGG(n.NoteID, ', ')配合GROUP BY c.CaseID, cf.CaseFlowID实现

内容的提问来源于stack exchange，提问作者Pey