MAX 上的 MySQL JOIN

Question

我有两个我的 SQL 表，我正在尝试加入，它们被简化为:

+----------------------------+
| customers                  |
+-------------+-------+------+
| customer_id | first | last |
+-------------+-------+------+
| 0           | John  | Doe  |
+-------------+-------+------+
| 1           | Jane  | Doe  |
+-------------+-------+------+

+-------------------------------------------------------------------+
| contact_log                                                       |
+----------------+-------------+--------------+---------------------+
| contact_log_id | customer_id | contact_type | date_time           |
+----------------+-------------+--------------+---------------------+
| 0              | 0           | email        | 2016-05-17 03:21:45 |
+----------------+-------------+--------------+---------------------+
| 1              | 0           | phone        | 2016-05-17 16:11:35 |
+----------------+-------------+--------------+---------------------+
| ...            | ...         | ...          |                     |
+----------------+-------------+--------------+---------------------+

我需要一个查询来选择客户，以及他们最近的联系时间和类型。我试过这个查询:

SELECT
    `customers`.`customer_id`,
    `customers`.`first`,
    `customers.last`,
    `contact_log`.`contact_type`,
    MAX(`contact_log`.`date_time`)
FROM
    `customers`
JOIN
    `contact_log`
ON
    `customers`.`customer_id` = `contact_log`.`customer_id`

这通常会错误地对 date_time 进行排序。研究该问题后，某些 MySQL 版本中存在一个错误，其中 MAX 和 MIN 无法与 DATETIME 一起正常工作。所以解决方法是

MAX(CAST(`contact_log`.`date_time` AS CHAR))

然后我得到客户行，以及最新的 date_time。但是，contact_type 与时间不匹配。在示例数据中，我的结果如下所示:

+-------------+-------+------+--------------+---------------------+
| customer_id | first | last | contact_type | date_time           |
+-------------+-------+------+--------------+---------------------+
| 0           | John  | Doe  | email        | 2016-05-17 16:11:35 |
+-------------+-------+------+--------------+---------------------+

contact_type 与 contact_log 表中的 date_time 不匹配。我怀疑这与 SELECT/JOIN 发生的顺序以及过滤时间有关。我必须小心子查询(以避免 n+1)，因为这些是非常大的表，并且可能会从两个表中选择数百行。

使 contact_type 和 date_time 匹配的正确查询是什么？

更新当我最初问这个问题时，我没有意识到 View 中不能有子查询。这需要另存为 View 。为了这个问题的完整性，如何将其分解为多个 View 并合并为一个？

Answer 1

没有浏览量

一个简单的解决方案是使用子查询来获取按日期排序的联系人日志，由全局查询调用以按 customer_id 对它们进行分组:

SELECT * FROM
(
    SELECT
        customers.customer_id,
        customers.first,
        customers.last,
        contact_log.contact_type,
        contact_log.date_time

        FROM customers
            INNER JOIN contact_log ON contact_log.customer_id = customers.customer_id -- or LEFT JOIN - see comment

        ORDER BY contact_log.date_time DESC
) logs GROUP BY logs.customer_id

如果您有一个庞大的数据库，您将必须检查模式是否正确索引、是否启用缓存等...

有观点

道理是一样的。子查询被第一个 View 取代，“全局” View 请求它对结果进行分组。请注意，我在“日志” View 中使用了 GROUP BY 而不是 ORDER BY。

CREATE VIEW logs AS 
    SELECT
        customers.customer_id,
        customers.first,
        customers.last,
        contact_log.contact_type,
        contact_log.date_time

        FROM customers
            LEFT JOIN contact_log ON contact_log.customer_id = customers.customer_id

        GROUP BY
            customers.customer_id,
            contact_log.date_time DESC,
            contact_log.contact_type DESC;

CREATE VIEW testview AS SELECT * FROM logs GROUP BY logs.customer_id;

SELECT * FROM testview;