SQL三表关联查询实现重复列仅首行显示的层级化数据输出

我有三张存在层级一对多关联的表，基础表结构如下：

Group             ID|Title
RiskFactor        GroupID|Question
Choices           RiskFactorID|Label|Score

表关联规则：一个Group对应多条RiskFactor记录，每条RiskFactor记录对应多条带分值的选项Choices。这种关联关系下，INNER JOIN和LEFT JOIN的查询返回结果完全一致。

现有查询语句：

SELECT        
       Groups.Title, 
       RiskFactors.Question,  
       Choices.Label, 
       Choices.Score
FROM            
       Groups 
INNER JOIN RiskFactors ON Groups.GroupId = RiskFactors.GroupId 
INNER JOIN Choices ON RiskFactors.RiskFactorId = Choices.RiskFactorId
ORDER BY Groups.groupid

该查询的现有输出存在字段重复问题，示例如下：

Title                       Question                                                   Label  Score
4.1 - Employment & Finance  Is your current job in JEOPARDY?  (Describe why in notes)   Yes 3
4.1 - Employment & Finance  Is your current job in JEOPARDY?  (Describe why in notes)   Maybe   2
4.1 - Employment & Finance  Is your current job in JEOPARDY?  (Describe why in notes)   NA  1
4.1 - Employment & Finance  Is your current job in JEOPARDY?  (Describe why in notes)   No  1
4.1 - Employment & Finance  How LONG was (is) your longest FULL TIME job?   NA  1
4.1 - Employment & Finance  How LONG was (is) your longest FULL TIME job?   0-6 Months  1
4.1 - Employment & Finance  How LONG was (is) your longest FULL TIME job?   1-3 Years   1
4.1 - Employment & Finance  How LONG was (is) your longest FULL TIME job?   6-12 Months 1
4.1 - Employment & Finance  How LONG was (is) your longest FULL TIME job?   Only Part Time  1
4.1 - Employment & Finance  How LONG was (is) your longest FULL TIME job?   More than 3 Years   1

期望的输出效果是把同组下重复的Title、Question字段值替换为NULL或空值，实现层级化展示，目标格式如下：

TITLE                       QUESTION                                                    Label SCORE
4.1 - Employment & Finance  Is your current job in JEOPARDY?  (Describe why in notes)   Yes 3
                                                                                        Maybe   2
                                                                                        NA  1
                                                                                        No  1
                            How LONG was (is) your longest FULL TIME job?   NA  1
                                                                            0-6 Months  1
                                                                            1-3 Years   1
                                                                            6-12 Months 1
                                                                            Only Part Time  1
                                                                            More than 3 Years   1

之前尝试过CTE、Pivot表的写法都没有实现预期效果，我不是专业SQL开发人员，知道这是通用的展示需求，之前都是靠前端Grid控件的列去重功能实现该效果，现在想知道如何在SQL层面直接输出符合要求的层级化数据。

这个场景不需要用到Pivot或者复杂的递归CTE，核心逻辑非常直白：只保留每个分组第一行的分组字段值，同组后续行的对应分组字段全部置空，用标准窗口函数就可以快速实现。
逻辑拆解：

• 先对查询结果按照业务层级做固定排序，保证同一个Group、同一个RiskFactor下的行是连续排列的
• 对Title字段：取当前行上一行的GroupId做对比，如果值相等，说明当前行不是当前Group的第一行，将Title置为NULL；如果值不相等，保留Title的原始值
• 对Question字段：取当前行上一行的RiskFactorId做对比，如果值相等，说明当前行不是当前问题的第一行，将Question置为NULL；如果值不相等，保留Question的原始值

可直接运行的兼容主流数据库的SQL写法：

WITH OrderedResult AS (
    SELECT        
           Groups.Title,
           Groups.GroupId,
           RiskFactors.Question,
           RiskFactors.RiskFactorId,
           Choices.Label,
           Choices.Score
    FROM            
           Groups 
    INNER JOIN RiskFactors 
        ON Groups.GroupId = RiskFactors.GroupId 
    INNER JOIN Choices 
        ON RiskFactors.RiskFactorId = Choices.RiskFactorId
)
SELECT
    CASE 
        WHEN GroupId = LAG(GroupId) OVER(ORDER BY GroupId, RiskFactorId, Score DESC) 
        THEN NULL 
        ELSE Title 
    END AS Title,
    CASE 
        WHEN RiskFactorId = LAG(RiskFactorId) OVER(ORDER BY GroupId, RiskFactorId, Score DESC) 
        THEN NULL 
        ELSE Question 
    END AS Question,
    Label,
    Score
FROM OrderedResult
ORDER BY GroupId, RiskFactorId, Score DESC

注意事项 #

• 窗口函数的排序规则必须和业务要求的展示顺序完全一致，否则会出现同一个Group的行被拆分、重复字段散落在不同位置的问题
• 如果使用的是不支持窗口函数的老旧数据库（比如MySQL 5.7及以下版本），可以用会话变量逐行记录上一行的分组ID做判断，核心逻辑和上述写法完全一致，只是语法写法略有不同
• 这种在SQL层输出空值的方式更适合固定格式的报表打印、纯文本导出场景；如果是前端交互式页面，更推荐返回原始关联结果，由前端Grid组件做单元格合并，调整样式的灵活性更高

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