SQL如何实现按指定列排序的TOP5与BOTTOM5合并后整体降序输出

你可以直接将UNION ALL拼接后的整体结果作为派生表，在外层添加全局排序规则即可，无需修改原有子查询的逻辑，修改后的代码如下：

SELECT * 
FROM (
    -- 原有查询开始
    SELECT * 
    FROM
        (SELECT DISTINCT TOP(5) 
             OG.GAMES AS 'Olympic Games', OC.CITY AS 'Hosting City', 
             OG.Expenses_Mil AS 'Financial Expenses (in multiples of 1M$)' 
         FROM 
             [Olympics].[Events_Facts] AS OEF 
         JOIN 
             [Olympics].[Games] AS OG ON OEF.GAMES_ID = OG.GAMES_ID
         JOIN 
             [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
         ORDER BY 
             OG.Expenses_Mil DESC) A

    UNION ALL

    SELECT * 
    FROM 
        (SELECT DISTINCT TOP(5) 
             OG.GAMES AS 'Olympic Games', OC.CITY AS 'Hosting City', 
             OG.Expenses_Mil AS 'Financial Expenses (in multiples of 1M$)' 
         FROM 
             [Olympics].[Events_Facts] AS OEF 
         JOIN 
             [Olympics].[Games] AS OG ON OEF.GAMES_ID = OG.GAMES_ID
         JOIN 
             [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
         ORDER BY 
             OG.Expenses_Mil) B
    -- 原有查询结束
) AS CombinedResult
-- 全局按费用列降序排序
ORDER BY [Financial Expenses (in multiples of 1M$)] DESC

补充说明 #

• 子查询内的ORDER BY仅和TOP搭配生效，用于筛选最高/最低的5条数据，不会影响UNION ALL之后的整体结果顺序，因此必须在外层添加全局排序才能得到你需要的最终效果。
• 也可以用窗口函数实现相同需求，仅需要扫描一次数据表，性能更优，代码逻辑更简洁：

WITH RankedGames AS (
    SELECT DISTINCT
        OG.GAMES AS 'Olympic Games', 
        OC.CITY AS 'Hosting City', 
        OG.Expenses_Mil AS 'Financial Expenses (in multiples of 1M$)',
        -- 按费用降序排名取TOP5
        ROW_NUMBER() OVER (ORDER BY OG.Expenses_Mil DESC) AS Rank_Desc,
        -- 按费用升序排名取BOTTOM5
        ROW_NUMBER() OVER (ORDER BY OG.Expenses_Mil ASC) AS Rank_Asc
    FROM [Olympics].[Events_Facts] AS OEF 
    JOIN [Olympics].[Games] AS OG ON OEF.GAMES_ID = OG.GAMES_ID
    JOIN [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
)
SELECT [Olympic Games], [Hosting City], [Financial Expenses (in multiples of 1M$)]
FROM RankedGames
WHERE Rank_Desc <=5 OR Rank_Asc <=5
ORDER BY [Financial Expenses (in multiples of 1M$)] DESC

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