事实上,您根本不需要查询字符串参数的外部
我的PostgreSQL服务器上有两个模式,我正在尝试创建一个数据透视表:
WITH cte AS (
SELECT l.fcid, t.fixture_code, COUNT(f.fixture_type_id) AS count
FROM aim.locations as l
JOIN aim.fixture_instances as f
ON l.id = f.location_id
JOIN aim.fixture_types as t
ON f.fixture_type_id = t.id
GROUP BY l.fcid, t.fixture_code
)
SELECT *
FROM lab.CROSSTAB('SELECT fcid, fixture_code, SUM(count) FROM cte group by 1,2 order by 1,2',
'Select DISTINCT fixture_code from cte order by 1')
AS CT (FCID integer, "fx1" bigint, "fx2" bigint)
ORDER BY fcid;
然而,我收到一个错误:
ERROR: relation "cte" does not exist LINE 1: Select DISTINCT fixture_code from cte order by 1
当我只有一个模式时,查询就工作了
我使用CTE,以便可以使用查询创建视图
这里怎么了?
SELECT
。添加的sum(count)
在该星座中没有任何变化。CTE中的查询已经将最终结果传递给crosstab()
。完全消除CTE,从而也修复了错误的直接原因:
SELECT *
FROM lab.crosstab(
$$
SELECT l.fcid, t.fixture_code, count(*) AS cnt
FROM aim.locations l
JOIN aim.fixture_instances f ON l.id = f.location_id
JOIN aim.fixture_types t ON f.fixture_type_id = t.id
GROUP BY 1,2
ORDER BY 1,2
$$
, $$VALUES ('fx1'), ('fx2')$$
) AS ct(fcid int, fx1 bigint, fx2 bigint);
其他一些东西:
当您必须在列定义列表中拼写出相应的列名时,运行SELECT DISTINCT
毫无意义。将其替换为普通的VALUES
表达式。
根据查询的定义,f.fixture_type_id
是NOT NULL
,所以使用更快的等效count(*)
。
此处的基础知识:
- PostgreSQL交叉表查询