只获取每个代码最近日期的记录

ctzwtxfj 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(310)

我有一张这样的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        |
+-------+--------+------------+-------------------+

如何获得正确的输出？
提前谢谢。

mysql Date records

来源：https://stackoverflow.com/questions/51699792/get-only-the-records-with-most-recent-date-to-every-code

2条答案

按热度按时间

n7taea2i1#

一种规范的方法是连接到子查询，子查询为每个子查询查找最近的日期 code :

SELECT t1.*
FROM table01 t1
INNER JOIN
(
    SELECT code, MAX(date_review) AS max_date_review
    FROM table01
    GROUP BY code
) t2
    ON t1.code = t2.code AND t1.date_review = t2.max_date_review;

赞(0）回复(0）举报 2021-06-20

rdrgkggo2#

优化解决方案：

SELECT
  t1.code,
  t1.price,
  t1.perct_off,
  t1.date_review
FROM t t1
  LEFT JOIN t t2 ON
    t1.code = t2.code 
    AND t1.date_review < t2.date_review
WHERE t2.date_review IS NULL;

赞(0）回复(0）举报 2021-06-20

我来回答

只获取每个代码最近日期的记录

2条答案

相关问题

热门标签

最新问答