Postgresql: Query returning incorrect data

问题:

Suppose i have a table empgroupinfo and i want to fetch the employeeid who come in exact this two groupId 500 and 501 (will come dynamically) only, should not come in more or less number of group, where empid != 102 which is in 500 groupid.

I have tried following query:

select empid from empgroupinfo 
where empgroupid in(500,501) and empid != 102
group by empid having count(empid) = 2

But this above query also returns the empId that are in other groups.

I want to fetch the empid for the case when employees are in exactly these two groupids (500 and 501) only and empid != 102.

回答1:

Your WHERE clause selects rows where empgroupid is either 500 or 501, not empids where all the empgroupids form the array [500, 501].

You could use an 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]

Depending on where the [500, 501] array comes from, you may not know whether it itself is sorted or not. In that case a "contains AND is contained by" (operators @> and <@) should work too.

---

#= 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

回答2:

Try:

select empid 
from empgroupinfo 
group by empid 
where empid <> 102
having count(*) = 2 and min(empgroupid) = 500 and max(empgroupid) = 501