SQLITE CTE限制（相关子征服）

Question

我很困惑为什么相关的子查询在SQL中效果很好，但是与依赖关系模仿相同逻辑的CTE是错误的，错误地说“ XYZ CTE不存在”。

错误消息：在数据库“ bb_data”上执行SQL查询时的错误：没有这样的表：CTE2

我当然可以将其持续到某种程度上，但是这些是暂时的转换，因此我为什么选择了CTE。

相关的子查询方法可以正常工作

SELECT * ,
arrival_year||"-"||month_transformed||"-"||day_transformed as arrival_date
FROM(

SELECT *,
CASE WHEN length(arrival_month_numeric) =1 THEN "0"||arrival_month_numeric ELSE arrival_month_numeric END as month_transformed,
CASE WHEN length(arrival_date_day_of_month) =1 THEN "0"||arrival_date_day_of_month ELSE arrival_date_day_of_month END as day_transformed
FROM
(
SELECT *,
CASE 
WHEN arrival_month = 'January'  THEN 1
WHEN arrival_month = 'February'  THEN 2
WHEN arrival_month = 'March'  THEN 3
WHEN arrival_month = 'April'  THEN 4
WHEN arrival_month = 'May'  THEN 5
WHEN arrival_month = 'June'  THEN 6
WHEN arrival_month = 'July'  THEN 7
WHEN arrival_month = 'August'  THEN 8
WHEN arrival_month = 'September'  THEN 9
WHEN arrival_month = 'October'  THEN 10
WHEN arrival_month = 'November'  THEN 11
WHEN arrival_month = 'December'  THEN 12
ELSE 0 END as arrival_month_numeric
FROM
booking_table))

CTE方法

WITH CTE1 AS(
SELECT *,
    CASE 
    WHEN arrival_month = 'January'  THEN 1
    WHEN arrival_month = 'February'  THEN 2
    WHEN arrival_month = 'March'  THEN 3
    WHEN arrival_month = 'April'  THEN 4
    WHEN arrival_month = 'May'  THEN 5
    WHEN arrival_month = 'June'  THEN 6
    WHEN arrival_month = 'July'  THEN 7
    WHEN arrival_month = 'August'  THEN 8
    WHEN arrival_month = 'September'  THEN 9
    WHEN arrival_month = 'October'  THEN 10
    WHEN arrival_month = 'November'  THEN 11
    WHEN arrival_month = 'December'  THEN 12
    ELSE 0 END as arrival_month_numeric
    FROM
    booking_table),

    CTE2 AS(
    SELECT *,
        CASE WHEN length(arrival_month_numeric) =1 THEN "0"||arrival_month_numeric ELSE arrival_month_numeric END as month_transformed,
        CASE WHEN length(arrival_date_day_of_month) =1 THEN "0"||arrival_date_day_of_month ELSE arrival_date_day_of_month END as day_transformed
    FROM CTE1)
    
    SELECT * FROM CTE2;