如何合并需分别排序的过去3条与未来3条预约SQL查询结果？

要合并两个独立排序并取TOP3的查询结果，核心是让每个查询先完成自身的筛选和排序，再将结果合并。具体可以通过子查询或**CTE（公共表表达式）**实现，这两种方式能让每个查询独立执行排序和TOP筛选，再用UNION ALL合并结果（无需去重，效率更高）。

方法一：使用CTE实现 #

WITH 未来预约 AS (
    SELECT TOP 3 
        e.Events, e.EventDate
    FROM
        events e
    JOIN 
        EventMatters em ON em.Events = e.Events
    WHERE 
        Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
        AND EventKind = 'D' 
        AND CONVERT(DATE, e.eventdate) > '2022/05/03'
    ORDER BY 
        e.EventDate
),
过往预约 AS (
    SELECT TOP 3 
        e.Events, e.EventDate
    FROM
        events e
    JOIN 
        EventMatters em ON em.Events = e.Events
    WHERE 
        Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
        AND e.EventKind = 'D' 
        AND CONVERT(DATE, e.eventdate) < '2022/05/03'
    ORDER BY 
        EventDate DESC
)
SELECT Events, EventDate FROM 未来预约
UNION ALL
SELECT Events, EventDate FROM 过往预约
-- 若需对合并后的结果统一排序，可添加以下语句
-- ORDER BY CASE WHEN EventDate > '2022/05/03' THEN 1 ELSE 0 END DESC, EventDate;

方法二：使用子查询实现 #

-- 合并未来3条和过往3条预约记录
SELECT Events, EventDate
FROM (
    -- 未来预约查询
    SELECT TOP 3 
        e.Events, e.EventDate
    FROM
        events e
    JOIN 
        EventMatters em ON em.Events = e.Events
    WHERE 
        Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
        AND EventKind = 'D' 
        AND CONVERT(DATE, e.eventdate) > '2022/05/03'
    ORDER BY 
        e.EventDate
) AS 未来预约
UNION ALL
SELECT Events, EventDate
FROM (
    -- 过往预约查询
    SELECT TOP 3 
        e.Events, e.EventDate
    FROM
        events e
    JOIN 
        EventMatters em ON em.Events = e.Events
    WHERE 
        Matters = '000122FD-47B6-47B6-47B6-1FCAB474CE53' 
        AND e.EventKind = 'D' 
        AND CONVERT(DATE, e.eventdate) < '2022/05/03'
    ORDER BY 
        EventDate DESC
) AS 过往预约

关键说明 #

1. 用UNION ALL而非UNION：因为两个查询的日期范围无重叠（一个大于指定日期，一个小于），不会产生重复记录，UNION ALL无需执行去重操作，性能更优。
2. 子查询/CTE的作用：确保每个查询独立完成ORDER BY和TOP 3筛选，得到符合要求的结果后再合并，避免直接UNION导致排序逻辑失效。
3. 可选统一排序：如果需要对合并后的结果按“未来在前、过往在后”或其他规则排序，可在最后添加ORDER BY语句，示例已注释。

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