如何使用SQL仅检索与特定情况下日期字段的最后一个值相关的记录？

Question

我不太喜欢数据库，我在试图修改MySql上定义的查询时发现了一些问题。

我有一个查询，它是我的起始点(必须修改的查询)：

SELECT
    MC_PS.id                                            AS price_series_id,
    MC_PS.market_commodity_details_id                   AS market_commodity_details_id,
    MC_PS.price_date                                    AS price_date,
    MC_PS.avg_price                                     AS avg_price,
    CU.ISO_4217_cod                                     AS currency,
    MU.unit_name                                        AS measure_unit,
    MD_CD.market_details_id                             AS market_id,
    MD_CD.commodity_details_id                          AS commodity_id

    FROM Market_Commodity_Price_Series AS MC_PS

    INNER JOIN MeasureUnit AS MU
        ON MC_PS.measure_unit_id = MU.id

    INNER JOIN Currency AS CU
        ON MC_PS.currency_id = CU.id

    INNER JOIN MarketDetails_CommodityDetails AS MD_CD
          ON MC_PS.market_commodity_details_id = MD_CD.id

此查询返回某些市场中某些商品的价格，这是查询输出：

price_series_id      market_commodity_details_id price_date      avg_price            currency measure_unit       market_id            commodity_id        
-----------------------------------------------------------------------------------------------------------------------------------------------------
17                   25                          18/12/2017      778                  RWF      kilogram           4                    6                   
70                   32                          17/12/2017      5                    RWF      kilogram           6                    16                  
71                   32                          18/12/2017      6                    RWF      kilogram           6                    16                  
3                    26                          15/12/2017      12,84                XOF      kilogram           4                    8                   
4                    25                          15/12/2017      18,233               XOF      kilogram           4                    6                   
5                    25                          16/12/2017      22,84                XOF      kilogram           4                    6                   
6                    24                          16/12/2017      18,94                XOF      kilogram           4                    4                   
7                    24                          17/12/2017      28,63                XOF      kilogram           4                    4                   
9                    26                          18/12/2017      48,37                XOF      kilogram           4                    8                   
11                   24                          18/12/2017      33,33                XOF      kilogram           4                    4                   
18                   28                          18/12/2017      13,7                 XOF      kilogram           5                    4                   
19                   28                          17/12/2017      16,2                 XOF      kilogram           5                    4                   
20                   28                          16/12/2017      14,9                 XOF      kilogram           5                    4                   
21                   30                          18/12/2017      21,47                XOF      kilogram           5                    3                   
22                   30                          16/12/2017      22,39                XOF      kilogram           5                    3                   
23                   30                          17/12/2017      23,17                XOF      kilogram           5                    3                   
24                   26                          17/12/2017      16,22                XOF      kilogram           4                    8                   
26                   26                          16/12/2017      19,39                XOF      kilogram           4                    8                   
27                   31                          18/12/2017      13,2                 XOF      kilogram           5                    8                   
28                   31                          17/12/2017      14,4                 XOF      kilogram           5                    8                   
29                   31                          16/12/2017      12,6                 XOF      kilogram           5                    8                   
31                   25                          17/12/2017      17,8                 XOF      kilogram           4                    6                   
32                   25                          17/12/2017      14,8                 XOF      kilogram           4                    6                   
33                   29                          18/12/2017      8,2                  XOF      kilogram           5                    1                   
34                   29                          17/12/2017      9,6                  XOF      kilogram           5                    1                   
35                   29                          16/12/2017      9,3                  XOF      kilogram           5                    1                

如您所见，它包含在特定的一天内出售给市场的商品(commodity_id) (market_id)的价格(price_date)。

我需要修改我的查询，使之只显示在市场上出售的商品的最后价格。

实际上，如果在先前的结果中有多个记录与某一特定商品有关的不同日期出售，则必须返回最后一个日期的单一记录。

因此，修改后的查询的输出必须类似于以下内容：

price_series_id      market_commodity_details_id price_date      avg_price            currency measure_unit       market_id            commodity_id        
-----------------------------------------------------------------------------------------------------------------------------------------------------            
71                   32                          18/12/2017      6                    RWF      kilogram           6                    16                                  
9                    26                          18/12/2017      48,37                XOF      kilogram           4                    8                  
11                   24                          18/12/2017      33,33                XOF      kilogram           4                    4                 
18                   28                          18/12/2017      13,7                 XOF      kilogram           5                    4                                    
21                   30                          18/12/2017      21,47                XOF      kilogram           5                    3                                                    
27                   31                          18/12/2017      13,2                 XOF      kilogram           5                    8                                 
17                   25                          18/12/2017      778                  RWF      kilogram           4                    6                  
33                   29                          18/12/2017      8,2                  XOF      kilogram           5                    1                   

如您所见，每个、market_id、和字段只检索与最后一个price_date值相关的记录。

如何才能正确地实现此更改？

也许我可以在某种程度上使用组(与 with 子句一起使用)？

Answer 1

您的Market_Commodity_Price_Series表有自动递增的id列吗？您的应用程序是否总是使用最近的INSERT值对新行进行price_date处理？

如果是这样，可以向查询中添加WHERE子句，如下所示

WHERE MC_PS.id IN  ( SELECT MAX(id) id 
                       FROM Market_Commodity_Price_Series
                      GROUP BY market_commodity_details_id )

这将过滤除每个项目的最高id值之外的所有其他值。如果假设是正确的，这也将是最新的价值。

如果这些假设不成立，你可以使用类似的过滤器，但它更难。要获取每个项目的最新id值，请执行以下操作：

           SELECT a.id
             FROM Market_Commodity_Price_Series a
             JOIN (
                     SELECT MAX(price_date) price_date,
                            market_commodity_details_id 
                       FROM Market_Commodity_Price_Series 
                      GROUP BY market_commodity_details_id 
                  ) b ON a.market_commodity_details_id = b.market_commodity_details_id 
                     AND a.price_date = b.price_date

此查询查看表并查找每个项的最新日期。然后，它恢复该项的id值。然后，您可以在WHERE id IN ()子句中使用整个子查询，如上面所示。

Answer 2

琼斯的第一个想法不是个好主意。使用子查询获取带有最新日期的记录，并加入到其中。这是未经测试的，但请看最后一个联接。

SELECT
    MC_PS.id                                            AS price_series_id,
    MC_PS.market_commodity_details_id                   AS     market_commodity_details_id,
    MC_PS.price_date                                    AS price_date,
    MC_PS.avg_price                                     AS avg_price,
    CU.ISO_4217_cod                                     AS currency,
    MU.unit_name                                        AS measure_unit,
    MD_CD.market_details_id                             AS market_id,
    MD_CD.commodity_details_id                          AS commodity_id

FROM Market_Commodity_Price_Series AS MC_PS

INNER JOIN MeasureUnit AS MU
    ON MC_PS.measure_unit_id = MU.id

INNER JOIN Currency AS CU
    ON MC_PS.currency_id = CU.id

INNER JOIN MarketDetails_CommodityDetails AS MD_CD
      ON MC_PS.market_commodity_details_id = MD_CD.id

INNER JOIN 
(SELECT id , MAX(price_date) as price_date
    FROM Market_Commodity_Price_Series 
    GROUP BY id) b on MC_PS.id = b.id AND MC_PS.price_date = b.price_date