MySQL 在使用 GROUP BY WEEK 时按 SQL 中的另一列按最大(列)选择行？

Question

对于每个星期，我想为每个不同的代表值选择权重最高的行

-------------------------------------
| id | reps | weight |     date     |
| 1  |  1   |  15    |   2015-06-10 |
| 2  |  2   |  29    |   2015-06-12 |
| 3  |  1   |  30    |   2015-06-13 |
| 4  |  4   |  11    |   2015-06-14 |
| 5  |  1   |  15    |   2015-06-29 |
| 6  |  1   |  9     |   2015-06-30 |

我希望它回来

-------------------------------------
| id | reps | weight |     date     |
| 2  |  2   |  29    |   2015-06-12 |
| 3  |  1   |  30    |   2015-06-13 |
| 4  |  4   |  11    |   2015-06-14 |
| 5  |  1   |  15    |   2015-06-29 |

我试过了

SELECT MAX(weight) as weight, reps, date, id FROM log_items
GROUP BY reps, WEEK(date)

但它似乎返回完全随机的结果，我也尝试过使用子查询但它们也没有用(我猜我做错了)

Answer 1

尝试:

SELECT * 
FROM log_items
WHERE (reps, WEEK(date), weight ) in (
      SELECT reps, WEEK(date) , MAX(weight)
      FROM log_items
      GROUP BY reps, WEEK(date)
 )

演示:http://sqlfiddle.com/#!9/4bf84/9