sql - 子查询分解在PostgreSQL中不起作用

当我使用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完全一样，那么为什么前者不能工作呢？
谢谢！

最佳答案

请尝试以下操作：需要在子查询中从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 from t1)

关于sql - 子查询分解在PostgreSQL中不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53001660/