使用多子查询+union统计时报Unknown column in field list错误如何解决

报错根因 #

UNION 合并多个查询结果集时，最终返回的列名以第一个 SELECT 语句的别名为准。你第一个子查询的统计列别名为 dCount，第二个子查询写的 sCount 会被直接忽略，合并后的子查询表只有 dCount、Occupation 两列，外层查询引用不存在的 sCount 自然会报错。

优化后可运行代码 #

完全可以不用多层嵌套和UNION，用GROUP BY统计更简洁：

SELECT CONCAT("There are a total of ", occupation_count, " ", Occupation, "s.") AS result
FROM (
    SELECT COUNT(Occupation) AS occupation_count, LOWER(Occupation) AS Occupation 
    FROM OCCUPATIONS 
    WHERE Occupation IN ('Doctor','Singer')
    GROUP BY LOWER(Occupation)
) AS m;

保留原UNION逻辑的修正版本 #

如果要保留你原来的UNION写法，统一统计列的别名即可：

SELECT 
    CASE
        WHEN Occupation = "doctor" THEN CONCAT("There are a total of ", count_num, " ", Occupation, "s.")
        WHEN Occupation = "singer" THEN CONCAT("There are a total of ", count_num, " ", Occupation, "s.")
    END AS result
FROM (
    SELECT COUNT(Occupation) AS count_num, LOWER(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Doctor'
    UNION ALL
    SELECT COUNT(Occupation) AS count_num, LOWER(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Singer'
) AS m;

注：这里用UNION ALL替代UNION，因为两个子查询的结果不会重复，不需要额外去重，执行效率更高。

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