Postgres:使用ARRAY_AGG和HAVING(而不是WHERE)过滤结果 [英] Postgres: filtering results using ARRAY_AGG and HAVING (instead of WHERE)
问题描述
因此,我的数据库包含项目列表:
So my database contains a listing of items:
items: item.id | item.title
我还有第二个表,该表将多个标签与项目相关联:
And I have a second table that associates a number of tags with the items:
tags: tag.id | item.id
一个项目可能具有与其关联的多个标签(例如5、20和25) ,因此在标签表中每个标签值都有一行。
An item may have several tags (e.g. 5, 20, and 25) associated with it, and thus would have a row for each of those tag-values in the tags table.
我希望能够确定某个特定标签是否与某个商品相关联,而不会丢失所有其他标签数据。例如,
I want to be able to determine if a particular tag is associated with an item, without losing all other tag data. For example,
SELECT items.id, items.title
FROM items
INNER JOIN tags ON (tag.tag_id=items.id)
WHERE tag.id =27
没有之所以起作用,是因为它消除了包含其他标记值的行(如果tag.id为27,则该行不能是特定行上的任何其他值)。
doesn't work because it eliminates the rows containing the other tag values (if tag.id is 27, it cannot be any other value on a particular row).
我想使用ARRAY_AGG将各个标签号汇总到一个数组中,我可以(应该)轻松地测试其成员资格值。
To solve that, I want to use ARRAY_AGG to roll up the individual tag numbers into an array, against which I can (should) easily test values for membership.
SELECT items.id, items.title
FROM items
INNER JOIN tags ON (tag.tag_id=items.id)
GROUP BY items.id
HAVING ANY(ARRAY_AGG(tags.tag_id)=27)
(我看到这篇文章,介绍了如何无法使用ARRAY_AGG选择以AS命名的列)
但是上面的代码在HAVING子句的ANY部分产生语法错误e。
But the above code produces a syntax error on the ANY portion of the HAVING clause.
有什么想法?
推荐答案
原来是Postgres的任何关键字带有侧面,不能对称使用。
Turns out that Postgres's ANY keyword is sided and can't be used symmetrically.
因此,工作代码是:
SELECT items.id, items.title, ARRAY_AGG(tags.tag_id)
FROM items
INNER JOIN tags ON (tag.tag_id=items.id)
GROUP BY items.id
HAVING 27 = ANY(ARRAY_AGG(tags.tag_id))
这篇关于Postgres:使用ARRAY_AGG和HAVING(而不是WHERE)过滤结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!