mysql - SQL查询类似于distinct

Question

给定一个表 (c1, c2)，其值为 (1,1) (1,4)、(4,1) (2,2)，什么查询允许我只输出 c2 =1 的结果并且表中只存在一个条目？在这种情况下，它只会是 (4,1)，因为有两个条目 c1 =1。干杯。

Answer 1

嗯..有很多方法。这是一个:

select c1, c2
from my_table t1
where c2 = 1
  and 1 = (select count(*) from my_table t2 where t2.c1 = t1.c1)

其他一些方法:

select t1.c1, t1.c2
from my_table t1
join my_table t2 using (c1)
where t1.c2 = 1
group by t1.c1, t1.c2
having count(*) = 1;

select c1, c2
from my_table t1
where c2 = 1
  and not exists (
      select *
      from from my_table t2
      where t2.c1 =  t1.c1
        and t2.c2 <> t1.c2
  );

select t1.c1, t1.c2
from my_table t1
left join my_table t2
  on  t2.c1 =  t1.c1
  and t2.c2 <> t1.c2
where t1.c2 = 1
  and t2.c1 is null;

最后两个查询要求表中没有重复项。