如何将子查询与大型Union查询关联，避免硬编码Job编号？

用于获取目标JOB的子查询 #

该子查询返回指定条件下的JOB和PART列表：

SELECT DISTINCT X.JOB, FOO.PART FROM 
  (SELECT C.PART, C.ORDER_NO, C.ORDER_LINE AS ORD_LINE 
   FROM V_ORDER_HIST_LINE C 
   WHERE C.DATE_INVOICE = '2024-11-04' AND C.QTY_SHIPPED > 0 AND C.DATE_SHIPPED IS NOT NULL 
   AND C.PRODUCT_LINE <> 'GT' AND C.ORDER_LINE <> '8000' AND C.PART NOT LIKE 'PROGRESS%' 
  ) FOO 
LEFT OUTER JOIN V_JOB_HEADER X ON FOO.ORDER_NO = X.SALES_ORDER AND LEFT(FOO.ORD_LINE, 3) = X.SALES_ORDER_LINE

返回结果：

原Union查询（硬编码JOB） #

原查询通过硬编码JOB列表过滤数据：

SELECT SOO.JOB, SOO.MACHINE_NEW,
       SUM(SOO.EST_HOURS) AS EST_HOURS,
       SUM(SOO.ACT_HOURS) AS ACT_HOURS
FROM
(   SELECT A.JOB,
        CASE 
            WHEN A.MACHINE IN ('AENA','ZENA','ZEN1') OR A.MACHINE LIKE 'EN%' THEN 'ENGINEERING'
            WHEN A.MACHINE IN ('APLN','PES','PJB','PLF','PJH','PTH') THEN 'PLANNING'
            WHEN A.MACHINE LIKE 'APR%' OR A.MACHINE LIKE 'PR%' THEN 'PROGRAMMING'
            WHEN A.MACHINE IN ('ASAW','ZSAW') THEN 'SAW'
            WHEN A.MACHINE IN ('AWJ1','ZWJ1') THEN 'WATERJET'
            WHEN A.MACHINE = '' THEN 'REWORK'
            ELSE A.MACHINE
        END AS MACHINE_NEW,
        SUM(A.HOURS_WORKED) AS ACT_HOURS,
        NULL AS EST_HOURS
    FROM V_JOB_DETAIL A
    WHERE A.LMO = 'L' AND A.JOB IN ('083802','089701','089702','089704','089705','089706') 
    GROUP BY A.JOB, MACHINE_NEW
UNION
    SELECT B.JOB,
        CASE 
            WHEN B.PART IN ('AENA','ZENA','ZEN1') OR B.PART LIKE 'EN%' THEN 'ENGINEERING'
            WHEN B.PART IN ('APLN','PES','PJB','PLF','PJH','PTH') THEN 'PLANNING'
            WHEN B.PART LIKE 'APR%' OR B.PART LIKE 'PR%' THEN 'PROGRAMMING'
            WHEN B.PART IN ('ASAW','ZSAW') THEN 'SAW'
            WHEN B.PART IN ('AWJ1','ZWJ1') THEN 'WATERJET'
            WHEN B.PART = '' THEN 'REWORK'
            ELSE B.PART
        END AS MACHINE_NEW,
        NULL AS ACT_HOURS,
        SUM(B.HOURS_ESTIMATED) AS EST_HOURS
    FROM V_JOB_OPERATIONS B
    WHERE B.LMO = 'L' AND B.JOB IN ('083802','089701','089702','089704','089705','089706') 
    GROUP BY B.JOB, MACHINE_NEW
) SOO
GROUP BY SOO.JOB, SOO.MACHINE_NEW

修改后的动态关联查询 #

将硬编码的JOB列表替换为子查询，实现动态过滤：

SELECT SOO.JOB, SOO.MACHINE_NEW,
       SUM(SOO.EST_HOURS) AS EST_HOURS,
       SUM(SOO.ACT_HOURS) AS ACT_HOURS
