多关联只读表中按分组生成行号的SQL查询问题

场景1：仅查询status类型的变更记录并生成行号 #

以下SQL会过滤出所有field为status的变更记录，同时生成符合要求的分组编号和行号：

SELECT
    DENSE_RANK() OVER (ORDER BY issue.id) AS cg_row,
    ROW_NUMBER() OVER (PARTITION BY issue.id ORDER BY changegroup.id, changeitem.id) AS ci_row,
    issue.id AS iss_issueid,
    changeitem.field
FROM
    issue
JOIN changegroup ON issue.id = changegroup.issueid
JOIN changeitem ON changegroup.id = changeitem.groupid
WHERE
    changeitem.field = 'status'
ORDER BY
    issue.id, changegroup.id, changeitem.id;

关键函数说明 #

• DENSE_RANK() OVER (ORDER BY issue.id)：按工单ID全局排序生成cg_row，同一工单的所有记录对应相同的编号，编号连续无间隔。若不需要连续编号，可替换为ROW_NUMBER()。
• ROW_NUMBER() OVER (PARTITION BY issue.id ORDER BY changegroup.id, changeitem.id)：按工单ID分区，在每个分区内按变更组ID、变更记录ID的顺序生成递增的ci_row，确保同一工单内的status变更记录按先后顺序得到1、2、3...的行号。

场景2：保留所有变更记录，仅对status类型生成行号 #

如果需要展示所有类型的变更记录，仅对field为status的记录生成ci_row，其他记录的ci_row设为NULL，可使用以下SQL：

SELECT
    DENSE_RANK() OVER (ORDER BY issue.id) AS cg_row,
    CASE WHEN changeitem.field = 'status' 
         THEN ROW_NUMBER() OVER (PARTITION BY issue.id, changeitem.field ORDER BY changegroup.id, changeitem.id)
         ELSE NULL END AS ci_row,
    issue.id AS iss_issueid,
    changeitem.field
FROM
    issue
JOIN changegroup ON issue.id = changegroup.issueid
JOIN changeitem ON changegroup.id = changeitem.groupid
ORDER BY
    issue.id, changegroup.id, changeitem.id;

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