MySQL/MariaDB跨库关联如何仅查询每个用户最新日期的订单

问题原因 #

你现有SQL的逻辑错误出在：先通过LEFT JOIN order.orders o把用户的所有订单都关联出来了，后续关联的子查询仅会给匹配到最新日期的订单补充子查询字段，没有过滤掉非最新日期的订单，因此会返回同一个用户的全部订单记录。

解决方案 #

方案1：MySQL 8.0+ / MariaDB 10.2+ 窗口函数写法（推荐） #

如果需要同一个用户最新日期的多条订单全部返回，用RANK()窗口函数：

SELECT u.*, o.*
FROM oauth.users u
LEFT JOIN (
    SELECT 
        *,
        RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rk
    FROM order.orders
) o ON u.id = o.customer_id AND o.rk = 1;

如果仅需要每个用户返回一条最新订单即可，把RANK()替换为ROW_NUMBER()即可。

方案2：兼容低版本MySQL/MariaDB写法 #

适合不支持窗口函数的旧版本环境，逻辑和你原来的子查询思路一致，调整关联条件即可：

SELECT u.*, o.*
FROM oauth.users u
LEFT JOIN order.orders o 
    ON u.id = o.customer_id
-- 直接限定订单日期等于对应用户的最大下单日期
WHERE o.order_date = (
    SELECT MAX(order_date)
    FROM order.orders o2
    WHERE o2.customer_id = u.id
)
-- 如果要保留从未下单的用户，把上面的WHERE改成AND即可

这个写法也会自动返回同一个用户最新日期下的所有匹配订单。

注意：order是SQL保留字，如果执行报语法错误，可将所有order.orders替换为`order`.orders，用反引号包裹库名规避冲突。

内容的提问来源于stack exchange，提问作者Samuel Morgan-Tyghe