php mysql 鸽

mysql 分几组 每组查几条

表结构 users

type	title	created_at
a	花开花下1	2022-02-14 00:26:01
a	花开花落2	2022-02-14 00:28:01
a	开心麻花3	2022-02-14 00:30:01
a	开心啦哈4	2022-02-14 00:32:01
a	开心摸哈5	2022-02-14 00:34:01
b	他的故事6	2022-02-14 00:36:01
b	大海卡的7	2022-02-14 00:36:01
b	大海故事8	2022-02-14 00:34:01
b	大海声音9	2022-02-14 00:34:01
b	大海哈哈10	2022-02-14 00:34:01
c	故事开始11	2022-02-14 00:39:01

试例

根据 type 每类根据时间排序取 3条

select number, type, title, created_at
from (select @type := '', @number := 0) as rcin
         join (select @number := IF(type != @type, 1, @number + 1) as number,
                      @type := type,
                      title,
                      type,
                      created_at
               from users
               order by type, created_at desc) as nrcircirci
where number <= 3
order by created_at desc, number

参考资料

• https://www.cnblogs.com/net-god/p/11139250.html
• https://stackoverflow.com/questions/18533080/laravel-eloquent-groupby-and-also-return-count-of-each-group
• https://stackoverflow.com/questions/29648770/laravel-5-how-do-i-retrieve-10-records-of-each-kind-of-data-in-a-column
• https://stackoverflow.com/a/36490929/6686061