PostgreSQL：查询返回不正确的数据 [英] Postgresql: Query returning incorrect data

问题描述

假设我有一个表 empgroupinfo ，我想获取这两个groupId 500和501 （只会动态出现），不应加入或多或少的组数，其中 empid！= 102 在500个groupid中。

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.

我尝试了以下查询：

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

但是此查询还返回其他组中的empId。

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

我想获取 empid 仅当雇员恰好在这两个组ID（500和501）中且 empid！= 102 时。

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

推荐答案

您的 WHERE 子句选择 empgroupid 为500或501的行，而不是 empid s，其中所有 empgroupid s组成数组 [500，501] 。

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

您可以在 HAVING 子句中使用 ARRAY_AGG ：

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]

根据 [500，501] 数组的来源，您可能不知道它是否本身是否已排序。在这种情况下，包含AND包含（运算符 @> 和< @ ）也应该起作用

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

这篇关于PostgreSQL：查询返回不正确的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！