Mysql，选择最大值和匹配值

Question

我有一个包含日期和值的表格。我想要做的是获取值的 MAX() 值以及与该值对应的日期。即

+------------+-------+
| pdate      | score |
+------------+-------+
| 2012-05-01 |    80 |
| 2012-05-02 |    50 |
| 2012-05-03 |    52 |
| 2012-04-02 |   100 |
| 2012-05-02 |    10 | 
+------------+-------+

我想要的输出是 2012-04-02 - 100

这是我的查询:

SELECT pdate,MAX(Score) as maxscore FROM tblpulse 
 WHERE DID = '171488' && pdate BETWEEN '2012-05-02' 
   AND '2012-06-26' and pdate ORDER BY pdate ASC

Answer 1

创建一个子查询并按您的 MAX 排序...

SELECT * FROM (
SELECT pdate,MAX(Score) as maxscore FROM tblpulse 
WHERE DID = '171488' && pdate BETWEEN '2012-05-02' 
AND '2012-06-26' and pdate ORDER BY pdate ASC)
ORDER BY maxscore DESC LIMIT 1