You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何为含零值缺陷数据的SQL查询添加GROUP BY子句获目标结果

avatar
AI 小北
2026-6-27
问题：分组Unpivot结果并保留无缺陷记录

我司采购的第三方系统提供的表包含20个DEFECTID字段及对应的20个DEFECTCNT字段，已通过Unpivot查询得到基础结果集（代码如下）：

WITH Unpivoted AS
(
    SELECT
        OC_DDATA_PC.PARTNO,
        CAST(OC_DDAT_AUX_PC.UDL40 AS datetime) AS SAMPLEDATE,
        stagingPLM.DBO.PLANTS.PLANT_NAME,
        OC_DDATA_PC.UDL2 as SHIFT,
        OC_DDATA_PC.UDL4 as SIGNON_ID,
        OC_DDATA_PC.UDL6 as LINE_NUMBER,
        OC_DDAT_AUX_PC.UDL12 as CAVITY,
        OC_DDAT_AUX_PC.UDL27 as SPEC_TYPE,
        OC_DDAT_AUX_PC.UDL28 as PROPERTY_TREE,
        OC_DDAT_AUX_PC.UDL30 as DEFECT_TYPE,
        OC_DDATA_PC.SSIZE,
        ColName,
        ColID,
        ColValue
    FROM            
        OC_DDATA_PC 
    INNER JOIN
        OC_DDAT_AUX_PC ON OC_DDATA_PC.PARTNO = OC_DDAT_AUX_PC.PARTNOAUX AND OC_DDATA_PC.DATETIME = OC_DDAT_AUX_PC.DATETIMEAUX 
    INNER JOIN
        stagingPLM.dbo.PLANTS ON OC_DDATA_PC.UDL1 = stagingPLM.dbo.PLANTS.PLANT_CODE
    CROSS APPLY (
        VALUES
        ('DEFECTID1',OC_DDATA_PC.DEFECTID1,OC_DDATA_PC.DEFECTCNT1),
        ('DEFECTID2',OC_DDATA_PC.DEFECTID2,OC_DDATA_PC.DEFECTCNT2),
        ('DEFECTID3',OC_DDATA_PC.DEFECTID3,OC_DDATA_PC.DEFECTCNT3),
        ('DEFECTID4',OC_DDATA_PC.DEFECTID4,OC_DDATA_PC.DEFECTCNT4),
        ('DEFECTID5',OC_DDATA_PC.DEFECTID5,OC_DDATA_PC.DEFECTCNT5),
        ('DEFECTID6',OC_DDATA_PC.DEFECTID6,OC_DDATA_PC.DEFECTCNT6),
        ('DEFECTID7',OC_DDATA_PC.DEFECTID7,OC_DDATA_PC.DEFECTCNT7),
        ('DEFECTID8',OC_DDATA_PC.DEFECTID8,OC_DDATA_PC.DEFECTCNT8),
        ('DEFECTID9',OC_DDATA_PC.DEFECTID9,OC_DDATA_PC.DEFECTCNT9),
        ('DEFECTID10',OC_DDATA_PC.DEFECTID10,OC_DDATA_PC.DEFECTCNT10),
        ('DEFECTID11',OC_DDATA_PC.DEFECTID11,OC_DDATA_PC.DEFECTCNT11),
        ('DEFECTID12',OC_DDATA_PC.DEFECTID12,OC_DDATA_PC.DEFECTCNT12),
        ('DEFECTID13',OC_DDATA_PC.DEFECTID13,OC_DDATA_PC.DEFECTCNT13),
        ('DEFECTID14',OC_DDATA_PC.DEFECTID14,OC_DDATA_PC.DEFECTCNT14),
        ('DEFECTID15',OC_DDATA_PC.DEFECTID15,OC_DDATA_PC.DEFECTCNT15),
        ('DEFECTID16',OC_DDATA_PC.DEFECTID16,OC_DDATA_PC.DEFECTCNT16),
        ('DEFECTID17',OC_DDATA_PC.DEFECTID17,OC_DDATA_PC.DEFECTCNT17),
        ('DEFECTID18',OC_DDATA_PC.DEFECTID18,OC_DDATA_PC.DEFECTCNT18),
        ('DEFECTID19',OC_DDATA_PC.DEFECTID19,OC_DDATA_PC.DEFECTCNT19),
        ('DEFECTID20',OC_DDATA_PC.DEFECTID20,OC_DDATA_PC.DEFECTCNT20)

    ) AS Unpvt(ColName, ColID, ColValue)

    WHERE
        OC_DDAT_AUX_PC.UDL28 LIKE 'PULP %'
)
SELECT *
FROM Unpivoted

