我有一张这样的table
+------+-------+-----------+-------------+
| code | price | perct_off | date_review |
+------+-------+-----------+-------------+
| 0001 | 1500 | 40 | 2017-12-30 |
| 0001 | 1500 | 40 | 2018-02-15 |
| 0001 | 2000 | 25 | 2018-07-31 |
| 0002 | 3000 | 45 | 2018-03-20 |
| 0002 | 5000 | 20 | 2018-08-01 |
| 0003 | 3000 | 40 | 2018-01-16 |
+------+-------+-----------+-------------+
我只想记录每个代码的最大日期。iutput必须是:
+------+-------+-----------+-------------+
| code | price | perct_off | date_review |
+------+-------+-----------+-------------+
| 0001 | 2000 | 25 | 2018-07-31 |
| 0002 | 5000 | 20 | 2018-08-01 |
| 0003 | 3000 | 40 | 2018-01-16 |
+------+-------+-----------+-------------+
当我尝试时:
SELECT DISTINCT
(code), price, perct_off, MAX(date_review)
FROM `table01`
GROUP BY code
我有这个输出
+-------+--------+------------+-------------------+
| code | price | perct_off | max(date_review) |
+-------+--------+------------+-------------------+
| 0001 | 1500 | 40 | 2018-07-31 |
| 0002 | 3000 | 45 | 2018-08-01 |
| 0003 | 3000 | 40 | 2018-01-16 |
+-------+--------+------------+-------------------+
如何获得正确的输出?
提前谢谢。
2条答案
按热度按时间n7taea2i1#
一种规范的方法是连接到子查询,子查询为每个子查询查找最近的日期
code
:rdrgkggo2#
优化解决方案: