mysql - 获取mysql中的多行和单行

Question

我有一张 table

id = 1
name = 'one'
group = 1

id = 2
name = 'two'
group = 1

id = 3
name = 'three'
group = 2

我必须在一个sql查询中获取所有单例的名字，在这种情况下id = 3另一个 sql 所有名称都是多个，在本例中 id=1 和 id=2，因为它们具有相同的组。我想它应该是选择中的选择，但不确定。

提前致谢。

Answer 1

听起来您想使用类似的东西:

select id
from yourtable
group by `group`
having count(`group`) = 1

参见SQL Fiddle with Demo .

然后，如果您想返回所有详细信息，那么您可以将查询扩展为:

select *
from yourtable t1
where id in (select id
            from yourtable t2
            group by `group`
            having count(`group`) = 1)

参见SQL Fiddle with Demo .

如果你想返回具有相同组的所有行，那么你可以使用:

select *
from yourtable t1
where `group` in (select `group`
                  from yourtable t2
                  group by `group`
                  having count(`group`) > 1)

参见SQL Fiddle with Demo

然后，如果您想返回所有内容以及一个标识它是单个还是多个的标志，那么您可以使用与此类似的东西。您会注意到，我添加了一个标志来显示哪些组具有一行，然后哪些组具有多于一行:

select *, 'single' Total
from yourtable t1
where `group` in (select `group`
                  from yourtable t2
                  group by `group`
                  having count(`group`) = 1)
union all
select *, 'multiple' Total
from yourtable t1
where `group` in (select `group`
                  from yourtable t2
                  group by `group`
                  having count(`group`) > 1)

参见SQL Fiddle with Demo