MySQL JOIN ON含NULL列处理：单客户匹配最新礼盒

问题背景 #

为教堂圣诞礼盒活动搭建网站时，需要实现客户索引页面，展示每个客户的最新有效礼盒编号及基本信息。现有clients和hampers两张表，原查询依赖clients.hamper_id关联，无法正确匹配hamper_id为NULL的客户的最新礼盒记录，要求：

• 每个客户仅返回一条最新礼盒数据
• 无礼盒记录时，关联字段返回NULL
• 不依赖clients.hamper_id字段进行匹配

表结构与测试数据 #

clients表 #

hampers表 #

原查询问题 #

原查询通过clients.hamper_id关联礼盒表，导致hamper_id为NULL的客户无法匹配到任何礼盒记录：

SELECT h.`id` AS h_id, h.`client_id` AS c_id, IF(YEAR(h.`created_date`)=2022, h.`hamper_no`, NULL) AS hamper_no, YEAR(h.`created_date`) AS h_year, c.`id`, `hamper_id`, `name` FROM `clients` AS c LEFT JOIN `hampers` AS h ON (c.`id` = h.`client_id` AND IF(c.`hamper_id` IS NULL, NULL, c.`hamper_id`) = h.`id`) ORDER BY `name`; 

可行解决方案 #

方法1：窗口函数（推荐，MySQL 8.0+） #

利用ROW_NUMBER()窗口函数对每个客户的礼盒按创建时间降序排序，标记最新记录后关联客户表：

SELECT 
    h.h_id,
    h.c_id,
    IF(YEAR(h.created_date) = 2022, h.hamper_no, NULL) AS hamper_no,
    YEAR(h.created_date) AS h_year,
    c.id,
    c.hamper_id,
    c.name
FROM clients c
LEFT JOIN (
    SELECT 
        id AS h_id,
        client_id AS c_id,
        hamper_no,
        created_date,
        -- 按客户分组，最新礼盒标记为1
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY created_date DESC) AS rn
    FROM hampers
) h ON c.id = h.c_id AND h.rn = 1
ORDER BY c.name;

逻辑说明

1. 子查询中对每个客户的礼盒按created_date倒序排列，给最新记录分配编号1
2. 主查询通过LEFT JOIN只关联编号为1的记录，确保每个客户仅返回一条最新数据
3. 无礼盒的客户（如DOE, Jack），所有关联字段自动返回NULL

方法2：子查询匹配最新日期（兼容MySQL 5.x） #

如果使用低版本MySQL（不支持窗口函数），可以通过子查询获取每个客户的最新礼盒创建日期，再关联匹配：

SELECT 
    h.id AS h_id,
    h.client_id AS c_id,
    IF(YEAR(h.created_date) = 2022, h.hamper_no, NULL) AS hamper_no,
    YEAR(h.created_date) AS h_year,
    c.id,
    c.hamper_id,
    c.name
FROM clients c
LEFT JOIN hampers h 
    ON c.id = h.client_id 
    AND h.created_date = (
        -- 获取当前客户的最新礼盒创建日期
        SELECT MAX(created_date) 
        FROM hampers 
        WHERE client_id = c.id
    )
ORDER BY c.name;

逻辑说明

1. 子查询动态获取每个客户对应的最新礼盒日期
2. 主查询通过LEFT JOIN匹配客户ID和该日期，确保只返回最新礼盒记录
3. 无礼盒的客户关联字段返回NULL，符合需求

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