mysql - 从一个表连接到另一个表中选择最近的结果

Question

我有两张 table 。

一个表包含客户数据，例如姓名和电子邮件地址。另一个表包含状态更改的日志。

状态日志表如下所示:

+-------------+------------+------------+
| customer_id | status     | date       |
+-------------+------------+------------+
| 1           | Bought     | 2018-07-01 |     
| 1           | Bought     | 2018-07-02 |     
| 2           | Ongoing    | 2018-07-03 |     
| 3           | Ongoing    | 2018-07-04 |     
| 1           | Not Bought | 2018-07-05 |     
| 4           | Bought     | 2018-07-06 |     
| 4           | Not Bought | 2018-07-07 |     
| 4           | Bought     | 2018-07-08 | *    
| 3           | Cancelled  | 2018-07-09 |     
+-------------+------------+------------+

和客户数据:

+-------------+------------+
| id | name   | email      |
+-------------+------------+
| 1  | Alex   | alex@home  |
| 2  | John   | john@home  |
| 3  | Simon  | si@home    |
| 4  | Philip | phil@home  |
+-------------+------------+

我想选择在 7 月 (07) 有“购买”的客户。但排除最近状态从“购买”其他任何东西变为“购买”的客户。

结果应该只有一个客户 (Philip) - 所有其他客户的状态都已更改为最近已购买以外的状态。

我有以下 SQL:

SELECT 
    a.customer_id
FROM
    statuslog a
WHERE
    DATE(a.`date`) LIKE '2018-07-%'
        AND a.status = 'Bought'
        ORDER BY a.date DESC
        LIMIT 1

但这就是我所能得到的!上面的查询只返回一个结果，但实际上可能有多个结果。

感谢任何帮助!

Answer 1

这是一种使用相关子查询获取最新状态记录的方法:

SELECT sl.customerid
FROM wwym_statuslog sl
WHERE sl.date = (SELECT MAX(sl2.date)
                 FROM wwym_statuslog sl2
                 WHERE sl2.customer_id = sl.customer_id AND
                       sl2.date >= '2018-07-01' AND
                       sl2.date < '2018-08-01'
                ) AND
      sl.status = 'Bought'
ORDER BY sl.date DESC
LIMIT 1;

注意事项:

• 使用有意义的表别名!也就是说，表名的缩写，而不是任意字母，例如 a 和 b。
• 使用正确的日期算法。 LIKE 用于字符串。 MySQL 有很多可用的日期函数。
• 在 MySQL 8+ 中，您将使用 ROW_NUMBER()。