mysql - 每个日期加载前5条记录

我有一张桌子，上面有不同用户的明智测验分数。我想为每个日期加载前5名得分手。

表样本创建语句：

CREATE TABLE `subscriber_score` (
  `msisdn` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',

  `total_questions_sent` int(11) NOT NULL DEFAULT '0',

  `total_correct_answers` int(11) NOT NULL DEFAULT '0',
  `total_wrong_answers` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`msisdn`,`date`),
  KEY `fk_subscriber_score_subscriber1` (`msisdn`),
  CONSTRAINT `fk_subscriber_score_subscriber1` FOREIGN KEY (`msisdn`) REFERENCES `subscriber` (`msisdn`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我尝试过的查询：

SELECT subscriber.msisdn AS msisdn,subscriber.name AS name,subscriber.gender AS gender,tmp2.score AS score,tmp2.date AS winning_date
FROM subscriber,
    (SELECT msisdn,tmp.date,tmp.score
     FROM subscriber_score,
          (SELECT date,MAX(score) AS score
           FROM subscriber_score
           WHERE date > '2014-10-10' AND date < '2014-11-10' GROUP BY date)
          tmp
     WHERE subscriber_score.date=tmp.date AND subscriber_score.score=tmp.score)
tmp2
WHERE subscriber.msisdn=tmp2.msisdn ORDER BY winning_date

实际输出：每个日期仅显示一个得分最高的球员。

希望输出每个日期的前5（或说10）条记录。

最佳答案

我认为您可以使用变量为每行分配一个行号，然后过滤每个日期的前5个。

SELECT  s.name AS name,
        s.gender AS gender,
        s.msisdn,
        ss.date,
        ss.score
FROM    (   SELECT  ss.msisdn,
                    ss.score,
                    @r:= CASE WHEN ss.Date = @d THEN @r + 1 ELSE 1 END AS RowNum,
                    @d:= ss.date AS winning_date
            FROM    subscriber_score AS ss
                    CROSS JOIN (SELECT @d:= '', @r:= 0) AS v
            WHERE   ss.date > '2014-10-10'
            AND     ss.date < '2014-11-10'
            ORDER  BY ss.Date, ss.Score DESC
        ) AS ss
        INNER JOIN Subscriber AS s
            ON s.msisdn = ss.msisdn
WHERE   ss.RowNum <= 5;

Example on SQL Fiddle