派生表查询报ERROR 1054未知列NName：字段值计数查询修正

问题背景 #

我需要编写查询语句创建统计表格，统计指定字段各值的出现次数；因单表数据不足，需关联另一表获取NName字段。

表结构与数据

Records_table：

Ref Score Iteration
1   High  1
1   Middle 2
2   Middle 1
2   Middle 2
2   Low    3
3   Low    1
4   High   1
4   High   2
4   High   3

Name_table：

Ref NName
1   Falcon
2   Willow
3   Lance
4   Ranger

期望输出

NName  High Middle Low
Falcon 1    1      0
Willow 0    2      1
Lance  0    0      1
Ranger 3    0      0

现有查询语句及报错

我写了以下查询（已移除多余的Ref字段）：

SELECT tc.nname AS NName, COUNT(High) High, COUNT(Middle) Middle, COUNT(Low) Low 
FROM (
SELECT NName, 'High' High, NULL Middle, NULL Low 
FROM records_table tr JOIN name_table tc ON tc.ref = tr.ref WHERE tr.score = 'High'
UNION ALL
SELECT NName, NULL, 'Middle', NULL 
FROM records_table tr JOIN name_table tc ON tc.ref = tr.ref WHERE tr.score = 'Middle'
UNION ALL
SELECT NName, NULL, NULL, 'Low' 
FROM records_table tr JOIN name_table tc ON tc.ref = tr.ref WHERE tr.score = 'Low'
) T 
GROUP BY NName;

执行后报错：

ERROR 1054 (42S22): Unknown column 'NName' in 'field list'

问题分析 #

报错的核心原因是外部查询引用了不存在的表别名：

• 子查询中的tc是name_table的别名，但这个别名仅在子查询的各个JOIN范围内有效，外部查询的数据源是子查询的别名T，无法直接访问tc表。
• 子查询的结果集T已经包含了NName列，所以外部查询应该直接引用T.NName（或省略表名直接写NName）。

另外，原查询的COUNT(High)写法虽然能工作，但逻辑上是统计High列非NULL的行数，本质和SUM(CASE WHEN ...)是一样的，不过还有更简洁高效的写法（不需要用UNION ALL拆分后再聚合）。

修正方案 #

方案1：修正原查询的字段引用

只需要把外部查询的tc.nname改成T.NName即可：

SELECT T.NName, COUNT(High) AS High, COUNT(Middle) AS Middle, COUNT(Low) AS Low 
FROM (
SELECT tc.NName, 'High' AS High, NULL AS Middle, NULL AS Low 
FROM records_table tr JOIN name_table tc ON tc.ref = tr.ref WHERE tr.score = 'High'
UNION ALL
SELECT tc.NName, NULL AS High, 'Middle' AS Middle, NULL AS Low 
FROM records_table tr JOIN name_table tc ON tc.ref = tr.ref WHERE tr.score = 'Middle'
UNION ALL
SELECT tc.NName, NULL AS High, NULL AS Middle, 'Low' AS Low 
FROM records_table tr JOIN name_table tc ON tc.ref = tr.ref WHERE tr.score = 'Low'
) T 
GROUP BY T.NName;

方案2：更高效的条件聚合写法（推荐）

用SUM(CASE WHEN ...)直接在关联后的表上做条件统计，避免UNION ALL的额外开销，逻辑更清晰：

SELECT 
    tc.NName,
    SUM(CASE WHEN tr.Score = 'High' THEN 1 ELSE 0 END) AS High,
    SUM(CASE WHEN tr.Score = 'Middle' THEN 1 ELSE 0 END) AS Middle,
    SUM(CASE WHEN tr.Score = 'Low' THEN 1 ELSE 0 END) AS Low
FROM name_table tc
LEFT JOIN records_table tr ON tc.Ref = tr.Ref
GROUP BY tc.NName;

这里用LEFT JOIN可以确保即使某个NName没有对应的记录（比如如果有个Ref在name_table但不在records_table），也能显示为0，更符合统计需求。

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