SQL视图构建需求：筛选特定WC_Code的tJCLines.Qty1字段

我来帮你搞定这个需求！要实现只在WCCode等于'SLIT_DEC120'或'SLIT_DEC056'时才取Qty1的值，最直接的方法是用SQL的CASE条件表达式。下面分两种常见场景给出实现方案：

场景1：保留原有行结构（每行对应一条tJCLines记录） #

如果你的视图需要保持原来的行粒度（每个tJCLines记录对应一行结果），直接在SELECT列表里加入CASE表达式即可：

SELECT 
    RIGHT(dbo.tJCHeader.JCno, LEN(dbo.tJCHeader.JCno) - 1) AS 'WO NUMBER',
    dbo.tJCHeader.DATEfinish AS 'Date of Closure',
    dbo.tSalesOrderLink.SOno AS 'Sales Order',
    dbo.tJCHeader.FGCode AS 'Item Number',
    dbo.tBOMh.UDtxt1 AS 'Film Grade',
    dbo.tJCHeader.QTYstd AS 'Qty To produce',
    dbo.tJCHeader.QTYact AS 'Good Qty Done',
    '' AS 'Error Qty Done',
    RIGHT(RTRIM(dbo.tJCHeader.BOMCode), 6) AS Machine, -- 优化了原来的子查询写法
    dbo.tBOMh.UDval1 AS 'Roll Width',
    dbo.tBOMh.UDval2 AS 'Roll Length',
    '' AS 'sq metres',
    '' AS 'Core Cut time',
    '' AS 'Core Cut quantity',
    '' AS 'Core Nothch time',
    '' AS 'Core Notch quantity',
    '0.317' AS 'Th Setup time',
    '' AS 'Th Slit time',
    '' AS 'Th Packing time',
    '' AS 'Rolls Scrapped quantity',
    '' AS 'Rolls Scrapped sqm',
    '' AS 'Overruns quantity',
    '' AS 'Overuns sqm',
    dbo.tSalesOrderLink.CustomerDesc AS 'Client',
    '' AS 'Operator',
    dbo.tJCLines.Qty1,
    dbo.tJCLines.WCCode,
    -- 新增的条件取值字段
    CASE 
        WHEN dbo.tJCLines.WCCode IN ('SLIT_DEC120', 'SLIT_DEC056') THEN dbo.tJCLines.Qty1
        ELSE NULL -- 若要显示空字符串而非NULL，可改为 ELSE ''
    END AS 'Slit Specific Qty'
FROM 
    dbo.tBOMh 
INNER JOIN 
    dbo.tJCHeader ON dbo.tBOMh.BomCode = dbo.tJCHeader.BOMCode 
INNER JOIN 
    dbo.tJCLines ON dbo.tJCHeader.Indx = dbo.tJCLines.Indx 
LEFT OUTER JOIN 
    dbo.tSalesOrderLink ON dbo.tJCHeader.JCno = dbo.tSalesOrderLink.JCno

说明： #

• CASE表达式会逐行检查WCCode是否匹配指定值，匹配时返回对应Qty1，否则返回NULL（或空字符串，按需调整）。
• 我给新增字段起了别名'Slit Specific Qty'，你可以根据实际业务改成更贴合的名称。

场景2：按工单汇总（每个WO一行，合并符合条件的Qty1） #

如果你的视图需要按工单（WO NUMBER）聚合，把同一工单下符合条件的Qty1汇总起来，就需要用条件聚合函数（比如SUM/MAX）配合GROUP BY：

SELECT 
    RIGHT(dbo.tJCHeader.JCno, LEN(dbo.tJCHeader.JCno) - 1) AS 'WO NUMBER',
    dbo.tJCHeader.DATEfinish AS 'Date of Closure',
    dbo.tSalesOrderLink.SOno AS 'Sales Order',
    dbo.tJCHeader.FGCode AS 'Item Number',
    dbo.tBOMh.UDtxt1 AS 'Film Grade',
    dbo.tJCHeader.QTYstd AS 'Qty To produce',
    dbo.tJCHeader.QTYact AS 'Good Qty Done',
    '' AS 'Error Qty Done',
    RIGHT(RTRIM(dbo.tJCHeader.BOMCode), 6) AS Machine,
    dbo.tBOMh.UDval1 AS 'Roll Width',
    dbo.tBOMh.UDval2 AS 'Roll Length',
    '' AS 'sq metres',
    '' AS 'Core Cut time',
    '' AS 'Core Cut quantity',
    '' AS 'Core Nothch time',
    '' AS 'Core Notch quantity',
    '0.317' AS 'Th Setup time',
    '' AS 'Th Slit time',
    '' AS 'Th Packing time',
    '' AS 'Rolls Scrapped quantity',
    '' AS 'Rolls Scrapped sqm',
    '' AS 'Overruns quantity',
    '' AS 'Overuns sqm',
    dbo.tSalesOrderLink.CustomerDesc AS 'Client',
    '' AS 'Operator',
    -- 汇总符合条件的Qty1，用SUM求和，MAX取最大值，按需选择
    SUM(CASE 
            WHEN dbo.tJCLines.WCCode IN ('SLIT_DEC120', 'SLIT_DEC056') THEN dbo.tJCLines.Qty1
            ELSE 0 -- 若不想统计不符合条件的记录，改为 ELSE NULL（SUM会忽略NULL）
        END) AS 'Total Slit Qty'
FROM 
    dbo.tBOMh 
INNER JOIN 
    dbo.tJCHeader ON dbo.tBOMh.BomCode = dbo.tJCHeader.BOMCode 
INNER JOIN 
    dbo.tJCLines ON dbo.tJCHeader.Indx = dbo.tJCLines.Indx 
LEFT OUTER JOIN 
    dbo.tSalesOrderLink ON dbo.tJCHeader.JCno = dbo.tSalesOrderLink.JCno
GROUP BY
    RIGHT(dbo.tJCHeader.JCno, LEN(dbo.tJCHeader.JCno) - 1),
    dbo.tJCHeader.DATEfinish,
    dbo.tSalesOrderLink.SOno,
    dbo.tJCHeader.FGCode,
    dbo.tBOMh.UDtxt1,
    dbo.tJCHeader.QTYstd,
    dbo.tJCHeader.QTYact,
    RIGHT(RTRIM(dbo.tJCHeader.BOMCode), 6),
    dbo.tBOMh.UDval1,
    dbo.tBOMh.UDval2,
    dbo.tSalesOrderLink.CustomerDesc

说明： #

• 这里用SUM来汇总符合条件的Qty1，如果只需要取最大值，换成MAX即可。
• GROUP BY子句必须包含所有非聚合的字段，否则会报错。

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