MYSQL - 找到每天评分最高的 3 条记录

Question

鉴于此 MYSQL 图像表，我想要每天评分最高的记录。

id    created_at             rateScore
--------------------------------------
1     2014-06-16 12:30:00    100
2     2014-06-16 13:30:00    200
3     2014-06-16 14:30:00    50    
4     2014-06-16 15:30:00    20  
5     2014-06-16 16:30:00    0  
6     2014-06-17 14:30:00    100  
7     2014-06-17 14:31:00    90  
8     2014-06-18 14:31:00    100 

预期结果:每天只显示 3 张评分最高的图片。

id    created_at             rateScore
--------------------------------------
1     2014-06-16 12:30:00    100
2     2014-06-16 13:30:00    200
3     2014-06-16 14:30:00    50        
6     2014-06-17 14:30:00    100  
7     2014-06-17 14:31:00    90  
8     2014-06-18 14:31:00    100 

我尝试了很多子选择等，但我开始感到沮丧。这个问题看起来很简单，但对我来说很难解决。也许有人提示如何解决这个问题。

提前致谢，祝您有愉快的一天

Answer 1

您可以在 MySQL user defined variables 的帮助下获得结果

SELECT 
t.id,
t.created_at,
t.rateScore
FROM 
(
    SELECT 
    *,
    IF(@sameDate = DATE(created_at), @rn := @rn + 1,
       IF(@sameDate := DATE(created_at), @rn := 1, @rn := 1)
    ) AS row_number
    FROM your_table 
    CROSS JOIN (SELECT @sameDate := '0000-00-00', @rn := 1) var
    ORDER BY created_at,rateScore DESC 
) AS t
WHERE t.row_number <= 3
ORDER BY t.created_at, t.rateScore DESC

为了得到X每天的条目只需更改此行 ( WHERE t.row_number <= 3 )，如下所示:

WHERE t.row_number <= X

注意:我猜是 (created_at,rateScore) 上的综合索引会提高性能。

如果您没有，则可以创建和衡量性能变化:

ALTER TABLE `your_table` ADD INDEX `idx_table_creaed_at_rateScore` (
    `created_at`,
    `rateScore`
);