SQL子查询替代左连接中Risks表报错，求正确写法

需按产品组统计各年度总成本，但仅计算每个风险的最新版本金额，排除历史数据。现有Risks、Risk_Groups、Costs三张表，原查询可统计年度总成本，但无法筛选最新版本风险。尝试用获取最新版本的子查询替换原查询中的Risks表时，出现ON附近语法错误且别名r未被识别的问题，求修正后的SQL语句。

表结构 #

• Risks表：Risks_RiskID、Risks_Change_Number、Risks_RiskGroup_ID、<其他产品属性>
• Risk_Groups表：RiskGroup_ID、<其他组属性>
• Costs表：Costs_RiskID、Costs_Change_Number、Costs_FY、Cost_Risk_Amount

原查询代码 #

SELECT Risk_Groups.RiskGroup_ID, Costs.Costs_FY, Sum(Costs.Costs_Risk_Amount) AS TotalAmount
FROM (Risk_Groups LEFT JOIN 
Risks 
ON Risk_Groups.RiskGroup_ID = Risks.Risks_RiskGroup_ID) 
LEFT JOIN Costs ON (Risks.Risks_Change_Number = Costs.Costs_Change_Number) AND (Risks.Risks_RiskID = Costs.Costs_RiskID)
where Risk_Groups.RiskGroup_ID = 'My test group'
GROUP BY Risk_Groups.RiskGroup_ID, Costs.Costs_FY

获取最新版本的子查询 #

(select r.Risks_RiskID, r.Risks_change_number, r.Risks_RiskGroup_ID
    from risks r
        inner join (
           select risks_Riskid, max(risks_change_number) as maxversion 
           from risks maxr group by risks_riskid) maxr 
           on r.Risks_RiskID = maxr.Risks_RiskID and r.Risks_Change_Number = maxr.MaxVersion)

报错原因分析 #

替换后的查询存在两个核心问题：

1. 子查询未指定别名，导致后续关联时无法引用其字段；r是子查询内部的局部别名，外部查询无法直接访问
2. 子查询与Risk_Groups关联时，引用了外部不可见的r别名，引发ON子句语法错误

修正后的SQL语句 #

SELECT Risk_Groups.RiskGroup_ID, Costs.Costs_FY, Sum(Costs.Costs_Risk_Amount) AS TotalAmount
FROM (Risk_Groups 
LEFT JOIN 
-- 给子查询指定别名latest_risks，便于外部引用
(select r.Risks_RiskID, r.Risks_change_number, r.Risks_RiskGroup_ID
    from risks r
        inner join (
           select risks_Riskid, max(risks_change_number) as maxversion 
           from risks group by risks_riskid) maxr 
           on r.Risks_RiskID = maxr.Risks_RiskID and r.Risks_Change_Number = maxr.MaxVersion) AS latest_risks
-- 使用子查询别名关联Risk_Groups
ON Risk_Groups.RiskGroup_ID = latest_risks.Risks_RiskGroup_ID) 
-- 关联Costs时同样使用子查询别名
LEFT JOIN Costs ON (latest_risks.Risks_Change_Number = Costs.Costs_Change_Number) AND (latest_risks.Risks_RiskID = Costs.Costs_RiskID)
WHERE Risk_Groups.RiskGroup_ID = 'My test group'
GROUP BY Risk_Groups.RiskGroup_ID, Costs.Costs_FY

关键修正说明 #

• 为获取最新风险版本的子查询添加别名latest_risks，确保外部查询可以正常引用其字段
• 将所有关联条件中的r替换为子查询别名latest_risks，避免引用内部局部别名导致的识别错误
• 移除了报错查询中多余的Risks_center_rank字段（表结构未提及，若实际业务需要可自行添加）

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