mysql - 每个产品的最后订单时间戳

Question

我想找到为指定的 product_id 支付的上次付款(如果没有，则为 NULL)。下面是我正在使用的表格的表示(简化版)。

+----------+
|Products  |
|----------+
|product_id|
+----------+
+---------------+
|Orders         |
+---------------+
|order_id       |
|order_timestamp|
|order_status   |
+---------------+
+-----------------+
|ProductsOrdersMap|
+-----------------+
|product_id       |
|order_id         |
+-----------------+

在 JOIN、MAX、GROUP BY、LEFT JOIN、多个 INNER JOIN 以获得最大的每组 n 之后，我仍然无法得到正确的结果。大多数时候，具有多个订单的产品会返回多行。到目前为止我得到的最好结果是(我正在搜索特定产品):

product_id  order_id  order_timestamp      order_status
8           NULL      NULL                 NULL
9           NULL      NULL                 NULL
10          NULL      NULL                 NULL
12          NULL      NULL                 NULL
13          NULL      NULL                 NULL
14          11        2013-08-13 07:22:01  finished
15          11        2013-08-13 07:22:01  finished
15          12        2013-08-14 00:00:00  finished
32          11        2013-08-13 07:22:01  finished
83          9         2013-08-13 07:04:02  finished
83          10        2013-08-13 07:11:42  finished

编辑 PP之后。 anwser，我最终得到了以下查询:

SELECT p.product_id, o.order_id, MAX(order_timestamp) AS order_timestamp, order_status
FROM Products p LEFT JOIN (ProductsOrdersMap m, Orders o)
  ON (p.product_id = m.product_id AND m.order_id = o.order_id)
WHERE p.product_id IN (8,9,10,12,13,14,15,32,83)
GROUP BY p.product_id

哪个返回

product_id  order_id  order_timestamp      order_status
8           NULL      NULL                 NULL
9           NULL      NULL                 NULL
10          NULL      NULL                 NULL
12          NULL      NULL                 NULL
13          NULL      NULL                 NULL
14          11        2013-08-13 07:22:01  finished
15          11        2013-08-13 07:22:01  finished
32          11        2013-08-13 07:22:01  finished
83          9         2013-08-13 07:04:02  finished

乍一看似乎是正确的，但只有产品 ID 和时间戳是正确的。对比上面两个查询可以发现，对于产品15和83，order_id是错误的(order_status也可能是错误的)。

Answer 1

此查询应返回指定的结果集(这只是桌面检查，未测试)

返回所有 product_id

SELECT p.product_id
     , m.order_d
     , m.order_timestamp
     , m.order_status
  FROM products p
  LEFT
  JOIN ( SELECT kl.product_id
              , MAX(ko.order_timestamp) AS latest_timestamp
           FROM orderproductsmap kl
           JOIN orders ko
             ON ko.order_id = kl.order_id
          GROUP
             BY kl.product_id
       ) l
    ON l.product_id = p.product_id
  LEFT
  JOIN ( SELECT ml.product_id
              , mo.order_id
              , mo.order_timestamp
              , mo.order_status
           FROM orderproductsmap ml
           JOIN orders mo
             ON mo.order_id = ml.order_id
       ) m
    ON m.product_id = l.product_id
   AND m.order_timestamp = l.latest_timestamp
 GROUP
    BY p.product_id

内联 View “l”为我们获取每个“product_id”的最新“order_timestamp”。这与内联 View “m”相结合，让我们获得具有最新时间戳的订单的整行。

如果碰巧有多个订单具有相同的最新“order_timestamp”(即 order_timestamp 不能保证对于给定的 product_id) 然后最外面的 GROUP BY 确保只返回其中一个订单行。

如果只需要返回特定的 product_id 值，请在最外层查询中添加一个 WHERE 子句。为了提高性能，可以在内联 View 中重复相同的谓词。

为了仅返回特定的 product_id，我们添加了三个 WHERE 子句:

SELECT p.product_id
     , m.order_d
     , m.order_timestamp
     , m.order_status
  FROM products p
  LEFT
  JOIN ( SELECT kl.product_id
              , MAX(ko.order_timestamp) AS latest_timestamp
           FROM orderproductsmap kl
           JOIN orders ko
             ON ko.order_id = kl.order_id
          WHERE kl.product_id IN (8,9,10,12,13,14,15,32,83)
          GROUP
             BY kl.product_id
       ) l
    ON l.product_id = p.product_id
  LEFT
  JOIN ( SELECT ml.product_id
              , mo.order_id
              , mo.order_timestamp
              , mo.order_status
           FROM orderproductsmap ml
           JOIN orders mo
             ON mo.order_id = ml.order_id
          WHERE ml.product_id IN (8,9,10,12,13,14,15,32,83)
       ) m
    ON m.product_id = l.product_id
   AND m.order_timestamp = l.latest_timestamp
 WHERE p.product_id IN (8,9,10,12,13,14,15,32,83)
 GROUP
    BY p.product_id

只需要最外层查询的 WHERE 子句。添加其他两个只是为了通过限制每个派生表的大小来提高性能。