MySQL 8.0同表多次左连接加别名报1054错误如何解决

错误根因 #

你的代码触发1054错误主要有3个问题：

• 表别名语法错误：MySQL中如果别名包含空格，需要用反引号 `` 包裹，不能用单引号''，单引号仅用来标识字符串值，用作表别名本身违反语法规则。
• 别名引用逻辑错误：当你给products表设置别名后，后续关联条件、查询字段中引用该表字段时，必须使用你定义的别名，不能再使用原表名database.products，否则数据库无法识别对应表的字段。
• 统计逻辑错误：多次关联同一张表后，你查询语句中的sum(database.products.weight)无法区分是哪次关联的表数据，统计结果也不符合预期。

另外补充一点：你的统计语句缺少GROUP BY子句，MySQL 8.0默认开启ONLY_FULL_GROUP_BY规则，没有按非聚合字段分组也会触发报错。

修复方案 #

方案1：修复多左连接写法 #

如果要沿用你多次左连接的思路，正确写法如下：

SELECT 
  op.id, 
  op.xxx, 
  sum(`type 0`.weight) as `type 0`, 
  sum(`type 1`.weight) as `type 1`,   
  sum(`type 2`.weight) as `type 2`
FROM database.operative_processes op
LEFT JOIN database.products as `type 0`
  ON op.id = `type 0`.operative_process_that_made_me 
  and `type 0`.product_type = 0
LEFT JOIN database.products as `type 1`
  ON op.id = `type 1`.operative_process_that_made_me 
  and `type 1`.product_type = 1
LEFT JOIN database.products as `type 2`
  ON op.id = `type 2`.operative_process_that_made_me 
  and `type 2`.product_type = 2
GROUP BY op.id, op.xxx

注：这里给operative_processes也加了短别名op，简化代码，避免重复写长表名

方案2：更推荐的条件聚合写法 #

不需要多次关联products表，单次关联后用条件判断分类统计即可，执行效率更高，也不会出现多表关联导致的数据重复问题：

SELECT 
  op.id, 
  op.xxx, 
  sum(if(p.product_type = 0, p.weight, 0)) as `type 0`,
  sum(if(p.product_type = 1, p.weight, 0)) as `type 1`,
  sum(if(p.product_type = 2, p.weight, 0)) as `type 2`
FROM database.operative_processes op
LEFT JOIN database.products p
  ON op.id = p.operative_process_that_made_me
GROUP BY op.id, op.xxx

如果需要兼容更标准的SQL语法，也可以把if换成CASE WHEN语句：

sum(CASE WHEN p.product_type = 0 THEN p.weight ELSE 0 END) as `type 0`

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