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

Z时代
2024-01-10
分类：问答

我不是很喜欢数据库，我发现一些问题试图修改在MySql上定义的查询。如何使用SQL在此特定情况下只检索与日期字段的最后一个值相关的记录？

我有这个疑问这是我的出发Poing的（有需要修改的查询）：

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）。

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

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

所以我修改查询的输出必须是这样的一个：

正如你可以看到每个market_id和commodity_id字段只检索有关最后记录price_date value

如何正确实施此更改？

也许我可以用GROUP BY用HAVING子句以某种方式？

回答：

你的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()子句中使用整个子查询，如上所示。

回答：

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

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