表如下所示:
id ... item_id likes
1 1 200
1 300
1 400
1 4
... 2 54
2 31
3 22
如何 for each 最受欢迎的商品获得3件或更少的商品
[
item_id: 1,
likes: 200,
],
[
item_id: 1,
likes: 300,
],
[
item_id: 1,
likes: 400,
],
[
item_id: 2,
likes: 54,
],
[
item_id: 2,
likes: 31,
],
[
item_id: 3,
likes: 22,
],
由于MySQL 8.0，您可以使用窗口函数row_number
with n as (
select
id, item_id, likes, row_number() over (partition by item_id) rn
from tbl
) select
item_id, likes
from n
where rn < 4;
<?php
$query = "with n as (
select
id, item_id, likes, row_number() over (partition by item_id) rn
from tbl
) select item_id, likes from n where rn < 4;";
// Select using Laravel
$rows = $db::select($db::raw($query));
var_export($rows);