如何用SQL筛选含指定供应商零件的工单并返回全量零件

编辑说明：已添加可复现查询语句。

我有一组工单数据，每个工单包含多个零件编号，且每个零件对应不同供应商。我需要仅返回包含指定供应商（PARTS_EMPORIUM、LABEL_KING）零件的工单，同时返回这些工单中的所有零件。在Tableau中我可以通过创建计算字段实现：{FIXED [JOB_NUM]: COUNTD(IF [SUPPLIER]="SUPLR_A" OR [SUPPLIER]="SUPLR_B" THEN [JOB_NUM] END)}（命名为Calc_1），再创建另一个计算字段：IF [Calc_1] = 1 THEN [JOB_NUM] END，之后过滤掉NULL值。但我希望直接用SQL实现，避免不必要的数据提取与处理。

我尝试模仿Tableau的逻辑，统计包含指定供应商的工单的唯一计数，但仅返回了对应供应商零件的行数据，无法将该计数同步到同工单的所有行。

DROP TABLE IF EXISTS #JOB_DATA
DROP TABLE IF EXISTS #SUPPLIER_DATA

CREATE TABLE #JOB_DATA (
    JOB_NUM varchar(255),
    PART_NUM varchar(255),
    QTY varchar(255)
);

CREATE TABLE #SUPPLIER_DATA (
    PART_NUM varchar(255),
    SUPPLIER_NUM varchar(255),
    SUPPLIER_NAME VARCHAR(255)
);

INSERT INTO #JOB_DATA
VALUES 
    ('A-1','PN-004','1'),
    ('A-1','PN-009','1'),
    ('A-1','PN-015','1'),
    ('A-1','PN-005','3'),
    ('A-1','PN-006','1'),
    ('B-22','PN-004','1'),
    ('B-22','PN-007','2'),
    ('B-22','PN-009','1'),
    ('C-333','PN-004','5'),
    ('C-333','PN-009','1'),
    ('C-333','PN-010','1');

INSERT INTO #SUPPLIER_DATA
VALUES 
    ('PN-001','A13582','PARTS_EMPORIUM'),
    ('PN-002','A13582','PARTS_EMPORIUM'),
    ('PN-003','V23451','LABEL_KING'),
    ('PN-004','W69851','PLASTICS_INC'),
    ('PN-005','A13582','PARTS_EMPORIUM'),
    ('PN-006','A13582','PARTS_EMPORIUM'),
    ('PN-007','V23451','LABEL_KING'),
    ('PN-008','V23451','LABEL_KING'),
    ('PN-009','W69851','PLASTICS_INC'),
    ('PN-010','W69851','PLASTICS_INC'),
    ('PN-011','W69851','PLASTICS_INC'),
    ('PN-012','L68529','METALWORKS'),
    ('PN-013','L68529','METALWORKS'),
    ('PN-014','L68529','METALWORKS'),
    ('PN-015','V23451','LABEL_KING');

WITH M AS (
    SELECT A.JOB_NUM, A.PART_NUM, A.QTY, B.SUPPLIER_NAME
    FROM #JOB_DATA AS A
    LEFT JOIN (SELECT PART_NUM, SUPPLIER_NAME FROM #SUPPLIER_DATA WHERE SUPPLIER_NAME IN ('PARTS_EMPORIUM','LABEL_KING')) AS B
        ON A.PART_NUM = B.PART_NUM
), T AS (
    SELECT SUPPLIER_NAME, JOB_NUM, COUNT(DISTINCT JOB_NUM) AS JobCount
    FROM M
    GROUP BY JOB_NUM, SUPPLIER_NAME
)
SELECT T.JobCount, M.*
FROM M
LEFT JOIN T
    ON M.JOB_NUM = T.JOB_NUM
    AND M.SUPPLIER_NAME = T.SUPPLIER_NAME

当前返回数据 #

期望返回数据 #

最终我会通过WHERE JobCount = 1过滤掉NULL值，恳请提供解决方案。

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