SQL添加别名后仍提示FROM子查询必须有别名错误排查

编写SQL对bookings.flights表的航班数据按时段分类时，即使尝试为子查询添加别名，执行时仍然触发错误。
原SQL语句如下：

SELECT * FROM 

(select 
flight_id,
flight_no,
scheduled_departure,
scheduled_arrival,
cast (scheduled_departure as text) TIMINGS
from bookings.flights), as TABLE1

case when TIMINGS
between 02:00:00+05:30 to 06:00:00+05:30 then 'Early morning flights'
when scheduled_departure to scheduled_arrival 

between 06:00:00+05:30  to 11:00:00+05:30 then 'Morning flights'
when scheduled_departure to scheduled_arrival 

between 11:00:00+05:30 to 16:00:00+05:30 then 'Noon flights'
when scheduled_departure to scheduled_arrival 

between 16:00:00+05:30 to 19:00:00+05:30 then 'Evening flights'
when scheduled_departure to scheduled_arrival 

between 19:00:00+05:30 to 23:00:00+05:30 then 'Night flights'
when scheduled_departure to scheduled_arrival 

between 23:00:00+05:30 to 02:00:00+05;30  then 'Night flights'
END as TIMINGS

执行返回的错误信息：

ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM (select
^
HINT: For example, FROM (SELECT ...) [AS] foo.
SQL state: 42601
Character: 15

该报错不是单一问题导致，原SQL存在多处语法和逻辑错误：

• 子查询别名语法错误：子查询闭合括号后多余的逗号导致别名TABLE1和子查询被分隔，数据库解析FROM子句时没有识别到子查询的别名，直接触发提示中的报错。
• CASE语句位置错误：CASE是SELECT子句的组成部分，用于生成计算列，原写法直接将CASE放在FROM子句之后，完全不符合SQL语法结构。
• BETWEEN语法使用错误：标准BETWEEN语法为值 BETWEEN 左边界 AND 右边界，原代码中错误使用to替代AND，甚至将字段名scheduled_departure to scheduled_arrival写在WHEN和BETWEEN之间，语法完全不合法。
• 字面量格式错误：时间类型的字面量没有用单引号包裹，最后一个边界值还将时区偏移的冒号错写为分号（05;30），属于无效值。
• 逻辑错误：将完整的带时区时间戳转为文本无法准确匹配时段，跨天的23:00-次日02:00区间用BETWEEN写反向区间会直接返回空结果，同时子查询和外层计算列都命名为TIMINGS会触发字段重名冲突。

不需要嵌套多余子查询，直接在主查询中完成时段计算即可，修正后的SQL如下：

SELECT
  flight_id,
  flight_no,
  scheduled_departure,
  scheduled_arrival,
  CASE
    WHEN (scheduled_departure AT TIME ZONE 'Asia/Kolkata')::TIME BETWEEN '02:00:00' AND '06:00:00' THEN 'Early morning flights'
    WHEN (scheduled_departure AT TIME ZONE 'Asia/Kolkata')::TIME BETWEEN '06:00:00' AND '11:00:00' THEN 'Morning flights'
    WHEN (scheduled_departure AT TIME ZONE 'Asia/Kolkata')::TIME BETWEEN '11:00:00' AND '16:00:00' THEN 'Noon flights'
    WHEN (scheduled_departure AT TIME ZONE 'Asia/Kolkata')::TIME BETWEEN '16:00:00' AND '19:00:00' THEN 'Evening flights'
    WHEN (scheduled_departure AT TIME ZONE 'Asia/Kolkata')::TIME >= '19:00:00'
      OR (scheduled_departure AT TIME ZONE 'Asia/Kolkata')::TIME < '02:00:00' THEN 'Night flights'
  END AS flight_time_slot
FROM bookings.flights;

修正点说明：

• 移除了多余的嵌套子查询和错误的逗号，直接从源表查询数据，从根源避免子查询别名识别错误。
• 统一使用标准BETWEEN ... AND ...语法，删除错误的to关键字，所有时间字面量用单引号包裹，修正了分号写错的无效值问题。
• 先将航班计划起飞时间转换为东5.5区（印度标准时间）时区，再提取时间部分做区间匹配，避免完整时间戳转文本导致的匹配失败。
• 跨天的夜间时段拆分为两个判断条件，覆盖19:00到次日02:00的全部范围，避免反向BETWEEN区间的逻辑错误。
• 时段计算列重命名为flight_time_slot，避免字段重名冲突。

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