多表聚合entrcod字段关联查询触发MySQL #1054错误排查

错误原因分析 #

第二个SQL触发MySQL返回#1054 - 在IN/ALL/ANY子查询中存在未知字段'entrcod'错误，核心是语法结构错误：你将AND entrcod IN (...)直接写在UNION语句之后，但UNION是用于合并多个SELECT结果集的关键字，无法直接在其后追加WHERE过滤条件。需要先把多表聚合的entrcod结果封装为子查询，再对这个子查询的结果进行过滤。

正确SQL写法 #

写法1：子查询聚合后过滤 #

先通过UNION把所有目标表的对应字段统一命名为entrcod，再将这个结果作为子查询，用IN条件匹配prospectuserclient的相关字段：

SELECT entrcod
FROM (
    SELECT entrcod FROM marchelot WHERE marcod = 494128
    UNION
    SELECT buccod AS entrcod FROM marchebuc WHERE marcod = 494128
    UNION
    SELECT ouvrcod AS entrcod FROM marcheouvrage WHERE marcod = 494128
    UNION
    SELECT oeuvcod AS entrcod FROM marcheoeuvre WHERE marcod = 494128
) AS all_entrcods
WHERE entrcod IN (
    SELECT entrcod FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT betcod FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT buccod FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT ouvrcod FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT oeuvcod FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
);

写法2：用JOIN实现交集（性能更优） #

如果数据量较大，用JOIN替代IN子查询通常性能更好，逻辑是将两边的聚合结果分别作为临时表，再做内连接：

SELECT DISTINCT a.entrcod
FROM (
    SELECT entrcod FROM marchelot WHERE marcod = 494128
    UNION
    SELECT buccod AS entrcod FROM marchebuc WHERE marcod = 494128
    UNION
    SELECT ouvrcod AS entrcod FROM marcheouvrage WHERE marcod = 494128
    UNION
    SELECT oeuvcod AS entrcod FROM marcheoeuvre WHERE marcod = 494128
) AS a
JOIN (
    SELECT entrcod AS match_code FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT betcod AS match_code FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT buccod AS match_code FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT ouvrcod AS match_code FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
    UNION
    SELECT oeuvcod AS match_code FROM prospectuserclient WHERE procod = 20347 AND usercod = '001'
) AS b ON a.entrcod = b.match_code;

补充说明 #

• 使用UNION会自动去重，如果需要保留重复值（比如统计出现次数），可以替换为UNION ALL，但结合需求是找双方都存在的重复值，UNION更合适。
• 确保所有被聚合的字段数据类型一致，否则可能出现隐式转换导致的匹配错误。

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