如何一次选择一个类别就可以获取最新的2个项目(使用mysql) [英] How to get the latest 2 items per category in one select (with mysql)

问题描述

我的数据如下:

id|category|insertdate|title....
--------------------------------
1|1|123|test 1
2|1|124|test 2
3|1|125|test 3
4|2|102|test 4
5|2|103|test 5
6|2|104|test 6

我要完成的工作是每个类别获取最新的2个条目(按insertdate DESC的顺序排列)，因此结果应为:

id|....
----
3|....
2|....
6|....
5|....

使用group by获取最新消息很容易，但是如何在不启动多个查询的情况下获取最新2?

感谢您的帮助；-)

解决方案

在这里，您可以成为好友！

SET @counter = 0;
SET @category = '';

SELECT
    *
FROM
(
    SELECT
        @counter := IF(data.category = @category, @counter+1, 0) AS counter,
        @category := data.category,
        data.*
    FROM
    (
        SELECT
            *
        FROM test
        ORDER BY category, date DESC
    ) data
) data
HAVING counter < 2

My data looks like the following:

id|category|insertdate|title....
--------------------------------
1|1|123|test 1
2|1|124|test 2
3|1|125|test 3
4|2|102|test 4
5|2|103|test 5
6|2|104|test 6

What I try to accomplish is get the latest 2 entries per category (as in order by insertdate DESC), so the result should be:

id|....
----
3|....
2|....
6|....
5|....

Getting the latest by using group by is easy, but how do I get the latest 2 without launching multiple queries?

Thanks for any help ;-)
S.

解决方案

Here you go buddy!

SET @counter = 0;
SET @category = '';

SELECT
    *
FROM
(
    SELECT
        @counter := IF(data.category = @category, @counter+1, 0) AS counter,
        @category := data.category,
        data.*
    FROM
    (
        SELECT
            *
        FROM test
        ORDER BY category, date DESC
    ) data
) data
HAVING counter < 2

这篇关于如何一次选择一个类别就可以获取最新的2个项目(使用mysql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！