MySQL限制内组? [英] mysql limit inside group?

问题描述

我想限制组内记录的大小，这是我的试用版，如何正确执行?

I want to limit the size of records inside a group, and here is my trial, how to do it right?

mysql> select * from accounts limit 5 group by type;

错误1064(42000):您的SQL语法有错误；查看手册 对应于您的MySQL服务器版本 在第1行的按类型分组"附近使用正确的语法

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by type' at line 1

推荐答案

使用编号的行我有些运气:

I've had some luck with using numbered rows:

set @type = '';
set @num = 0;

select
  items.*,
  @num := if(@type = item_type, @num + 1, 1) as dummy_1,
  @type := item_type as dummy_2,
  @num as row_number
from items
group by
  item_type,
  row_number
having row_number < 3;

这将为您每个item_type提供2个结果. (一个陷阱:请确保您重新运行前两个set语句，否则您的行号将越来越稳定，并且row_number < 3限制将不起作用.

This will give you 2 results per item_type. (One gotcha: make sure you re-run the first two set statements otherwise your row numbers will steadily get higher and higher and the row_number < 3 restriction won't work.

我从

I pieced this together from a couple of posts which have been linked in other answers on SO.

这篇关于MySQL限制内组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！