SQL查询：按JobNum/AssemblyNum返回生产位置，无匹配返回默认值

问题核心 #

需要按JobNum和AssemblyNum分组返回结果：若组内存在OpCode为PU/PU-A/PU-B/PU-C的记录，返回对应预设值（PU→Sub，PU-A→Sub A等）；若组内无此类记录，返回Inhouse。之前用单纯的CASE/IF表达式会丢失无PU类记录的分组，以下是两种可行方案：

方案一：聚合函数+COALESCE #

利用MAX聚合提取组内的PU类对应值，无PU类时用COALESCE返回默认值：

SELECT 
    JobNum,
    AssemblyNum,
    COALESCE(
        MAX(CASE 
            WHEN OpCode = 'PU' THEN 'Sub'
            WHEN OpCode = 'PU-A' THEN 'Sub A'
            WHEN OpCode = 'PU-B' THEN 'Sub B'
            WHEN OpCode = 'PU-C' THEN 'Sub C'
            ELSE NULL
        END),
        'Inhouse'
    ) AS Location
FROM YourTableName
GROUP BY JobNum, AssemblyNum;

逻辑说明：CASE将PU类转成对应值，其他转NULL；MAX会忽略NULL，返回组内有效的PU类对应值；若组内全为NULL，COALESCE兜底返回Inhouse，确保所有分组都有结果。

方案二：窗口函数优先取值 #

通过窗口函数给PU类记录标记更高优先级，取每组的第一条记录：

WITH RankedOps AS (
    SELECT 
        JobNum,
        AssemblyNum,
        CASE 
            WHEN OpCode = 'PU' THEN 'Sub'
            WHEN OpCode = 'PU-A' THEN 'Sub A'
            WHEN OpCode = 'PU-B' THEN 'Sub B'
            WHEN OpCode = 'PU-C' THEN 'Sub C'
            ELSE 'Inhouse'
        END AS Location,
        ROW_NUMBER() OVER (
            PARTITION BY JobNum, AssemblyNum 
            ORDER BY 
                CASE WHEN OpCode IN ('PU','PU-A','PU-B','PU-C') THEN 0 ELSE 1 END,
                OpCode
        ) AS rn
    FROM YourTableName
)
SELECT JobNum, AssemblyNum, Location
FROM RankedOps
WHERE rn = 1;

逻辑说明：窗口函数中，PU类记录的排序优先级设为0（靠前），其他为1（靠后）；ROW_NUMBER()给每组记录编号后，取rn=1的记录，优先返回PU类对应值，无PU类时返回Inhouse。

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