MYSQL:为不同的列值获取相同数量的行 [英] MYSQL: Fetch same number of rows for different column values

问题描述

好吧，假设我有这张桌子

Ok so let's say I have this table

表名:示例"

id | type  | value
-----------------
1  | color | blue
2  | plants| apple
3  | color | red
4  | color | yellow
5  | plants| grapes
6  | things| cellphone

如何进行单个查询(或可能带有子查询)以返回每个 type 的 2 行(如果数据较少，则更少).

How can I make a single query (or maybe with subqueries) to return 2 (or less if there are less data) rows for EACH type.

我可以针对单个查询执行此操作:

I can do this for individual queries:

select * from `example` where `type` = 'color' limit 2

我不能只使用 where in 因为这不会返回相等数量的结果.

I couldn't just use where in coz this will NOT return an EQUAL number of results.

预期结果:(每种类型 2 个或更少)

Expected results: (2 or less for each type)

id | type  | value
-----------------
1  | color | blue
2  | plants| apple
3  | color | red
5  | plants| grapes
6  | things| cellphone

顺便说一句，我正在使用 laravel eloquent，但是原始的也可以.

I'm using laravel eloquent for this btw, but a raw one is ok.

推荐答案

使用 row_number():

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

这篇关于MYSQL:为不同的列值获取相同数量的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！