现在需要进一步优化：按SAMPLEDATE、PARTNO、LINE_NUMBER、CAVITY和SIGNON_ID进行GROUP BY，同时保留无缺陷（DEFECTID为0）的记录。但尝试的查询都返回20行，无法得到目标结果，且无法修改现有表结构，请问如何实现？

解决方案

方案1：保留缺陷明细+无缺陷分组标记

该方案会保留所有非0缺陷的原始明细，同时为全无缺陷的分组生成一行统一标记记录：

WITH Unpivoted AS (
    SELECT
        OC_DDATA_PC.PARTNO,
        CAST(OC_DDAT_AUX_PC.UDL40 AS datetime) AS SAMPLEDATE,
        stagingPLM.DBO.PLANTS.PLANT_NAME,
        OC_DDATA_PC.UDL2 as SHIFT,
        OC_DDATA_PC.UDL4 as SIGNON_ID,
        OC_DDATA_PC.UDL6 as LINE_NUMBER,
        OC_DDAT_AUX_PC.UDL12 as CAVITY,
        OC_DDAT_AUX_PC.UDL27 as SPEC_TYPE,
        OC_DDAT_AUX_PC.UDL28 as PROPERTY_TREE,
        OC_DDAT_AUX_PC.UDL30 as DEFECT_TYPE,
        OC_DDATA_PC.SSIZE,
        ColName,
        ColID,
        ColValue
    FROM            
        OC_DDATA_PC 
    INNER JOIN
        OC_DDAT_AUX_PC ON OC_DDATA_PC.PARTNO = OC_DDAT_AUX_PC.PARTNOAUX AND OC_DDATA_PC.DATETIME = OC_DDAT_AUX_PC.DATETIMEAUX 
    INNER JOIN
        stagingPLM.dbo.PLANTS ON OC_DDATA_PC.UDL1 = stagingPLM.dbo.PLANTS.PLANT_CODE
    CROSS APPLY (
        VALUES
        ('DEFECTID1',OC_DDATA_PC.DEFECTID1,OC_DDATA_PC.DEFECTCNT1),
        ('DEFECTID2',OC_DDATA_PC.DEFECTID2,OC_DDATA_PC.DEFECTCNT2),
        ('DEFECTID3',OC_DDATA_PC.DEFECTID3,OC_DDATA_PC.DEFECTCNT3),
        ('DEFECTID4',OC_DDATA_PC.DEFECTID4,OC_DDATA_PC.DEFECTCNT4),
        ('DEFECTID5',OC_DDATA_PC.DEFECTID5,OC_DDATA_PC.DEFECTCNT5),
        ('DEFECTID6',OC_DDATA_PC.DEFECTID6,OC_DDATA_PC.DEFECTCNT6),
        ('DEFECTID7',OC_DDATA_PC.DEFECTID7,OC_DDATA_PC.DEFECTCNT7),
        ('DEFECTID8',OC_DDATA_PC.DEFECTID8,OC_DDATA_PC.DEFECTCNT8),
        ('DEFECTID9',OC_DDATA_PC.DEFECTID9,OC_DDATA_PC.DEFECTCNT9),
        ('DEFECTID10',OC_DDATA_PC.DEFECTID10,OC_DDATA_PC.DEFECTCNT10),
        ('DEFECTID11',OC_DDATA_PC.DEFECTID11,OC_DDATA_PC.DEFECTCNT11),
        ('DEFECTID12',OC_DDATA_PC.DEFECTID12,OC_DDATA_PC.DEFECTCNT12),
        ('DEFECTID13',OC_DDATA_PC.DEFECTID13,OC_DDATA_PC.DEFECTCNT13),
        ('DEFECTID14',OC_DDATA_PC.DEFECTID14,OC_DDATA_PC.DEFECTCNT14),
        ('DEFECTID15',OC_DDATA_PC.DEFECTID15,OC_DDATA_PC.DEFECTCNT15),
        ('DEFECTID16',OC_DDATA_PC.DEFECTID16,OC_DDATA_PC.DEFECTCNT16),
        ('DEFECTID17',OC_DDATA_PC.DEFECTID17,OC_DDATA_PC.DEFECTCNT17),
        ('DEFECTID18',OC_DDATA_PC.DEFECTID18,OC_DDATA_PC.DEFECTCNT18),
        ('DEFECTID19',OC_DDATA_PC.DEFECTID19,OC_DDATA_PC.DEFECTCNT19),
        ('DEFECTID20',OC_DDATA_PC.DEFECTID20,OC_DDATA_PC.DEFECTCNT20)
    ) AS Unpvt(ColName, ColID, ColValue)
    WHERE OC_DDAT_AUX_PC.UDL28 LIKE 'PULP %'
),
GroupedDefects AS (
    -- 筛选并保留所有非0的缺陷明细
    SELECT
        SAMPLEDATE,
        PARTNO,
        LINE_NUMBER,
        CAVITY,
        SIGNON_ID,
        PLANT_NAME,
        SHIFT,
        SPEC_TYPE,
        PROPERTY_TREE,
        DEFECT_TYPE,
        SSIZE,
        ColName,
        ColID,
        ColValue
    FROM Unpivoted
    WHERE ColID <> 0
),
NoDefectGroups AS (
    -- 为全无缺陷的分组生成一行标记记录
    SELECT DISTINCT
        SAMPLEDATE,
        PARTNO,
        LINE_NUMBER,
        CAVITY,
        SIGNON_ID,
        PLANT_NAME,
        SHIFT,
        SPEC_TYPE,
        PROPERTY_TREE,
        DEFECT_TYPE,
        SSIZE,
        'NO_DEFECT' AS ColName,
        0 AS ColID,
        0 AS ColValue
    FROM Unpivoted
    WHERE NOT EXISTS (
        SELECT 1
        FROM Unpivoted u2
        WHERE u2.SAMPLEDATE = Unpivoted.SAMPLEDATE
          AND u2.PARTNO = Unpivoted.PARTNO
          AND u2.LINE_NUMBER = Unpivoted.LINE_NUMBER
          AND u2.CAVITY = Unpivoted.CAVITY
          AND u2.SIGNON_ID = Unpivoted.SIGNON_ID
          AND u2.ColID <> 0
    )
)
-- 合并两类结果
SELECT * FROM GroupedDefects
UNION ALL
SELECT * FROM NoDefectGroups
ORDER BY SAMPLEDATE, PARTNO, LINE_NUMBER, CAVITY, SIGNON_ID;

