根据 Postgres 中的条件筛选这些行中的重复行

Question

给定一个表

CREATE TABLE data(
 irs_number VARCHAR (50),
 mop_up INTEGER,
 ou VARCHAR (50)
);

我如何返回所有匹配的记录...

• 在另一行中至少有一个相同的irs_number值，并且
• 具有相同irs_number的mop_up中至少有一个必须设置为 1 和
• ou 值不相同，即仅返回与具有相同irs_number的行不匹配的值。

...以便返回所有< code>irs_numbers(不仅仅是条件为真的一个-参见下面的例子)。

我尝试了此操作，但查询无法在合理时间内完成：

SELECT irs_number, mop_up, ou
FROM data outer_data
WHERE (SELECT count(*)
FROM data inner_data
WHERE inner_data.irs_number = outer_data.irs_number
AND inner_data.mop_up = 1 OR outer_data.mop_up = 1
AND inner_data.ou <> outer_data.ou
);

以及此处描述的重复计数的变化：如何在PostgreSQL中查找重复记录-它们总是只返回重复记录，而不是应用的正确过滤器。

编辑:

示例数据：

INSERT INTO data VALUES 
('0001', 1, 'abc'),
('0001', 0, 'abc'),
('0001', 0, 'cde'),
('0001', 0, 'abc'),
('0002', 1, 'abc'),
('0002', 0, 'abc'),
('0003', 0, 'abc'),
('0003', 0, 'xyz')
;

SQLFiddle： http://sqlfiddle.com/#!17/be28f

理想情况下，查询应返回：

irs_number  mop_up  ou
-----------------------
0001        1       abc
0001        0       abc
0001        0       cde
0001        0       abc

(顺序不重要)这意味着它应该返回符合上述条件的< code>irs_number的所有行。

Answer 1

我认为这种连接可以:

SELECT * FROM data 
WHERE irs_number in (
  SELECT irs_number
  FROM data d
  WHERE EXISTS (SELECT 1
    FROM data 
    WHERE irs_number = d.irs_number
    AND (mop_up = 1 OR d.mop_up = 1)
    AND ou <> d.ou
  )
)

查看演示

Answer 2

您应该能够使用一个简单的存在子句来执行此操作：

SELECT irs_number, mop_up, ou
FROM data d
WHERE EXISTS (SELECT 1
              FROM data d2
              WHERE d2.irs_number = d.irs_number AND
                    d2.mop_up = 1 AND
                    d2.ou <> d.ou
             );

编辑：

上面曲解了问题。它假设< code>mop_up = 1需要在不同的< code>ou上。当我阅读这个问题时，这是模糊的，但似乎不是你想要的。因此，有两个< code >存在地址:

SELECT irs_number, mop_up, ou
FROM data d
WHERE EXISTS (SELECT 1
              FROM data d2
              WHERE d2.irs_number = d.irs_number AND
                    d2.mop_up = 1
             ) AND
     EXISTS (SELECT 1
              FROM data d2
              WHERE d2.irs_number = d.irs_number AND
                    d2.ou <> d.ou
             );

这是一个数据库

这两种解决方案都能够利用上的索引（irs_number，mop_up，ou）。