有沒有辦法按列顯示前5名和后5名以及順序？

有沒有辦法按列顯示前 5 名和后 5 名以及順序？

我有 2 個查詢，如下所示：

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

我希望最終輸出會按順序對費用列進行DESC排序，但我無法對嵌套列進行操作。

輸出是：

2016 Summer Rio de Janeiro    10
2014 Winter Sochi              8.2
2012 Summer London             8
2002 Winter Salt Lake City     7
2004 Summer Athina             7
1900 Summer Paris              1
1904 Summer St. Louis          1
1906 Summer Athina             1
1912 Summer Stockholm          1.3
1920 Summer Antwerpen          1.4

而所需的輸出應根據最后一列按 DESC 順序排列

uj5u.com熱心網友回復：

您可以使用視窗函式row_number() over()。如果碰巧有關系，并且您想看到它們……您可以dense_rank()改用

例子

with cte as (
SELECT OG.GAMES AS [Olympic Games]
     , OC.CITY AS [Hosting City]
     , OG.Expenses_Mil AS [Financial Expenses (in multiplies of 1M$)]
     , RN1 = row_number() over (order by OG.Expenses_Mil asc)
     , RN2 = row_number() over (order by OG.Expenses_Mil desc)
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 multiplies of 1M$)]
 From  cte 
 Where RN1<=5
    or RN2<=5
 Order by [Financial Expenses (in multiplies of 1M$)] desc

只是一個旁注： 視窗函式可能是無價的。它們非常值得您花時間適應它們。

uj5u.com熱心網友回復：

@JohnCappelletti 的回答非常好。但是它確實需要兩種排序，一種是升序，一種是降序。

這是一個使用單一排序的解決方案：

我們使用LEAD偏移量來檢查是否缺少 5 行。

with cte as (
  SELECT OG.GAMES AS [Olympic Games]
       , OC.CITY AS [Hosting City]
       , OG.Expenses_Mil AS [Financial Expenses (in multiplies of 1M$)]
       , RN = row_number() over (order by OG.Expenses_Mil desc)
       , ID5 = lead(OEF.ID, 5) over (order by OG.Expenses_Mil desc)
  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 multiplies of 1M$)]
 From  cte 
 Where RN <= 5
    or ID5 IS NULL
 Order by [Financial Expenses (in multiplies of 1M$)] desc

uj5u.com熱心網友回復：

我找到了解決SP問題的原始方法：

CREATE PROCEDURE OLYMPIC_UNION_ORDERBY
AS  
SELECT * FROM

(SELECT DISTINCT TOP(5) OG.GAMES AS 'Olympic Games', OC.CITY AS 'Hosting City', OG.Expenses_Mil AS 'Financial Expenses (in multiplies 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 multiplies 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
;

EXEC OLYMPIC_UNION_ORDERBY

CREATE TABLE  TempTable
(OUTPUT1 VARCHAR(MAX),
 OUTPUT2 VARCHAR(MAX),
 OUTPUT3 FLOAT
)

INSERT INTO TempTable
EXEC OLYMPIC_UNION_ORDERBY

SELECT * 
FROM TempTable
ORDER BY OUTPUT3

標籤：sql sql-server 查询语句

上一篇：選擇的存盤程序邏輯

下一篇：分離具有優先權的重疊日期記錄