SQL查询同Sub_JS_Number、Month下Defect_type不同且缺陷重和超Issued_weight的行

基础表结构说明 #

存储生产缺陷明细的数据表共包含5个字段：

• Sub_JS_Number：生产子批次编号
• Issued_weight：批次投用重量
• Defect_type：缺陷类型
• Defect_weight：单条记录对应缺陷重量
• Month：统计月份

查询规则 #

需筛选同时满足两个条件的原始明细记录：

1. 按Sub_JS_Number、Month分组后，组内存在至少2种不同的Defect_type取值
2. 同组内所有记录的Defect_weight求和结果，大于该组对应的Issued_weight值（同组内Issued_weight为批次固定属性，取值完全一致）

样例数据中符合要求的仅为Sub_JS_Number='2122032/2-001A'、Month='January'的2条记录：该组缺陷重量总和为4.50+2.92=7.42，大于投用重量4.52；另一组满足多缺陷类型条件的Sub_JS_Number='2121830/1-001A'、Month='January'分组，缺陷重量总和为7.564，小于投用重量7.58，不符合筛选要求。

全条件满足版SQL实现 #

以下写法兼容MySQL8.0、PostgreSQL、SQL Server等所有支持窗口函数的主流数据库，执行效率高于传统聚合后关联的写法：

SELECT 
    Sub_JS_Number,
    Issued_weight,
    Defect_type,
    Defect_weight,
    Month
FROM (
    SELECT 
        *,
        -- 统计同组内不同缺陷类型的数量
        COUNT(DISTINCT Defect_type) OVER (PARTITION BY Sub_JS_Number, Month) AS defect_type_cnt,
        -- 统计同组内缺陷总重量
        SUM(Defect_weight) OVER (PARTITION BY Sub_JS_Number, Month) AS total_defect_weight
    FROM production_defect -- 替换为实际表名
) t
WHERE 
    defect_type_cnt > 1 -- 匹配条件①：组内存在多种缺陷类型
    AND total_defect_weight > Issued_weight; -- 匹配条件②：缺陷总重量大于批次投用重量

老版本数据库兼容写法 #

针对不支持窗口函数的环境（如MySQL5.x），可改用聚合后关联原表的逻辑实现：

SELECT d.*
FROM production_defect d
JOIN (
    SELECT 
        Sub_JS_Number,
        Month,
        COUNT(DISTINCT Defect_type) AS defect_type_cnt,
        SUM(Defect_weight) AS total_defect_weight,
        MAX(Issued_weight) AS batch_issued_weight
    FROM production_defect
    GROUP BY Sub_JS_Number, Month
) agg ON d.Sub_JS_Number = agg.Sub_JS_Number AND d.Month = agg.Month
WHERE 
    agg.defect_type_cnt > 1
    AND agg.total_defect_weight > agg.batch_issued_weight;

简化版SQL（仅匹配多缺陷类型条件） #

如果不需要做重量和校验，仅筛选组内存在多种缺陷类型的记录，可直接使用简化写法：

SELECT 
    Sub_JS_Number,
    Issued_weight,
    Defect_type,
    Defect_weight,
    Month
FROM (
    SELECT 
        *,
        COUNT(DISTINCT Defect_type) OVER (PARTITION BY Sub_JS_Number, Month) AS defect_type_cnt
    FROM production_defect
) t
WHERE defect_type_cnt > 1;

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