如何修改SQL实现两月数据对比，展示当期与上期不良率

可以通过条件聚合的方式，将两个时间段的数据统计到同一行中，实现当期与上期指标的并排展示。修改后的SQL如下：

SELECT 
    itemcode,
    itemname,
    -- 当期（2022-07-01 至 2022-07-14）核心指标
    SUM(CASE WHEN date BETWEEN '2022-07-01' AND '2022-07-14' THEN good + bad ELSE 0 END) AS present_prdn,
    SUM(CASE WHEN date BETWEEN '2022-07-01' AND '2022-07-14' THEN good ELSE 0 END) AS present_good,
    SUM(CASE WHEN date BETWEEN '2022-07-01' AND '2022-07-14' THEN bad ELSE 0 END) AS present_bad,
    CONCAT(ROUND(
        CASE WHEN SUM(CASE WHEN date BETWEEN '2022-07-01' AND '2022-07-14' THEN good + bad ELSE 0 END) = 0 THEN 0 
             ELSE (SUM(CASE WHEN date BETWEEN '2022-07-01' AND '2022-07-14' THEN bad ELSE 0 END) / SUM(CASE WHEN date BETWEEN '2022-07-01' AND '2022-07-14' THEN good + bad ELSE 0 END)) * 100 
        END, 0), '%') AS PresentRejection,
    -- 上期（2022-06-01 至 2022-06-30）核心指标
    SUM(CASE WHEN date BETWEEN '2022-06-01' AND '2022-06-30' THEN good + bad ELSE 0 END) AS previous_prdn,
    SUM(CASE WHEN date BETWEEN '2022-06-01' AND '2022-06-30' THEN good ELSE 0 END) AS previous_good,
    SUM(CASE WHEN date BETWEEN '2022-06-01' AND '2022-06-30' THEN bad ELSE 0 END) AS previous_bad,
    CONCAT(ROUND(
        CASE WHEN SUM(CASE WHEN date BETWEEN '2022-06-01' AND '2022-06-30' THEN good + bad ELSE 0 END) = 0 THEN 0 
             ELSE (SUM(CASE WHEN date BETWEEN '2022-06-01' AND '2022-06-30' THEN bad ELSE 0 END) / SUM(CASE WHEN date BETWEEN '2022-06-01' AND '2022-06-30' THEN good + bad ELSE 0 END)) * 100 
        END, 0), '%') AS PreviousRejection
FROM tb_data
WHERE date BETWEEN '2022-06-01' AND '2022-07-14' -- 限定仅查询目标时间段的数据，提升效率
GROUP BY itemcode, itemname -- 补充itemname到分组，避免SQL语法报错
ORDER BY present_good DESC;

关键修改说明 #

• 条件聚合逻辑：通过SUM(CASE WHEN ...)语法，针对两个时间段分别统计生产总数、良品数、不良品数，将不同时段的数据聚合到同一行，实现并排展示。
• 除零错误规避：计算不良率时加入判断逻辑，若某时段无生产数据（总生产数为0），直接返回0，避免出现除以0的报错。
• 查询范围优化：WHERE子句限定日期范围为6月1日至7月14日，仅加载需要的数据，减少不必要的计算开销。
• 分组语法修正：原SQL仅按itemcode分组，但SELECT中包含itemname，在严格SQL模式下会触发语法错误，因此补充itemname到分组字段中。

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