PostgreSQL 交叉表

Question

谁能帮助我并告诉我为什么这个交叉表查询不起作用？我目前收到的错误是“[42703] 错误:列“1”不存在”。非常感谢任何帮助!

select * from
  crosstab('select
source
,status
,sum(CASE
  when cast(rejected as int) = 1 
      then (cast(articles as int) * cast(rejected as int))
  else cast(articles as int)
  end) as Units

from summary.itemdata
where

 first_owner = "1"
and created >= "2017-01-01"
and ownerships = "1"
group BY
  source
,status
order by source, status')
as final_result(source text, 
               discarded int, 
               "pick-job" int, 
               missing int, 
               "weigh-end" int, 
               received int, 
               shipped int, 
               "cat-end" int, 
               packed int, 
               picking int)

Answer 1

由于您将查询作为字符串参数传递给 crosstab，所以双引号之间的 1 被解释为列名，而您的意图是将其作为字符串值where 条件。
这是使用单引号完成的，但需要转义到字符串中。转义单引号的一种方法是在它前面使用另一个单引号。

改变这部分

where

 first_owner = "1"
and created >= "2017-01-01"
and ownerships = "1"

用转义的单引号替换双引号

where

 first_owner = ''1''
and created >= ''2017-01-01''
and ownerships = ''1''

看起来一样，但意思完全不同。