mysql - SQL 过滤掉具有多个值的 id

Question

我有以下格式的 MySQL 表

id     c1     c2
---------------------
id1    c1v1   c2v1  -> keep id1 even it has duplicates
id1    c1v1   c2v1
id2    c1v2   c2v2  -> filter out id2 because it has various c1, c2
id2    c1v2   c2v3
id3    c1v3   c2v4
....

预期输出:

id     c1     c2
---------------------
id1    c1v1   c2v1
id3    c1v3   c2v4
....

我只想保留 id 仅具有唯一 c1、c2 值的记录。我确实有一个解决方案，但它需要全表扫描两次，包括一个联接，这是非常低效的，我想知道是否有更好的方法来做到这一点。

select 
    distinct id, c1, c2
from table 
inner join 
    (select 
        id, 
        count(distinct c1, c2) as counts
    from table 
    group by id
    having counts = 1) tmp
on table.id = tmp.id

Answer 1

仅使用group by怎么样:

select id, min(c1) as c1, min(c2) as c2
from t
group by id
having min(c1) = max(c1) and min(c2) = max(c2)

如果你想要所有原始记录，那么使用不存在

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.id = t.id and
                        (t2.c1 <> t.c1 or t2.c2 <> t.c2)
                 );