SQL多列最大值查询在HUE中报错，请求排查解决

关键错误点 #

• 字段列表末尾遗漏逗号：原查询中'' as change_type后未加逗号，直接衔接计算date_last_mod的子查询，导致SQL解析失败，这是触发报错的核心原因。
• JOIN语句缺失关键字：关联al_maiden_name_spk表时，left join people.al_maiden_name_spk h后未写on关键字，正确写法需补充on a.id = h.id。
• 中文/英文单引号混用：多处使用中文单引号（如’Z’、‘0047’），需统一替换为英文单引号'，否则会被解析为非法字符。
• 连接条件笔误：关联al_occupation_spk表时，连接条件写成a.id = a.id，会导致笛卡尔积，应修正为a.id = g.id。
• Hive语法兼容性限制：第一种用VALUES构造临时表取最大值的写法，部分Hive版本不支持，建议改用兼容性更好的UNION ALL方式。

修正后的完整SQL #

Select 
    a.id as id, 
    first_name as name, 
    surname, 
    h.maiden_name, 
    c.salutation, 
    c.gender, 
    coalesce(d.marital_status, 'Z') as marital_status, 
    coalesce(g.occupation, '0047') as occupation, 
    coalesce(f.kind_of_work, '9') as kind_of_work,
    '' as change_type,
    (SELECT MAX(date_last_mod) FROM (
        SELECT a.date_last_mod
        UNION ALL
        SELECT c.date_last_mod
        UNION ALL
        SELECT d.date_last_mod
        UNION ALL
        SELECT f.date_last_mod
        UNION ALL
        SELECT g.date_last_mod
        UNION ALL
        SELECT h.date_last_mod 
    ) AS value(date_last_mod)) AS date_last_mod
from people.al_names_spk a
inner join people.al_salutation_spk c
    on a.id = c.id
left join people.al_marital_status_spk d
    on a.id = d.id
left join people.al_kind_of_work_spk f
    on a.id = f.id
inner join people.al_occupation_spk g
    on a.id = g.id
left join people.al_maiden_name_spk h
    on a.id = h.id
LEFT SEMI JOIN people_st.stg_person_hist_spk b 
    ON (a.id = b.id AND b.data_date_part = '2023-06-01')

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