MySQL多表查询：如何让year与leave_alloted列唯一值仅显示一次

当然可以实现这个需求！你可以利用MySQL的窗口函数（或低版本的用户变量）来对比当前行与前一行的year和leave_alloted值，将重复出现的相同值替换为NULL或空格。下面分两种场景给出具体实现：

场景1：MySQL 8.0及以上版本（支持窗口函数） #

这种情况用LAG()窗口函数最简洁，它能获取分组内前一行的指定列值，再通过CASE语句判断是否重复：

SELECT 
    username,
    start,
    `end`, -- end是MySQL关键字，需要用反引号包裹
    leave_type,
    workdays,
    -- 处理year列：同用户下前一行year与当前行相同则返回NULL，否则返回当前year
    CASE 
        WHEN LAG(year) OVER (PARTITION BY username ORDER BY start) = year THEN NULL 
        ELSE year 
    END AS year,
    -- 处理leave_alloted列：逻辑同year列
    CASE 
        WHEN LAG(leave_alloted) OVER (PARTITION BY username ORDER BY start) = leave_alloted THEN NULL 
        ELSE leave_alloted 
    END AS leave_alloted
FROM 
    users
JOIN 
    `leave` ON users.username = `leave`.username -- leave是关键字，需反引号包裹
JOIN 
    leave_allotment ON users.username = leave_allotment.username AND `leave`.year = leave_allotment.year
ORDER BY 
    username, start;

关键逻辑说明： #

• PARTITION BY username：按用户分组，确保只对比同一用户内的行
• ORDER BY start：按请假开始时间排序，保证行的顺序符合业务逻辑
• 如果想要把重复值替换为空格而非NULL，只需把CASE里的NULL替换为''即可

场景2：MySQL 5.7及以下版本（不支持窗口函数） #

这种情况可以用用户变量来记录前一行的状态，逐行对比判断：

SELECT 
    username,
    start,
    `end`,
    leave_type,
    workdays,
    -- 处理year列：同用户下前一行year与当前行相同则返回NULL，否则更新变量并返回当前year
    CASE 
        WHEN @prev_user = username AND @prev_year = year THEN NULL 
        ELSE @prev_year := year 
    END AS year,
    -- 处理leave_alloted列：逻辑同year列
    CASE 
        WHEN @prev_user = username AND @prev_alloted = leave_alloted THEN NULL 
        ELSE @prev_alloted := leave_alloted 
    END AS leave_alloted
FROM 
    -- 先预查询并排序，保证变量判断的顺序正确
    (SELECT 
         u.username,
         l.start,
         l.end,
         l.leave_type,
         l.workdays,
         l.year,
         la.leave_alloted
     FROM 
         users u
     JOIN 
         `leave` l ON u.username = l.username
     JOIN 
         leave_allotment la ON u.username = la.username AND l.year = la.year
     ORDER BY 
         u.username, l.start) AS t,
    -- 初始化用户变量
    (SELECT @prev_user := '', @prev_year := NULL, @prev_alloted := NULL) AS vars;

关键逻辑说明： #

• 先通过子查询t将数据按用户和请假时间排序，确保变量判断的顺序正确
• 用@prev_user、@prev_year、@prev_alloted三个变量分别记录前一行的用户、年份和分配假期数，逐行对比更新

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