SQL关联查询中引用别名列失败：如何直接引用结果列？

SQL标准里没有类似this.compile_time这种直接引用同层SELECT子句别名的语法——这是因为SQL的解析执行顺序是先处理FROM/JOIN，再依次处理WHERE、GROUP BY、HAVING，最后才轮到SELECT。这意味着你在SELECT里定义的别名，在同层的其他SELECT表达式中还未被引擎识别，它会直接去查找FROM子句里的表列，所以才会把compile_time当成表B的列。

给你几个不用改别名的解决办法：

1. 用CTE（公共表表达式）拆分查询 #

先把聚合结果单独提取出来，外层再基于这个结果计算总和，就能直接引用别名：

WITH aggregated_data AS (
  select
    A.command_id             as command_id,
    sum(B.compile_time)      as compile_time,
    sum(B.run_time)          as run_time
  from commands as A
  inner join subcommands as B on A.command_id = B.command_id
  group by A.command_id
)
select
  command_id,
  compile_time,
  run_time,
  compile_time + run_time as total_time
from aggregated_data;

2. 重复聚合表达式 #

写法最简单，虽然看起来冗余，但大部分现代SQL引擎会自动优化重复的聚合计算，不会额外消耗性能：

select
  A.command_id             as command_id,
  sum(B.compile_time)      as compile_time,
  sum(B.run_time)          as run_time,
  sum(B.compile_time) + sum(B.run_time)  as total_time
from commands as A
inner join subcommands as B on A.command_id = B.command_id
group by A.command_id;

3. 用LATERAL JOIN/CROSS APPLY（部分数据库支持） #

如果你的数据库是PostgreSQL、SQL Server这类支持LATERAL JOIN或CROSS APPLY的，可以把聚合逻辑拆到JOIN环节，外层直接引用别名：

-- PostgreSQL 写法
select
  A.command_id,
  agg.compile_time,
  agg.run_time,
  agg.compile_time + agg.run_time as total_time
from commands as A
inner join subcommands as B on A.command_id = B.command_id
cross join lateral (
  select
    sum(B.compile_time) as compile_time,
    sum(B.run_time) as run_time
) as agg
group by A.command_id, agg.compile_time, agg.run_time;

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