php - 每一个月选出一位得分最高的获奖者

我需要选择得分最高的人作为每月获奖者。我也想展示以前的获奖者。我当前的查询仅选择前一个月的获奖者，但是如何选择所有前一个月的获奖者？

我的查询：

    function month_winner_now()
    {
        $query = $this->db->query("SELECT winner.id,winner.score,winner.user_id,winner.date, user.id,user.username,user.email,user_profile.user_image,user_profile.hometown,user_profile.country FROM `winner` LEFT JOIN `user` ON user.id = winner.user_id LEFT JOIN `user_profile` ON user_profile.user_id = winner.user_id WHERE  MONTH(CURDATE())= MONTH(winner.date) ORDER BY winner.score DESC
LIMIT 1");


            return $query->result_array();
    }


My current output :

"monthly winners":[
    {
        "id":"5",
        "score":"1256",
        "user_id":"5",
        "date":"2014-03-05",
        "username":"",
        "email":"",
        "user_image":"",
        "hometown":"",
        "country":""
    }

但是我需要像这样的输出

  "monthly winners":[
        {
            "id":"4",
            "score":"233",
            "user_id":"4",
            "date":"2014-03-02",
            "username":"Mahmudul Hasan Swapon",
            "email":"",
            "user_image":"",
            "hometown":"",
            "country":""
        },
        {
            "id":"7",
            "score":"123",
            "user_id":"7",
            "date":"2014-03-04",
            "username":"Prosanto Biswas",
            "email":"",
            "user_image":"",
            "hometown":"",
            "country":""
        }
    ],

每月获奖者json数组显示以前所有月份的获奖者，但每个月应该有一位获奖者。

DB表看起来像

   id   |   name   |  userid  |  score  |      date      |
   ------------------------------------------------------------
    1   |   john   |    1     |   44    |  2013-03-2
    2   |   mary   |    2     |   59    |  2013-03-5
    3   |   john   |    12    |   38    |  2013-03-8
    4   |   elvis  |    3     |   19    |  2013-02-10
    5   |   john   |    11    |   1002  |  2013-01-11
    6   |   johnah |    10    |   200   |  2013-01-11

最佳答案

我重新创建了sql查询并添加了另一个字段"month_of_year"，现在我认为它将根据您的要求对您有所帮助

SELECT
   winner.id,winner.score,winner.user_id,winner.date,
   user.id,user.username,user.email,user_profile.user_image,
   user_profile.hometown,user_profile.country,
   date_format( winner.date, '%Y-%m' ) AS month_of_year
FROM
   `winner`
   LEFT JOIN `user`
     ON user.id = winner.user_id
   LEFT JOIN `user_profile`
     ON user_profile.user_id = winner.user_id
GROUP BY month_of_year
ORDER BY winner.score DESC