如何在MySQL中限制同一值仅显示2行

Question

如何显示所有数据，但限制相同的名称值仅显示2行例如，这是我的表'test'：

id  name
1   title-1
2   title-1
3   title-2
4   title-1
5   title-3
6   title-2
7   title-1
8   title-2

我想让输出获得所有数据，但只限制2行来显示它是否是相同的name值，如下所示：

id  name
4   title-1
7   title-1
6   title-2
8   title-2
5   title-3

Answer 1

您可以在MySQL 8+中使用row_number()：

select id, name
from (select t.*,
             row_number() over (partition by name order by id desc) as seqnum
      from t
     ) t
where sequm <= 2;

在早期版本中，您可以使用相关子查询：

select t.*
from t
where t.id >= coalesce( (select t2.id
                         from t t2
                         where t2.name = t.name
                         order by t2.id desc
                         limit 1, 1
                        ), t.id);

Here是一个db<>fiddle，它表明这会产生预期的结果。

Answer 2

尝尝这个。一定要管用。

SELECT name FROM test group by test having count(*) <= 2

Answer 3

尝尝这个。

SELECT name from (
SELECT name, row_number() over (partition by name) as RN from TEST group by test having count(Name) >= 1 ) a where RN <= 2