子查询分解在PostgreSQL中无法工作

Question

当我使用postgresql时，我遇到了一个问题。这是代码：

WITH t1 AS (
SELECT
TABLE_NAME AS TABELA
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMN_NAME LIKE 'nu_cns' )
SELECT 
TABLE_NAME,
COLUMN_NAME
FROM 
INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME IN t1

如果我运行它，我会得到一个错误：

ERROR:  syntax error at or near "t1"
LINE 14:  TABLE_NAME IN t1
^
SQL state: 42601
Character: 211

但奇怪的是，如果我用整个子查询替换t1，它可以成功运行，如下所示：

WITH t1 AS (
SELECT
TABLE_NAME AS TABELA
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMN_NAME LIKE 'nu_cns' )
SELECT 
TABLE_NAME,
COLUMN_NAME
FROM 
INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME IN (
SELECT
TABLE_NAME AS TABELA
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMN_NAME LIKE 'nu_cns' )

你看，实际上子查询和t1完全一样。那么，为什么前者不能工作呢？谢谢

Answer 1

请尝试以下操作：您需要在子查询中从t1中选择table_name

WITH t1 AS 
( 
SELECT TABLE_NAME AS TABELA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'nu_cns' 
)
SELECT 
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN (select table_name from t1)

Answer 2

WITH t1 AS 
( 
SELECT TABLE_NAME AS TABELA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'nu_cns' 
)
SELECT 
t2.TABLE_NAME,
t2.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS t2 join t1 on t1.TABELA = t2.table_name