MySQL 5.7.40中创建关联用户最新事件的视图的可行方案

环境与表结构 #

运行MySQL Community Server v5.7.40，现有两张表customers和events，建表语句如下：

create table customers (customer_id int, name varchar(100), primary key (customer_id));
create table events (event_id int, customer_id int, event_date datetime, event_type varchar(100), primary key (event_id), foreign key (customer_id) references customers(customer_id));

表数据 #

CUSTOMERS表

EVENTS表

需求 #

创建一个视图，将每个客户记录与该客户的最新事件记录关联：

• 按event_date降序排序，日期相同时取event_id最小的记录（对应预期结果中的第一条）
• 内连接或左连接均可，无匹配事件的客户记录可忽略

预期结果 #

现有尝试的问题 #

新版本可行方法（MySQL 8+） #

使用窗口函数ROW_NUMBER()可以轻松实现，但MySQL 5.7不支持该语法：

WITH ranked_events AS (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY Customer_Id ORDER BY Event_Date DESC, event_id ASC) AS rn
  FROM events AS e
)
SELECT c.customer_id,
        c.name as customer_name,
        re.event_id as latest_event_id,
        re.event_date as latest_event_date,
        re.event_type as latest_event_type
FROM customers c
JOIN ranked_events re ON c.customer_id = re.customer_id 
WHERE re.rn = 1;

变量方法的局限 #

使用自增行号变量的SELECT语句可以得到正确结果，但添加create or replace view后会报错：

Error Code: 1351. View's SELECT contains a variable or parameter

即使去掉CTE（MySQL 5.7本身不支持CTE），改用嵌套子查询的变量写法，创建视图时仍然会触发该错误。

适合MySQL 5.7的视图实现方案 #

以下两种写法均不使用变量，符合MySQL 5.7视图的创建规则，且能得到预期结果：

方法一：关联子查询筛选最新事件 #

CREATE OR REPLACE VIEW customer_latest_events AS
SELECT 
    c.customer_id,
    c.name AS customer_name,
    e.event_id AS latest_event_id,
    e.event_date AS latest_event_date,
    e.event_type AS latest_event_type
FROM customers c
JOIN events e ON c.customer_id = e.customer_id
WHERE NOT EXISTS (
    SELECT 1 
    FROM events e2 
    WHERE e2.customer_id = e.customer_id
    AND (e2.event_date > e.event_date OR (e2.event_date = e.event_date AND e2.event_id < e.event_id))
);

方法二：分组获取最新事件标识后关联 #

CREATE OR REPLACE VIEW customer_latest_events AS
SELECT 
    c.customer_id,
    c.name AS customer_name,
    e.event_id AS latest_event_id,
    e.event_date AS latest_event_date,
    e.event_type AS latest_event_type
FROM customers c
JOIN events e ON c.customer_id = e.customer_id
JOIN (
    SELECT 
        customer_id,
        MAX(event_date) AS max_event_date,
        MIN(event_id) AS min_event_id
    FROM events
    WHERE event_date = (
        SELECT MAX(event_date) 
        FROM events e2 
        WHERE e2.customer_id = events.customer_id
    )
    GROUP BY customer_id
) latest ON e.customer_id = latest.customer_id 
    AND e.event_date = latest.max_event_date 
    AND e.event_id = latest.min_event_id;

内容的提问来源于stack exchange，提问作者nick_j_white