SQL使用首个查询结果执行第二个查询的两表关联实现方案

你之前写的CTE方案跑不通，是几个语法和逻辑错误叠在一起导致的：

• CTE里的账号字段别名多打了开头的空格：写的是as ' accountnumber'，别名带前置空格，后续关联的时候找不到对应字段
• CTE内的关联条件引用了不存在的表别名：metg.cnms_id=me.cnms_id里的me在整段代码里从来没定义过，直接触发语法错误
• 过滤逻辑写法错误：WHERE pm.AcctNumb in (counta.accountnumber)这种写法不成立，IN子句里不能直接挂关联后CTE的字段，要么写子查询，要么靠JOIN过滤，你同时写LEFT JOIN和这个错误的IN条件，逻辑本身也冲突
• 字段引用错误：关联CTE的时候写的Pm.accountnumber，但pm表的账号原始字段名是AcctNumb，accountnumber是你SELECT层才定义的别名，JOIN/WHERE执行顺序在SELECT之前，不能直接引用这个别名

WITH counta AS (
    SELECT DISTINCT
        Pd.Cpd AS accountnumber
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    -- 注意：原代码里的me是未定义的笔误，这里默认用pd表的cnms_id关联，你根据实际业务表替换即可
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg 
        ON metg.cnms_id = pd.cnms_id
)
SELECT 
    pm.AcctNumb AS accountnumber,
    SUM(CASE WHEN pm.cusid IN ('cr') THEN 1 ELSE 0 END) AS CA,
    SUM(CASE WHEN pm.cusid IN ('gb') THEN 1 ELSE 0 END) AS GB,
    SUM(CASE WHEN pm.cusid IN ('tev','offev','Lastev') THEN 1 ELSE 0 END) AS chr,
    SUM(CASE WHEN pm.cusid IN ('pm','pr','che') THEN 1 ELSE 0 END) AS Act,
    SUM(CASE WHEN pm.cusid IN ('supev','tev') THEN 1 ELSE 0 END) AS Fulfillment
FROM ops.medadata pm WITH (NOLOCK)
INNER JOIN mw.pim_acct Ma WITH (NOLOCK) 
    ON ma.AcctNumb = pm.AcctNumb
-- 用INNER JOIN关联CTE直接过滤账号，不需要额外写WHERE IN条件
INNER JOIN counta 
    ON pm.AcctNumb = counta.accountnumber
GROUP BY 
    pm.AcctNumb

如果你更习惯用IN做过滤，也可以删掉INNER JOIN counta那行，把WHERE条件改成下面这样，执行效果完全一致：

WHERE pm.AcctNumb IN (SELECT accountnumber FROM counta)

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