sql - 如何在 SQL 中查找重复记录

Question

桌子：

ID      status    CREATED TIME
OD01    CLOSED    12-09-2020
OD01    OPEN      10-09-2020
OD02    CLOSED    09-09-2020
OD02    CLOSED    07-09-2020
OD03    OPEN      04-09-2020
OD03    OPEN      06-09-2020

我想要输出，因为 ID 是相同的，两者都应该关闭

标签： sql

Answer 1

select id
from your_table
group by id
having sum(case when status = 'closed' then 1 else 0 end) >= 2

或者

select id
from your_table
where status = 'closed'
group by id
having count(*) >= 2