有没有一种方法可以限制MySQL中每组结果行的结果? [英] Is there a way to LIMIT results per group of result rows in MySQL?

问题描述

我有以下查询:

SELECT title, karma, DATE(date_uploaded) as d
FROM image
ORDER BY d DESC, karma DESC

这将给我一张图像记录列表，首先按最新日期排序，然后按大多数业障排序.

This will give me a list of image records, first sorted by newest day, and then by most karma.

缺少一件事:我只希望每天获得业力最高的x张图像.因此，举例来说，每天我只想要10个最大的业力图像.我当然可以每天运行多个查询，然后合并结果.

There is just one thing missing: I want to only get the x images with the highest karma per day. So for example, per day I only want the 10 most karma images. I could of course run multiple queries, one per day, and then combine the results.

我想知道是否有一种更聪明的方法仍然可以很好地执行.我想我正在寻找的是每组结果使用LIMIT x，y的方法吗?

I was wondering if there is a smarter way that still performs well. I guess what I am looking for is a way to use LIMIT x,y per group of results?

推荐答案

您可以通过使用变量模拟ROW_NUMBER来实现.

You can do it by emulating ROW_NUMBER using variables.

SELECT d, title, karma
FROM (
    SELECT
        title,
        karma,
        DATE(date_uploaded) AS d,
        @rn := CASE WHEN @prev = UNIX_TIMESTAMP(DATE(date_uploaded))
                    THEN @rn + 1
                    ELSE 1
               END AS rn,
        @prev := UNIX_TIMESTAMP(DATE(date_uploaded))
    FROM image, (SELECT @prev := 0, @rn := 0) AS vars
    ORDER BY date_uploaded, karma DESC
) T1
WHERE rn <= 3
ORDER BY d, karma DESC

结果:

'2010-04-26', 'Title9', 9
'2010-04-27', 'Title5', 8
'2010-04-27', 'Title6', 7
'2010-04-27', 'Title7', 6
'2010-04-28', 'Title4', 4
'2010-04-28', 'Title3', 3
'2010-04-28', 'Title2', 2

Quassnoi 上有一篇很好的文章，详细介绍了该技术:

Quassnoi has a good article about this which explains the technique in more details: Emulating ROW_NUMBER() in MySQL - Row sampling.

测试数据:

CREATE TABLE image (title NVARCHAR(100) NOT NULL, karma INT NOT NULL, date_uploaded DATE NOT NULL);
INSERT INTO image (title, karma, date_uploaded) VALUES
('Title1', 1, '2010-04-28'),
('Title2', 2, '2010-04-28'),
('Title3', 3, '2010-04-28'),
('Title4', 4, '2010-04-28'),
('Title5', 8, '2010-04-27'),
('Title6', 7, '2010-04-27'),
('Title7', 6, '2010-04-27'),
('Title8', 5, '2010-04-27'),
('Title9', 9, '2010-04-26');

这篇关于有没有一种方法可以限制MySQL中每组结果行的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！