基于另一表更新SQL表多列的优化查询求助

报错原因分析 #

从报错信息（无效的列名'total_count'、'pass_count'、'fail_count'）可以看出，核心问题是CTE定义的列别名和更新语句中引用的列名不匹配：
你在CTE里定义的统计列别名是TOTAL_SCENARIOS_COUNT、PASS_SCENARIOS_COUNT、FAIL_SCENARIOS_COUNT，但更新时却写成了t0.total_count等，数据库找不到对应的列，所以报错。

另外，CTE里用HAVING A_id = 4779的写法不够高效，应该先通过WHERE过滤特定A_id再分组，减少分组计算的数据量。

修正后的CTE关联更新语句 #

针对特定A_id（比如4779），正确的写法如下：

WITH T0 AS (
    SELECT 
        A_id,
        COUNT(A_id) AS total_count,  -- 别名和TableA的列名保持一致
        COUNT(CASE WHEN isPassed = 'Y' THEN 1 END) AS pass_count,
        COUNT(CASE WHEN isPassed = 'N' THEN 1 END) AS fail_count
    FROM TableB  -- 替换成实际表名RT_TEST_RUN_SCENARIO
    WHERE A_id = 4779  -- 先过滤再分组，提升效率
    GROUP BY A_id
)
UPDATE TableA
SET 
    total_count = T0.total_count,
    pass_count = T0.pass_count,
    fail_count = T0.fail_count
FROM TableA
INNER JOIN T0 ON TableA.A_id = T0.A_id;

如果需要批量更新所有A_id的统计值，去掉CTE里的WHERE A_id = 4779即可，这样会一次性更新TableA中所有存在对应数据的A_id行。

其他可行的更新方式 #

方式1：子查询关联更新（无需CTE） #

UPDATE TableA
SET 
    total_count = stats.total_count,
    pass_count = stats.pass_count,
    fail_count = stats.fail_count
FROM TableA
INNER JOIN (
    SELECT 
        A_id,
        COUNT(A_id) AS total_count,
        COUNT(CASE WHEN isPassed = 'Y' THEN 1 END) AS pass_count,
        COUNT(CASE WHEN isPassed = 'N' THEN 1 END) AS fail_count
    FROM TableB
    WHERE A_id = 4779
    GROUP BY A_id
) stats ON TableA.A_id = stats.A_id;

方式2：使用APPLY（适用于SQL Server） #

如果需要处理单个A_id的更新，APPLY写法更简洁：

UPDATE TableA
SET 
    total_count = stats.total_count,
    pass_count = stats.pass_count,
    fail_count = stats.fail_count
FROM TableA
CROSS APPLY (
    SELECT 
        COUNT(A_id) AS total_count,
        SUM(CASE WHEN isPassed = 'Y' THEN 1 ELSE 0 END) AS pass_count,
        SUM(CASE WHEN isPassed = 'N' THEN 1 ELSE 0 END) AS fail_count
    FROM TableB
    WHERE TableB.A_id = TableA.A_id
) stats
WHERE TableA.A_id = 4779;

方式3：使用聚合函数直接关联（简化版） #

针对单个A_id，也可以用JOIN+聚合的方式：

UPDATE a
SET 
    total_count = COUNT(b.A_id),
    pass_count = SUM(CASE WHEN b.isPassed = 'Y' THEN 1 ELSE 0 END),
    fail_count = SUM(CASE WHEN b.isPassed = 'N' THEN 1 ELSE 0 END)
FROM TableA a
LEFT JOIN TableB b ON a.A_id = b.A_id
WHERE a.A_id = 4779
GROUP BY a.A_id, a.total_count, a.pass_count, a.fail_count;

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