Hive不支持JOIN条件中使用子查询，如何通过自连接获取表最大日期

报错原因 #

Hive仅允许在WHERE、HAVING子句中使用子查询表达式，你原SQL中将子查询写在了LEFT JOIN的ON关联条件中，触发了语法限制。另外你原SQL中第一个取最大日期的子查询存在笔误，FROM table需改为FROM emp_table。

解决方案 #

将子查询挪到每个关联子查询的WHERE条件中即可，符合Hive语法要求的实现如下：

SELECT
    a.FIRST_NAME, a.LAST_NAME, a.MANAGER, a.MANAGER_CODE, a.EMPLOYEE_CODE, 
    b.FIRST_NAME AS MANAGER_FN, b.LAST_NAME AS MANAGER_LN, 
    c.FIRST_NAME AS DIRECTOR_FN, c.LAST_NAME AS DIRECTOR_LN
FROM
    emp_table a 
LEFT JOIN
    (SELECT DISTINCT manager_code, employee_code, first_name, last_name
     FROM emp_table
     WHERE end_date = (SELECT MAX(end_date) FROM emp_table)) AS b 
ON a.manager_code = b.employee_code
LEFT JOIN 
    (SELECT DISTINCT manager_code,employee_code, first_NAME, LAST_NAME
     FROM emp_table
     WHERE end_date = (SELECT MAX(end_date) FROM emp_table)) AS c 
ON b.manager_code = c.employee_code;

如果想要优化重复计算最大日期的逻辑，也可以用CTE提前计算全局最大日期，减少重复计算：

WITH global_max_date AS (
    SELECT MAX(end_date) AS max_end FROM emp_table
),
latest_emp AS (
    SELECT DISTINCT manager_code, employee_code, first_name, last_name
    FROM emp_table
    CROSS JOIN global_max_date
    WHERE end_date = max_end
)
SELECT
    a.FIRST_NAME, a.LAST_NAME, a.MANAGER, a.MANAGER_CODE, a.EMPLOYEE_CODE, 
    b.FIRST_NAME AS MANAGER_FN, b.LAST_NAME AS MANAGER_LN, 
    c.FIRST_NAME AS DIRECTOR_FN, c.LAST_NAME AS DIRECTOR_LN
FROM emp_table a 
LEFT JOIN latest_emp b ON a.manager_code = b.employee_code
LEFT JOIN latest_emp c ON b.manager_code = c.employee_code;

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