具有日期过滤器的每个用户的最高分数 - Maximum Score of Each User with date filter

Maximum Score of Each User with date filter

例如，我有下面的表结构

 userid  |  score  |      datestamp      |
-----------------------------------------------------
   1     |   44    |  2013-06-01 14:25:55 
   1     |   50    |  2013-06-02 14:25:55 
   2     |   59    |  2011-06-04 09:25:51 
   1     |   60    |  2011-06-10 21:25:15 
   3     |   19    |  2011-06-02 07:25:18
   2     |   70    |  2011-06-05 09:25:51 
   2     |   12    |  2011-06-08 09:25:51 
   3     |   60    |  2011-06-05 07:25:18
   4     |   30    |  2011-06-10 07:25:18
   3     |   20    |  2011-06-08 07:25:18
   4     |   15    |  2011-06-09 07:25:18
   1     |   50    |  2013-06-11 14:25:55 
   2     |   100   |  2013-06-15 14:25:55 
   3     |   160   |  2013-06-12 14:25:55 
   4     |   75    |  2013-06-16 14:25:55

我需要这样一个查询，我会给出日期限制，它会给我在此期间每个用户的最高分数。例如，如果我给出开始日期2013-06-01和结束日期2011-06-10，结果将是：

 userid  |  score  |      datestamp      |
----------------------------------------------------- 
  1     |   60    |  2011-06-10 21:25:15 
  2     |   70    |  2011-06-05 09:25:51 
  3     |   60    |  2011-06-05 07:25:18
  4     |   30    |  2011-06-10 07:25:18

此外，如果我也给用户Id（使用IN（1,2,3,4））

，那就太好了

如果你不需要相应的日期，只需要最大分数，这将适用于

SELECT
    userid, MAX(score)
FROM
    tablename
WHERE
    datestamp BETWEEN '2011-06-10 00:00:00' AND '2013-06-01 23:59:59'
    -- AND userid IN (1, 2, 3, 4)
GROUP BY
    userid

不过，获取与之相关的日期将更加复杂。

尝试此查询，

SELECT userid, MAX(score) as score FROM table 
WHERE datestamp BETWEEN '2011-06-10 00:00:00' AND '2013-06-01 23:59:59' AND userid IN(1,2,3,4)
GROUP BY userid