CTE 导致红移误差"column reference is ambiguious for code"

Question

是什么导致了错误"列引用"；col_ 1"；是否模棱两可？

WITH    cte1 AS
(
SELECT  col_1, col_4
from table_1
),
cte2 AS
(
SELECT  two.col_1, two.col_5, three.col_6
from table_2 as two
left join table_3 as three
on two.col_1 = three.col_1 
),
cte3 AS
(
SELECT col_1, col_10
from table_4
)
SELECT  cte1.col_1, cte1.col_4,
cte2.col_1, cte2.col_5, cte2.col_6,
cte3.col_1, cte3.col_10
FROM cte1
left join cte2 on cte1.col_1 = cte2.col_6
left join cte3 on cte1.col_1 = cte3.col_10
; 

我怀疑cte2导致了最终外部查询中不明确的列名问题？

Answer 1

请检查以下查询。尽管它使用的是垃圾数据，但查询中没有不明确的引用。

WITH    cte1 AS
(
SELECT  col_1, col_4
from (select 1 col_1,2 col_4)d
),
cte2 AS
(
SELECT  two.col_1, two.col_5, three.col_6
from (select 1 col_1, 2 col_5) as two
left join (select 1 col_1,1 col_6) as three
on two.col_1 = three.col_1 
),
cte3 AS
(
SELECT col_1, col_10
from (select 1 col_1, 2 col_10) table_4
)

SELECT  cte1.col_1, cte1.col_4,
cte2.col_1, cte2.col_5, cte2.col_6,
cte3.col_1, cte3.col_10
FROM cte1
left join cte2 on cte1.col_1 = cte2.col_6
left join cte3 on cte1.col_1 = cte3.col_10

输出：

col_1
1

把它放在这里，因为它不适合注释部分。

正如我所说，cte1和cte3不是正确的陈述：

WITH    cte1 AS
(
SELECT  col_1, col_4
),

应该是这样的：

WITH    cte1 AS
(
SELECT  col_1, col_4
FROM tableName
),