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 中选择 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)
推荐阅读
- microsoft-edge - Azure AD B2C 未在 Edge 中正确注销
- c# - 将数字舍入到下一个最高的位置
- laravel-5 - 在 Laravel 5.6 中集成 PayUMoney
- python - 有没有办法在不先计算一阶导数的情况下计算数据的二阶导数?
- salesforce-marketing-cloud - 联系 Builder Marketing Cloud Connect 问题
- bash - 重击 | 将 .txt 导入 .txt 文件,逐行使用“读取时”
- laravel - 为什么 php PUT 路由在 apache 中有效,但在 IIS 中无效?
- html - 包装时如何防止弹性项目拉伸
- ruby-on-rails - NameError: undefined local variable or method `app' for - Rails 5.1.6 升级
- reactjs - 动态获取变量 JSX