mysql - 获取每组中的最后一项 MySql

Question

在第一个表中我有订单。在第二个中 - 发送到这些订单的消息。我需要为每个活跃 = 1 的订单获取最后一条消息的日期和状态(如果消息尚未发送，则为 NULL)。通过复合键“order_id + Offer”连接的表。

“订单”表:

+----------+----------+--------+----------+
| order_id | offer    | active | timezone |
+----------+----------+--------+----------+
|        6 | kopiya   |      1 |        0 |
|        6 | kopiya-3 |      1 |        0 |
|       10 | kopiya   |      1 |      180 |
|       23 | kopiya-2 |      1 |        0 |
|       27 | kopiya-2 |      0 |        0 |
+----------+----------+--------+----------+

“短信”表:

+------+----------+----------+------+--------+---------------------+
| key_ | order_id | offer    | type | status | date                |
+------+----------+----------+------+--------+---------------------+
| 1    |        6 | kopiya   | text |      1 | 2016-06-20 00:00:00 |
| 2    |        6 | kopiya-3 | text |      0 | 2016-06-21 00:00:00 |
| 3    |       10 | kopiya   | text |      0 | 2016-06-27 00:00:00 |
| 4    |       27 | kopiya-2 | text |      1 | 2016-06-21 00:00:00 |
| 6    |        6 | kopiya-3 | text |      1 | 2016-06-23 00:00:00 |
+------+----------+----------+------+--------+---------------------+

结果将是:

+----------+----------+---------------------+--------+
| order_id | offer    | last_date           | status |
+----------+----------+---------------------+--------+
|        6 | kopiya   | 2016-06-20 00:00:00 |      1 |
|        6 | kopiya-3 | 2016-06-23 00:00:00 |      1 |
|       10 | kopiya   | 2016-06-27 00:00:00 |      0 |
|       23 | kopiya-2 | NULL                |   NULL |
+----------+----------+---------------------+--------+

此查询无法正确运行:

SELECT o.order_id, o.offer, max(date) as last_date, status
FROM orders AS o
LEFT JOIN sms AS s
ON o.order_id=s.order_id AND o.offer=s.offer
WHERE `active` = 1
GROUP BY o.order_id, o.offer;

它显示:

+----------+----------+---------------------+--------+
| order_id | offer    | last_date           | status |
+----------+----------+---------------------+--------+
|        6 | kopiya   | 2016-06-20 00:00:00 |      1 |
|        6 | kopiya-3 | 2016-06-23 00:00:00 |      0 |
|       10 | kopiya   | 2016-06-27 00:00:00 |      0 |
|       23 | kopiya-2 | NULL                |   NULL |
+----------+----------+---------------------+--------+

对于键“6 kopiya-3”，它返回 status=0 但预期为 1，因为它从第一行而不是具有最大日期的行获取此值。我该如何解决这个问题？

Answer 1

这与 Unix One 的答案相同，只是格式不同？如果是这样，那么他们比我先一步 - 但请注意，这假设在 (order_id,offer) 上进行 PK，并返回与请求的结果集略有不同的结果集。

SELECT o.order_id
     , o.offer
     , s.date last_date
     , s.status
  FROM orders o 
  LEFT
  JOIN 
     ( SELECT x.* 
         FROM sms x
         JOIN 
            ( SELECT order_id
                   , offer
                   , MAX(date) date 
                FROM sms 
               GROUP 
                  BY order_id
                   , offer
            ) y 
           ON y.order_id = x.order_id 
          AND y.offer = x.offer
          AND y.date = x.date
      ) s
     ON s.order_id = o.order_id 
    AND s.offer = o.offer 
  WHERE o.active = 1;