mysql - 使用 INNER JOIN 查询仅选择唯一行

Question

这个问题已经有答案了:

Mysql select last row for each group (3 个回答)

已关闭 5 年前。

抱歉，不太确定如何表达。

有两个数据库表，store_locations和 store_diagnostics .

store_locations保存有关商店的静态信息，例如 zip_code , StoreId以及其他一些事情。而store_diagnostics保存每个商店位置的诊断数据和统计信息，例如 total_sold , total_revenue等

新条目被输入到 store_diagnostics每小时左右通过一个 cron 作业检查总销售额等。对我来说唯一的问题是，每次 cron 作业运行时都会创建一个新行，而不是更新以前存在的行，以便本周的销售额可以与上周等进行比较。

我当前的查询有效，但它会获取同一商店的多个条目。理想情况下，我只想获取最新的 store_diagnostics给定 StoreId 的行并通过 created 订购它们字段降序，以便我获得该特定商店最后创建的行。

这是我现有的查询:

SELECT
  locations.* ,
  location_stats.total_sold ,
  location_stats.total_moved_to ,
  location_stats.total_moved_from ,
  location_stats.total_revenue ,
  location_stats.created
FROM store_locations locations
  INNER JOIN
    store_diagnostics location_stats
  ON
    locations.StoreId = location_stats.StoreId
ORDER BY location_stats.created DESC , location_stats.total_sold DESC

如您所见，我只想为每个 StoreId 获取一个结果我希望在按 created 对行进行排序后获取它DESC 中的专栏订单。

location_stats.total_sold是我想对所有结果使用的排序方法，以便所有独特的商店都按其销售的商品总数进行排序。

如果不清楚，我很抱歉，我希望我已经充分解释了。

Answer 1

我添加了一个 where 子句，该子句根据创建的最大值进行过滤，如果按降序排序，该最大值将是顶部值。总体排序顺序已更改为问题描述中所示的total_sold。请注意，如果您的 cron 作业生成新行的速度快于创建的精度，您将得到重复项。我认为不会。如果您在 store_diagnostics 中有一个身份作为 PK，我会取其最大值而不是创建它。

选项 1:使用相关子查询

SELECT
  locations.* ,
  location_stats.total_sold ,
  location_stats.total_moved_to ,
  location_stats.total_moved_from ,
  location_stats.total_revenue ,
  location_stats.created
FROM store_locations locations
  INNER JOIN store_diagnostics location_stats ON locations.StoreId = location_stats.StoreId
WHERE location_stats.created = (select max(created) from store_diagnostics where stored_id = locations.stored_id)
ORDER BY location_stats.total_sold DESC

选项 2:加入群组

SELECT
  locations.* ,
  location_stats.total_sold ,
  location_stats.total_moved_to ,
  location_stats.total_moved_from ,
  location_stats.total_revenue ,
  location_stats.created
FROM store_locations locations
  INNER JOIN store_diagnostics location_stats ON locations.StoreId = location_stats.StoreId
  INNER JOIN (select store_id, max(created) created from stored_diagnostics group by store_id) mx on locations.store_id = mx.stored_id and location_stats.created = mx.created
ORDER BY location_stats.total_sold DESC