mysql - MYSQL中每个ID根据日期选择最新版本

Question

我有一个像这样的表:

+------------+-------------------+--------------+------------+
| listing_id | transaction_title | image_thumb  | sale_date  |
+------------+-------------------+--------------+------------+
| 226835186  | Title Version 11  | Img Style 11 | 2016-02-08 |
+------------+-------------------+--------------+------------+
| 226835186  | Title Version 11  | Img Style 12 | 2016-02-16 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 21  | Img Style 21 | 2016-02-15 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 22  | Img Style 22 | 2016-02-17 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 23  | Img Style 21 | 2016-02-16 |
+------------+-------------------+--------------+------------+
| 230105831  | Title Version 31  | Img Style 31 | 2016-02-12 |
+------------+-------------------+--------------+------------+
| 230105831  | Title Version 32  | Img Style 31 | 2016-02-06 |
+------------+-------------------+--------------+------------+

我正在尝试使用最新使用的 transaction_title 和 image_thumb 版本来查询不同的 listing_id。对于上表查询输出将是:

+------------+-------------------+--------------+------------+
| listing_id | transaction_title | image_thumb  | sale_date  |
+------------+-------------------+--------------+------------+
| 226835186  | Title Version 11  | Img Style 12 | 2016-02-16 |
+------------+-------------------+--------------+------------+
| 228703248  | Title Version 22  | Img Style 22 | 2016-02-17 |
+------------+-------------------+--------------+------------+
| 230105831  | Title Version 31  | Img Style 31 | 2016-02-12 |
+------------+-------------------+--------------+------------+

我尝试了 select unique、num_rows 和 max() 的不同组合，但无法获得所需的结果。

我最近尝试过:

SELECT
    listing_id,transaction_title,image_thumb,sale_date
FROM (
    SELECT * FROM sales
    ORDER BY sale_date DESC
) AS transaction_title
GROUP BY listing_id

请帮忙!

Answer 1

您可以使用 row_number 基本方法，首先对 Listing_id 和 sale_date 进行降序排序，然后选择 row_number 为 1 的行。这将为您提供所需的数据集。这种方法的查询模板如下:

SELECT  INVW.listing_id, INVW.transaction_title, INVW.image_thumb, INVW.sale_date 
FROM (
    SELECT  listing_id, transaction_title, image_thumb, sale_date
        ,@rank := if(@listing_id = listing_id or listing_id is null, @rank + 1, 1) as row_number 
        ,@listing_id := listing_id as dummy
    FROM <###REPLACE_ME_WITH_TABLE_NAME###>, (select @rank := 0,@listing_id := '') rank
    ORDER BY listing_id,sale_date DESC 
) INVW where INVW.row_number = 1;