我有一个crosstab()
查询,如下所示:
SELECT *
FROM crosstab(
'SELECT row_name, extra1, extra2..., another_table.category, value
FROM table t
JOIN another_table ON t.field_id = another_table.field_id
WHERE t.field = certain_value AND t.extra1 = val1
ORDER BY row_name ASC',
'SELECT category_name FROM category_name WHERE field = certain_value'
) AS ct(row_name text, extra1 text, extra2 text, ...)
简化的示例,实际查询非常复杂并且包含重要信息。上面的查询在使用table.extra1 = val1
过滤后返回N个结果行。
当我按如下方式更改查询时:
SELECT *
FROM crosstab(
'SELECT row_name, extra1, extra2..., another_table.category, value
FROM table t
JOIN another_table ON t.field_id = another_table.field_id
WHERE t.field = certain_value AND t.extra1IN (val1, ...)-->more values
ORDER BY row_name ASC',
'SELECT category_name FROM category_name WHERE field = certain_value'
) AS ct(row_name text, extra1 text, extra2 text, ...)
WHERE extra1 = val1; -->condition on the result
添加了更多可能的值table.extra1 IN (val1, ...)
和最终条件WHERE extra1 = val1
。现在我得到的行比原来的少。更糟糕的是,如果我向IN (val1, ...)
添加更多的值,我得到的行更少。为什么?
extra1, extra2, ...
是交叉表术语中的"额外列"。
tablefunc 模块的手册解释了这些规则:
它还可能有一个或多个"额外"列。
row_name
列必须 成为第一。类别和value
列必须是最后两列, 按此顺序。处理row_name
和category
之间的任何列 作为"额外"。对于具有相同row_name
值的所有行,"额外"列应相同。
再往下:
输出
row_name
列以及任何"额外"列将从组的第一行复制。
我大胆强调关键部分。
您仅按row_name
排序:
ORDER BY row_name ASC
在第一个示例中,使用以下命令进行筛选并不重要:
WHERE ... t.extra1 = 'val1' -- single quotes by me
无论如何,所有输入行都extra1 = 'val1'
。但在第二个示例中,使用以下内容进行筛选很重要:
WHERE ... t.extra1 IN('val1', ...) --> More values
现在,对于额外的列extra1
,违反了上述第一个粗体要求。虽然第一个输入查询的排序顺序是不确定的,但"extra"列extra1
的结果值是任意选择的。extra1
的可能值越多,最终具有"val1"的行就越少:这就是您观察到的。
您仍然可以使其正常工作:要报告至少具有其中一项的每个row_name
的extra1 = 'val1'
,请将ORDER BY
更改为:
ORDER BY row_name, (extra1 <> 'val1')
在顶部对"val1"进行排序。该boolean
表达式的说明(带有指向更多内容的链接):
- PostgreSQL:分组然后过滤表,条件为不存在
其他"额外"列仍然是任意选择的,而排序顺序不是确定性的。
交叉表基础知识:
- PostgreSQL 交叉表查询
- 具有多个"行名"列的 Postgresql 交叉表查询