如何将两个不同最大日期的结果作为同一结果集列？附现有MySQL查询

要实现把两个不同截止日期对应的科目最新记录并列显示在同一行，核心思路是分别获取每个日期阈值下的科目最新记录，再通过pupilID和subject作为关联键将结果集连接。下面提供两种常用的实现方式，你可以根据自己的MySQL版本和需求选择：

方式一：使用窗口函数（推荐，MySQL 8.0+支持） #

窗口函数ROW_NUMBER()可以更简洁地筛选出每个科目在指定日期内的最新记录，再通过JOIN合并结果：

WITH date1_latest AS (
    SELECT
        pupilID,
        subject,
        judgement,
        assessor,
        dateAchieved,
        -- 按科目分组，日期倒序排序，标记最新记录为1
        ROW_NUMBER() OVER (PARTITION BY pupilID, subject ORDER BY dateAchieved DESC) AS rn
    FROM wwlpredictedoveralljudgements
    WHERE pupilID = '115137218853'
      AND dateAchieved <= '2023-06-30' -- 第一个截止日期
),
date2_latest AS (
    SELECT
        pupilID,
        subject,
        judgement,
        assessor,
        dateAchieved,
        ROW_NUMBER() OVER (PARTITION BY pupilID, subject ORDER BY dateAchieved DESC) AS rn
    FROM wwlpredictedoveralljudgements
    WHERE pupilID = '115137218853'
      AND dateAchieved <= '2024-06-30' -- 第二个截止日期
)
SELECT
    -- 确保subject字段不为空，兼容单侧无记录的情况
    COALESCE(d1.subject, d2.subject) AS subject,
    d1.judgement AS judgement_before_date1,
    d1.assessor AS assessor_before_date1,
    d1.dateAchieved AS date_achieved_before_date1,
    d2.judgement AS judgement_before_date2,
    d2.assessor AS assessor_before_date2,
    d2.dateAchieved AS date_achieved_before_date2
FROM date1_latest d1
LEFT JOIN date2_latest d2
    ON d1.pupilID = d2.pupilID AND d1.subject = d2.subject AND d2.rn = 1
WHERE d1.rn = 1
-- 补充仅在第二个日期有记录的科目
UNION ALL
SELECT
    d2.subject AS subject,
    NULL AS judgement_before_date1,
    NULL AS assessor_before_date1,
    NULL AS date_achieved_before_date1,
    d2.judgement AS judgement_before_date2,
    d2.assessor AS assessor_before_date2,
    d2.dateAchieved AS date_achieved_before_date2
FROM date2_latest d2
LEFT JOIN date1_latest d1
    ON d1.pupilID = d2.pupilID AND d1.subject = d2.subject AND d1.rn = 1
WHERE d2.rn = 1 AND d1.pupilID IS NULL;

方式二：使用关联子查询（兼容低版本MySQL） #

如果你的MySQL版本不支持CTE和窗口函数，可以用NOT EXISTS子查询筛选最新记录，再通过LEFT JOIN合并：

SELECT
    COALESCE(t1.subject, t2.subject) AS subject,
    t1.judgement AS judgement_before_date1,
    t1.assessor AS assessor_before_date1,
    t1.dateAchieved AS date_achieved_before_date1,
    t2.judgement AS judgement_before_date2,
    t2.assessor AS assessor_before_date2,
    t2.dateAchieved AS date_achieved_before_date2
FROM
    (-- 筛选第一个日期内的科目最新记录
     SELECT 
         pupilID, subject, judgement, assessor, dateAchieved
     FROM wwlpredictedoveralljudgements t_main
     WHERE pupilID = '115137218853'
       AND dateAchieved <= '2023-06-30'
       AND NOT EXISTS (
           SELECT 1 FROM wwlpredictedoveralljudgements t_sub
           WHERE t_sub.pupilID = t_main.pupilID
             AND t_sub.subject = t_main.subject
             AND t_sub.dateAchieved <= '2023-06-30'
             AND t_sub.dateAchieved > t_main.dateAchieved
       )) AS t1
LEFT JOIN
    (-- 筛选第二个日期内的科目最新记录
     SELECT 
         pupilID, subject, judgement, assessor, dateAchieved
     FROM wwlpredictedoveralljudgements t_main
     WHERE pupilID = '115137218853'
       AND dateAchieved <= '2024-06-30'
       AND NOT EXISTS (
           SELECT 1 FROM wwlpredictedoveralljudgements t_sub
           WHERE t_sub.pupilID = t_main.pupilID
             AND t_sub.subject = t_main.subject
             AND t_sub.dateAchieved <= '2024-06-30'
             AND t_sub.dateAchieved > t_main.dateAchieved
       )) AS t2
ON t1.pupilID = t2.pupilID AND t1.subject = t2.subject
-- 补充仅在第二个日期有记录的科目
UNION ALL
SELECT
    t2.subject AS subject,
    NULL AS judgement_before_date1,
    NULL AS assessor_before_date1,
    NULL AS date_achieved_before_date1,
    t2.judgement AS judgement_before_date2,
    t2.assessor AS assessor_before_date2,
    t2.dateAchieved AS date_achieved_before_date2
FROM
    (SELECT 
         pupilID, subject, judgement, assessor, dateAchieved
     FROM wwlpredictedoveralljudgements t_main
     WHERE pupilID = '115137218853'
       AND dateAchieved <= '2024-06-30'
       AND NOT EXISTS (
           SELECT 1 FROM wwlpredictedoveralljudgements t_sub
           WHERE t_sub.pupilID = t_main.pupilID
             AND t_sub.subject = t_main.subject
             AND t_sub.dateAchieved <= '2024-06-30'
             AND t_sub.dateAchieved > t_main.dateAchieved
       )) AS t2
LEFT JOIN
    (SELECT 
         pupilID, subject, judgement, assessor, dateAchieved
     FROM wwlpredictedoveralljudgements t_main
     WHERE pupilID = '115137218853'
       AND dateAchieved <= '2023-06-30'
       AND NOT EXISTS (
           SELECT 1 FROM wwlpredictedoveralljudgements t_sub
           WHERE t_sub.pupilID = t_main.pupilID
             AND t_sub.subject = t_main.subject
             AND t_sub.dateAchieved <= '2023-06-30'
             AND t_sub.dateAchieved > t_main.dateAchieved
       )) AS t1
ON t1.pupilID = t2.pupilID AND t1.subject = t2.subject
WHERE t1.pupilID IS NULL;

关键说明 #

• 请将示例中的'2023-06-30'和'2024-06-30'替换为你实际需要的两个截止日期。
• COALESCE函数用于处理某个日期阈值下无对应科目记录的情况，避免subject字段出现NULL。
• 如果你的场景中两个日期阈值下的科目完全一致，可以简化掉UNION ALL部分，直接用LEFT JOIN即可。

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