Postgres将所有数组值匹配到具有条件的条件的同一列---已更新 [英] Postgres Match All array values to same column with and condition ---updated

问题描述

我有带有以下列的表table_a

I have table table_a with following columns

id  event_id
1   101
1   102
1   103
2   105
2   103
2   106

I并使用与条件类似的IN查询来搜索（101、103）和条件类似的条件

I and to search (101, 103) with and conditions similar to IN query with OR condition

例如，id 1匹配101和103 event_id；

For example id 1 match both for 101 and 103 event_id;

为此，我编写了以下查询，但它不起作用。

For this I write following query but it not working.

select * from table_a where event_id = ALL(ARRAY[101,103]);

已更新----------------- ----------
还有另一个问题

UPDATED--------------------------- And I have another problem

说id是另一个表的外部变量event_categories

let say id is foreign of another table event_categories having relation like this.

id      parent_id
101     null
102     101
103     101
104     null
105     104

所以我想基于AND与父项从table_a中获取记录事件类别，或在该父项的子事件类别中。

so I want to fetch records from table_a based on AND with parent event category, OR within sub event categories of that parent.

例如101，104，且AND
102，103在OR之中，即101

For example 101, 104 with AND 102, 103 within OR of 101

推荐答案

您需要为单个ID汇总所有event_id：

You need to aggregate all event_ids for a single ID:

select id
from table_a
group by id
having array_agg(event_id) @> array[101,103];

@> 是包含运算符因此它会检查所有event_id的数组是否包含具有这两个ID的数组。

The @> is the contains operator so it checks if the array of all event_ids contains the array with those two IDs.

这将返回至少具有 这两个ID的任何id事件101和103（这是您在问题中所要求的）。

That will return any id that has at least the two event 101 and 103 (which is what you asked for in your question).

如果您要查找具有恰好这两个event_id（示例数据中不包含）的ID，则可以使用：

If you would like to find those IDs that have exactly those two event_ids (which your sample data does not contain) you could use:

select id
from table_a
group by id
having array_agg(distinct event_id order by event_id) = array[101,103];

请注意，数组中元素的顺序对于 = 运算符（与包含 @> 运算符不同）

Note that the order of the elements in the array matters for the = operator (unlike the "contains" @> operator)

这篇关于Postgres将所有数组值匹配到具有条件的条件的同一列---已更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！