Your WHERE
子句选择行,其中empgroupid
是 500 或 501,不是empid
那里所有的empgroupid
s 构成数组[500, 501]
.
你可以使用ARRAY_AGG
in the HAVING
clause:
SELECT empid
FROM empgroupinfo
GROUP BY empid
-- ORDER BY clause here is important, as array equality checks elements position by position, not just 'same elements as'
HAVING ARRAY_AGG(DISTINCT empgroupid ORDER BY empgroupid) = ARRAY[500, 501]
取决于在哪里[500, 501]
数组从何而来,你可能不知道它本身是否已排序。在这种情况下,“包含 AND 被包含”(运算符@>
and <@
)也应该有效。
#= CREATE TABLE empgroupinfo (empid int, empgroupid int);
CREATE TABLE
Time: 10,765 ms
#= INSERT INTO empgroupinfo VALUES (1, 500), (1, 501), (2, 500), (2, 501), (2, 502);
INSERT 0 5
Time: 1,451 ms
#= SELECT empid
FROM empgroupinfo
GROUP BY empid
HAVING ARRAY_AGG(empgroupid ORDER BY empgroupid) = ARRAY[500, 501];
┌───────┐
│ empid │
├───────┤
│ 1 │
└───────┘
(1 row)
Time: 0,468 ms