方案2：每个分组仅返回一行（聚合缺陷信息）

如果需求是每个分组只显示一行，聚合所有缺陷信息并标记无缺陷状态，可使用以下代码：

WITH Unpivoted AS (
    SELECT
        OC_DDATA_PC.PARTNO,
        CAST(OC_DDAT_AUX_PC.UDL40 AS datetime) AS SAMPLEDATE,
        stagingPLM.DBO.PLANTS.PLANT_NAME,
        OC_DDATA_PC.UDL2 as SHIFT,
        OC_DDATA_PC.UDL4 as SIGNON_ID,
        OC_DDATA_PC.UDL6 as LINE_NUMBER,
        OC_DDAT_AUX_PC.UDL12 as CAVITY,
        OC_DDAT_AUX_PC.UDL27 as SPEC_TYPE,
        OC_DDAT_AUX_PC.UDL28 as PROPERTY_TREE,
        OC_DDAT_AUX_PC.UDL30 as DEFECT_TYPE,
        OC_DDATA_PC.SSIZE,
        ColID,
        ColValue
    FROM            
        OC_DDATA_PC 
    INNER JOIN
        OC_DDAT_AUX_PC ON OC_DDATA_PC.PARTNO = OC_DDAT_AUX_PC.PARTNOAUX AND OC_DDATA_PC.DATETIME = OC_DDAT_AUX_PC.DATETIMEAUX 
    INNER JOIN
        stagingPLM.dbo.PLANTS ON OC_DDATA_PC.UDL1 = stagingPLM.dbo.PLANTS.PLANT_CODE
    CROSS APPLY (
        VALUES
        (OC_DDATA_PC.DEFECTID1,OC_DDATA_PC.DEFECTCNT1),
        (OC_DDATA_PC.DEFECTID2,OC_DDATA_PC.DEFECTCNT2),
        (OC_DDATA_PC.DEFECTID3,OC_DDATA_PC.DEFECTCNT3),
        (OC_DDATA_PC.DEFECTID4,OC_DDATA_PC.DEFECTCNT4),
        (OC_DDATA_PC.DEFECTID5,OC_DDATA_PC.DEFECTCNT5),
        (OC_DDATA_PC.DEFECTID6,OC_DDATA_PC.DEFECTCNT6),
        (OC_DDATA_PC.DEFECTID7,OC_DDATA_PC.DEFECTCNT7),
        (OC_DDATA_PC.DEFECTID8,OC_DDATA_PC.DEFECTCNT8),
        (OC_DDATA_PC.DEFECTID9,OC_DDATA_PC.DEFECTCNT9),
        (OC_DDATA_PC.DEFECTID10,OC_DDATA_PC.DEFECTCNT10),
        (OC_DDATA_PC.DEFECTID11,OC_DDATA_PC.DEFECTCNT11),
        (OC_DDATA_PC.DEFECTID12,OC_DDATA_PC.DEFECTCNT12),
        (OC_DDATA_PC.DEFECTID13,OC_DDATA_PC.DEFECTCNT13),
        (OC_DDATA_PC.DEFECTID14,OC_DDATA_PC.DEFECTCNT14),
        (OC_DDATA_PC.DEFECTID15,OC_DDATA_PC.DEFECTCNT15),
        (OC_DDATA_PC.DEFECTID16,OC_DDATA_PC.DEFECTCNT16),
        (OC_DDATA_PC.DEFECTID17,OC_DDATA_PC.DEFECTCNT17),
        (OC_DDATA_PC.DEFECTID18,OC_DDATA_PC.DEFECTCNT18),
        (OC_DDATA_PC.DEFECTID19,OC_DDATA_PC.DEFECTCNT19),
        (OC_DDATA_PC.DEFECTID20,OC_DDATA_PC.DEFECTCNT20)
    ) AS Unpvt(ColID, ColValue)
    WHERE OC_DDAT_AUX_PC.UDL28 LIKE 'PULP %'
)
SELECT
    SAMPLEDATE,
    PARTNO,
    LINE_NUMBER,
    CAVITY,
    SIGNON_ID,
    PLANT_NAME,
    SHIFT,
    SPEC_TYPE,
    PROPERTY_TREE,
    DEFECT_TYPE,
    SSIZE,
    -- 聚合非0缺陷ID，全0时显示"0"
    CASE WHEN MAX(ColID) = 0 THEN '0' ELSE STRING_AGG(CAST(ColID AS VARCHAR(10)), ', ') END AS DEFECT_IDS,
    -- 汇总缺陷计数，无缺陷时为0
    SUM(CASE WHEN ColID <> 0 THEN ColValue ELSE 0 END) AS TOTAL_DEFECT_COUNT
FROM Unpivoted
GROUP BY
    SAMPLEDATE,
    PARTNO,
    LINE_NUMBER,
    CAVITY,
    SIGNON_ID,
    PLANT_NAME,
    SHIFT,
    SPEC_TYPE,
    PROPERTY_TREE,
    DEFECT_TYPE,
    SSIZE
ORDER BY SAMPLEDATE, PARTNO, LINE_NUMBER, CAVITY, SIGNON_ID;

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.27 14:48:10