FROM
(   SELECT A.JOB,
        CASE 
            WHEN A.MACHINE IN ('AENA','ZENA','ZEN1') OR A.MACHINE LIKE 'EN%' THEN 'ENGINEERING'
            WHEN A.MACHINE IN ('APLN','PES','PJB','PLF','PJH','PTH') THEN 'PLANNING'
            WHEN A.MACHINE LIKE 'APR%' OR A.MACHINE LIKE 'PR%' THEN 'PROGRAMMING'
            WHEN A.MACHINE IN ('ASAW','ZSAW') THEN 'SAW'
            WHEN A.MACHINE IN ('AWJ1','ZWJ1') THEN 'WATERJET'
            WHEN A.MACHINE = '' THEN 'REWORK'
            ELSE A.MACHINE
        END AS MACHINE_NEW,
        SUM(A.HOURS_WORKED) AS ACT_HOURS,
        NULL AS EST_HOURS
    FROM V_JOB_DETAIL A
    WHERE A.LMO = 'L' 
      AND A.JOB IN (
          SELECT DISTINCT X.JOB 
          FROM (
              SELECT C.PART, C.ORDER_NO, C.ORDER_LINE AS ORD_LINE 
              FROM V_ORDER_HIST_LINE C 
              WHERE C.DATE_INVOICE = '2024-11-04' AND C.QTY_SHIPPED > 0 AND C.DATE_SHIPPED IS NOT NULL 
                AND C.PRODUCT_LINE <> 'GT' AND C.ORDER_LINE <> '8000' AND C.PART NOT LIKE 'PROGRESS%' 
          ) FOO 
          LEFT OUTER JOIN V_JOB_HEADER X ON FOO.ORDER_NO = X.SALES_ORDER AND LEFT(FOO.ORD_LINE, 3) = X.SALES_ORDER_LINE
          WHERE X.JOB IS NOT NULL -- 过滤未关联到JOB的记录
      )
    GROUP BY A.JOB, MACHINE_NEW
UNION
    SELECT B.JOB,
        CASE 
            WHEN B.PART IN ('AENA','ZENA','ZEN1') OR B.PART LIKE 'EN%' THEN 'ENGINEERING'
            WHEN B.PART IN ('APLN','PES','PJB','PLF','PJH','PTH') THEN 'PLANNING'
            WHEN B.PART LIKE 'APR%' OR B.PART LIKE 'PR%' THEN 'PROGRAMMING'
            WHEN B.PART IN ('ASAW','ZSAW') THEN 'SAW'
            WHEN B.PART IN ('AWJ1','ZWJ1') THEN 'WATERJET'
            WHEN B.PART = '' THEN 'REWORK'
            ELSE B.PART
        END AS MACHINE_NEW,
        NULL AS ACT_HOURS,
        SUM(B.HOURS_ESTIMATED) AS EST_HOURS
    FROM V_JOB_OPERATIONS B
    WHERE B.LMO = 'L' 
      AND B.JOB IN (
          SELECT DISTINCT X.JOB 
          FROM (
              SELECT C.PART, C.ORDER_NO, C.ORDER_LINE AS ORD_LINE 
              FROM V_ORDER_HIST_LINE C 
              WHERE C.DATE_INVOICE = '2024-11-04' AND C.QTY_SHIPPED > 0 AND C.DATE_SHIPPED IS NOT NULL 
                AND C.PRODUCT_LINE <> 'GT' AND C.ORDER_LINE <> '8000' AND C.PART NOT LIKE 'PROGRESS%' 
          ) FOO 
          LEFT OUTER JOIN V_JOB_HEADER X ON FOO.ORDER_NO = X.SALES_ORDER AND LEFT(FOO.ORD_LINE, 3) = X.SALES_ORDER_LINE
          WHERE X.JOB IS NOT NULL -- 过滤未关联到JOB的记录
      )
    GROUP BY B.JOB, MACHINE_NEW
) SOO
GROUP BY SOO.JOB, SOO.MACHINE_NEW

优化建议：使用CTE简化代码 #

通过**公共表表达式（CTE）**定义一次目标JOB逻辑，后续重复引用，提升可读性和维护性：

WITH TargetJobs AS (
    SELECT DISTINCT X.JOB 
    FROM (
        SELECT C.PART, C.ORDER_NO, C.ORDER_LINE AS ORD_LINE 
        FROM V_ORDER_HIST_LINE C 
        WHERE C.DATE_INVOICE = '2024-11-04' AND C.QTY_SHIPPED > 0 AND C.DATE_SHIPPED IS NOT NULL 
          AND C.PRODUCT_LINE <> 'GT' AND C.ORDER_LINE <> '8000' AND C.PART NOT LIKE 'PROGRESS%' 
    ) FOO 
    LEFT OUTER JOIN V_JOB_HEADER X ON FOO.ORDER_NO = X.SALES_ORDER AND LEFT(FOO.ORD_LINE, 3) = X.SALES_ORDER_LINE
    WHERE X.JOB IS NOT NULL
)
SELECT SOO.JOB, SOO.MACHINE_NEW,
       SUM(SOO.EST_HOURS) AS EST_HOURS,
       SUM(SOO.ACT_HOURS) AS ACT_HOURS
FROM
(   SELECT A.JOB,
        CASE 
            WHEN A.MACHINE IN ('AENA','ZENA','ZEN1') OR A.MACHINE LIKE 'EN%' THEN 'ENGINEERING'
            WHEN A.MACHINE IN ('APLN','PES','PJB','PLF','PJH','PTH') THEN 'PLANNING'
            WHEN A.MACHINE LIKE 'APR%' OR A.MACHINE LIKE 'PR%' THEN 'PROGRAMMING'
            WHEN A.MACHINE IN ('ASAW','ZSAW') THEN 'SAW'
            WHEN A.MACHINE IN ('AWJ1','ZWJ1') THEN 'WATERJET'
            WHEN A.MACHINE = '' THEN 'REWORK'
            ELSE A.MACHINE
        END AS MACHINE_NEW,
        SUM(A.HOURS_WORKED) AS ACT_HOURS,
        NULL AS EST_HOURS
    FROM V_JOB_DETAIL A
    JOIN TargetJobs TJ ON A.JOB = TJ.JOB
    WHERE A.LMO = 'L'
    GROUP BY A.JOB, MACHINE_NEW
UNION
    SELECT B.JOB,
        CASE 
            WHEN B.PART IN ('AENA','ZENA','ZEN1') OR B.PART LIKE 'EN%' THEN 'ENGINEERING'
            WHEN B.PART IN ('APLN','PES','PJB','PLF','PJH','PTH') THEN 'PLANNING'
            WHEN B.PART LIKE 'APR%' OR B.PART LIKE 'PR%' THEN 'PROGRAMMING'
            WHEN B.PART IN ('ASAW','ZSAW') THEN 'SAW'
            WHEN B.PART IN ('AWJ1','ZWJ1') THEN 'WATERJET'
            WHEN B.PART = '' THEN 'REWORK'
            ELSE B.PART
        END AS MACHINE_NEW,
        NULL AS ACT_HOURS,
        SUM(B.HOURS_ESTIMATED) AS EST_HOURS
    FROM V_JOB_OPERATIONS B
    JOIN TargetJobs TJ ON B.JOB = TJ.JOB
    WHERE B.LMO = 'L'
    GROUP BY B.JOB, MACHINE_NEW
) SOO
GROUP BY SOO.JOB, SOO.MACHINE_NEW

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