如何将SQL查询结果转为报告格式并存储至Reps表？

我有一个查询返回repid、Car、Model、Fault和cnt（出现次数统计）列，当前查询语句和输出如下：

当前查询语句 #

SELECT repid, Car, Model, Fault, COUNT(*) AS cnt
FROM Cars
GROUP BY repid, Car, Model, Fault
ORDER BY repid, Car, Model, COUNT(*) DESC;

当前输出 #

repid | Car  | Model | Fault | cnt
-----------------------------------------------
1     | Car1 | Model1 | Text1         | 5
1     | Car1 | Model1 | Text2         | 4
1     | Car1 | Model1 | Text3         | 3
1     | Car1 | Model2 | Text4         | 2
1     | Car1 | Model2 | Text5         | 1
2     | Car2 | Model1 | Text6         | 6
2     | Car2 | Model1 | Text7         | 4

我希望输出更具报告风格，repid、Car和Model每个分组仅显示一次，其余行仅展示Fault和cnt，并将结果存储到Reps表的RepsText字段中。期望输出示例：

期望输出 #

Report: RepID 1
-----------------
Car1
    Model1
        Text1 repeated 5 times
        Text2 repeated 4 times
        Text3 repeated 3 times

    Model2
        Text4 repeated 2 times
        Text5 repeated 1 times
Report: RepID 2
-----------------
Car2
    Model1
        Text6 repeated 6 times
        Text7 repeated 4 times

样本数据 #

Reps表 #

Cars表 #

已尝试方案 #

我尝试用ROW_NUMBER()和CASE表达式隐藏重复值，但效果不理想，代码如下：

WITH RankedData AS (
    SELECT 
        repid,
        Car,
        Model,
        Fault,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY repid, Car, Model ORDER BY COUNT(*) DESC) AS row_num
    FROM Cars
    GROUP BY repid, Car, Model, Fault
)
SELECT 
    CASE WHEN row_num = 1 THEN CAST(repid AS VARCHAR) ELSE '' END AS RepID,
    CASE WHEN row_num = 1 THEN Car ELSE '' END AS Car,
    CASE WHEN row_num = 1 THEN Model ELSE '' END AS Model,
    Fault AS Fault,
    cnt AS Count
FROM RankedData
ORDER BY repid, Car, Model, cnt DESC;

要生成这种层级化的报告文本并写入Reps表，核心是通过多层字符串聚合实现分组文本拼接，以下是主流数据库的实现方式：

1. SQL Server 实现 #

利用STRING_AGG函数进行多层聚合，先聚合故障行，再聚合模型组，最后聚合车辆组，形成完整报告：

WITH FaultLevel AS (
    -- 按故障分组，生成单条故障描述
    SELECT 
        repid,
        Car,
        Model,
        CONCAT('        ', Fault, ' repeated ', COUNT(*), ' times') AS fault_line
    FROM Cars
    GROUP BY repid, Car, Model, Fault
    ORDER BY COUNT(*) DESC
),
ModelLevel AS (
    -- 按模型分组，聚合故障行，加上模型标题
    SELECT 
        repid,
        Car,
        CONCAT('    ', Model, CHAR(13), CHAR(10), STRING_AGG(fault_line, CHAR(13), CHAR(10))) AS model_block
    FROM FaultLevel
    GROUP BY repid, Car, Model
),
CarLevel AS (
    -- 按车辆分组，聚合模型块，加上车辆标题
    SELECT 
        repid,
        CONCAT(Car, CHAR(13), CHAR(10), STRING_AGG(model_block, CHAR(13), CHAR(10))) AS car_block
    FROM ModelLevel
    GROUP BY repid, Car
),
ReportLevel AS (
    -- 生成完整报告文本
    SELECT 
        repid,
        CONCAT('Report: RepID ', repid, CHAR(13), CHAR(10), '-----------------', CHAR(13), CHAR(10), STRING_AGG(car_block, CHAR(13), CHAR(10))) AS full_report
    FROM CarLevel
    GROUP BY repid
)
-- 更新Reps表的RepsText字段
UPDATE r
SET r.RepsText = rpt.full_report
FROM Reps r
JOIN ReportLevel rpt ON r.repid = rpt.repid;

说明：CHAR(13)+CHAR(10)是Windows换行符，若使用Linux/macOS环境可替换为CHAR(10)

2. PostgreSQL 实现 #

PostgreSQL用STRING_AGG和E'\n'表示换行：

WITH FaultLevel AS (
    SELECT 
        repid,
        Car,
        Model,
        CONCAT('        ', Fault, ' repeated ', COUNT(*), ' times') AS fault_line
    FROM Cars
    GROUP BY repid, Car, Model, Fault
    ORDER BY COUNT(*) DESC
),
ModelLevel AS (
    SELECT 
        repid,
        Car,
        CONCAT('    ', Model, E'\n', STRING_AGG(fault_line, E'\n')) AS model_block
    FROM FaultLevel
    GROUP BY repid, Car, Model
),
CarLevel AS (
    SELECT 
        repid,
        CONCAT(Car, E'\n', STRING_AGG(model_block, E'\n')) AS car_block
    FROM ModelLevel
    GROUP BY repid, Car
),
ReportLevel AS (
    SELECT 
        repid,
        CONCAT('Report: RepID ', repid, E'\n', '-----------------', E'\n', STRING_AGG(car_block, E'\n')) AS full_report
    FROM CarLevel
    GROUP BY repid
)
UPDATE Reps r
SET RepsText = rpt.full_report
FROM ReportLevel rpt
WHERE r.repid = rpt.repid;

3. MySQL 实现 #

MySQL用GROUP_CONCAT和'\n'，需先设置group_concat_max_len避免文本截断：

SET group_concat_max_len = 1000000;

WITH FaultLevel AS (
    SELECT 
        repid,
        Car,
        Model,
        CONCAT('        ', Fault, ' repeated ', COUNT(*), ' times') AS fault_line
    FROM Cars
    GROUP BY repid, Car, Model, Fault
    ORDER BY COUNT(*) DESC
),
ModelLevel AS (
    SELECT 
        repid,
        Car,
        CONCAT('    ', Model, '\n', GROUP_CONCAT(fault_line SEPARATOR '\n')) AS model_block
    FROM FaultLevel
    GROUP BY repid, Car, Model
),
CarLevel AS (
    SELECT 
        repid,
        CONCAT(Car, '\n', GROUP_CONCAT(model_block SEPARATOR '\n')) AS car_block
    FROM ModelLevel
    GROUP BY repid, Car
),
ReportLevel AS (
    SELECT 
        repid,
        CONCAT('Report: RepID ', repid, '\n', '-----------------', '\n', GROUP_CONCAT(car_block SEPARATOR '\n')) AS full_report
    FROM CarLevel
    GROUP BY repid
)
UPDATE Reps r
JOIN ReportLevel rpt ON r.repid = rpt.repid
SET r.RepsText = rpt.full_report;

效果验证 #

执行上述语句后，Reps表中repid=1的RepsText字段内容将变为：

Report: RepID 1
-----------------
Car1
    Model1
        Text1 repeated 5 times
        Text2 repeated 4 times
        Text3 repeated 3 times
    Model2
        Text4 repeated 2 times
        Text5 repeated 1 times

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