SQL执行LEFT JOIN报错无'client'字段 需向结果追加统计列

错误根因 #

报错left join statement has no column 'client'的核心原因是：你在JOIN关联条件中使用了nested.client作为关联键，但nested子查询的返回字段仅包含route和freq，没有定义client字段，数据库无法识别该列。

除此之外你的SQL还存在4处语法问题：

1. 最外层SELECT中sum(nested.freq) as freq后多了多余的逗号
2. 第一个nested子查询中，uh.route和COUNT(uh.route)之间缺少逗号分隔
3. 最外层、raw2子查询都使用了聚合函数sum()但没有对应添加GROUP BY子句，不符合SQL聚合语法规范
4. 最外层SELECT未声明返回total_trans字段，无法得到你预期的三列结果

修复步骤 #

• 给nested子查询添加固定值字段'abc' as client，匹配JOIN关联条件的字段要求
• 删除多余的逗号，补全缺失的逗号
• 给所有使用聚合函数的查询添加对应的GROUP BY子句
• 最外层SELECT补全total_trans返回字段

修正后的完整SQL #

SELECT
  'abc' as client,
  sum(nested.freq) as freq,
  raw2.total_trans
FROM
  (
    SELECT
      'abc' as client,
      uh.route,
      COUNT(uh.route) as freq
    FROM
      employee AS up,
      hist AS uh
    where
      up.id = uh.eID
      AND uh.PrhEEBankRoute = '123'
    GROUP BY
      uh.route
  ) AS nested
  LEFT JOIN (
    SELECT
      'abc' as client,
      sum(raw.freq) as total_trans
    FROM
      (
        SELECT
          uh.route,
          COUNT(uh.route) as freq
        FROM
        employee AS up,
        hist AS uh
        where
          up.id = uh.eID
        GROUP BY
          uh.route
      ) AS raw
    GROUP BY client
  ) raw2 ON raw2.client = nested.client
GROUP BY client, raw2.total_trans;

效果验证 #

修正后执行SQL即可得到你期望的三列结果，freq为过滤后的路由访问总和，total_trans为全量路由访问总和。

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