仅在Postgres中选择那些两次的记录

Question

select distinct(msg_id),sub_id from programs where sub_id IN
(
select sub_id from programs group by sub_id having count(sub_id) = 2 limit 5
)

sub_id表示订户

内部查询将返回程序表中完全是2次的订户，并且主要查询将使那些具有不同msg_id的订户。

此结果将生成

msg_id  sub_id
------|--------|
 112  |  313
 111  |  222
 113  |  313
 115  |  112
 116  |  112
 117  |  101
 118  |  115
 119  |  115
 110  |  222

我希望它应该是

    msg_id  sub_id
    ------|--------|
     112  |  313
     111  |  222
     113  |  313
     115  |  112
     116  |  112
     118  |  115
     119  |  115
     110  |  222

117 |101（此结果不应该在输出中，因为它仅一次）

我只需要两次的记录。

Answer 1

我不确定，但是您只是错过了列表中的第二个字段吗？

select distinct msg_id, sub_id, <presumably other fields>
from programs
where (sub_id, msg_id) IN
(
  select sub_id, msg_id
  from programs
  group by sub_id, msg_id
  having count(sub_id) = 2
)

如果是这样，您也可以使用窗口功能来执行此操作：

with cte as (
  select
    msg_id, sub_id, <presumably other fields>,
    count (*) over (partition by msg_id, sub_id) as cnt
  from programs
)
select distinct
  msg_id, sub_id, <presumably other fields>
from cte
where cnt = 2

Answer 2

尝试此

SELECT msg_id, MAX(sub_id)
FROM programs 
GROUP BY msg_id
HAVING COUNT(sub_id) = 2  -- COUNT(sub_id) > 1 if you want all those that repeat more than once
ORDER BY msg_id