sql - 结合使用 string_agg 并在 postgres 中使用

Question

我的查询:

select
    s.id,
    s.title,
    string_agg(t1.title, ',') as a,
    string_agg(t2.title, ',') as b,
    string_agg(t3.title, ',') as c,
    string_agg(t4.title, ',') as d
from show as s
    left join tag as t1 on t1.show_id = s.id and t1.type='a'
    left join tag as t2 on t2.show_id = s.id and t2.type='b'
    left join tag as t3 on t3.show_id = s.id and t3.type='c'
    left join tag as t4 on t4.show_id = s.id and t4.type='d'
group by
    s.id,
    s.title
having
    t1.title = 'Something';

抛出错误:

ERROR: column "t1.title" must appear in the GROUP BY clause or be used in an aggregate function

根据这个手册页，我不明白的是 string_agg 是聚合函数:

http://www.postgresql.org/docs/9.4/static/functions-aggregate.html

我的数据:

show:
  id: 1
  title: 'test'

tag:
  id: 1
  show_id: 1
  type: 'a'
  title: 'title a'

tag:
  id: 2
  show_id: 1
  type: 'b'
  title: 'title a'

tag:
  id: 3
  show_id: 1
  type: 'c'
  title: 'title c'

tag:
  id: 4
  show_id: 1
  type: 'd'
  title: 'title d'

Answer 1

having 命令正在尝试搜索聚合字段。

要么按 s.title 搜索。或按 t1.title 分组。


有
s.title = '某物';


或者可以改变必须在 concat 字段上搜索。

有 string_agg(t1.title, ',') = '某物';