※ 引述《wellwind (..)》之銘言：
: 各位前輩好
: 小弟目前正在設計一個成績排名系統
: 排名資料庫格式大概如下
: 組別 學號 排名
: 001 00101 1
: 001 00103 2
: 001 00110 3
: 002 00204 1
: 002 00205 2
: 002 00214 3
: 目前是可以用類似下面的語法轉成橫向顯示
: SELECT [組別],
: MAX([學號1]) AS [第一名學號],
: MAX([學號2]) AS [第二名學號],
: MAX([學號3]) AS [第三名學號]
: FROM(
: SELECT [組別],
: CASE [排名] WHEN 1 THEN [學號] END AS [學號1],
: CASE [排名] WHEN 2 THEN [學號] END AS [學號2],
: CASE [排名] WHEN 3 THEN [學號] END AS [學號3]
: FROM score_tbl
: ) AS tbl2
: GROUP BY [組別]
: 查出來的表會變成這樣
: 組別 第一名學號 第二名學號 第三名學號
: 001 00101 00103 00110
: 002 00204 00205 00214
: 不過這樣沒有版法除裡相同排名的問題,例如同時是第一名,
: 或第四名跟第三名同分並列第三時,希望可以像這樣排
: 組別 第一名學號 第二名學號 第三名學號
: 001 00101 NULL 00110
: 001 00103 NULL NULL
: (00101,00103並列第一,所以00110是第三名)
: 002 00204 00205 00214
: 002 NULL NULL 00215
: (00214,00215並列第三,所以雖然是取前三名,但實際上有四名)
: 遇到這樣的要求,目前腦袋是一片空白,想請問各位該如何做這樣的查詢?
: 資料庫是SQL Server 2005
試試這樣可以嗎？
SELECT [虛擬編號],[組別],
MAX([第一名學號]), MAX([排名1]),
MAX([第二名學號]), MAX([排名2]),
MAX([第三名學號]), MAX([排名3])
FROM (
SELECT ROW_NUMBER() OVER (PARITION BY [組別],[排名1],[排名2],[排名3] ) AS [虛擬編號],
[組別],
[第一名學號],[排名1],
[第二名學號],[排名2],
[第三名學號],[排名3]
FROM (
SELECT [組別],
CASE [排名] WHEN 1 THEN [學號] END AS [第一名學號],
CASE [排名] WHEN 1 THEN [排名] END AS [排名1],
CASE [排名] WHEN 2 THEN [學號] END AS [第二名學號],
CASE [排名] WHEN 2 THEN [排名] END AS [排名2],
CASE [排名] WHEN 3 THEN [學號] END AS [第三名學號] CASE [排名] WHEN 3 THEN [排名] END AS [排名3],
FROM score_tbl )
) T_TMP
GROUP BGY [虛擬編號],[組別];
第二層T_TMP的結果：
虛擬編號 組別 第一名學號 排名1 第二名學號 排名2 第三名學號 排名3
1 1 101 1
2 1 103 1
1 1 110 3
1 2 204 1
1 2 205 2
1 2 214 3
2 2 215 3
最終結果：
虛擬編號 組別 第一名學號 排名1 第二名學號 排名2 第三名學號 排名3
1 1 101 1 110 3
2 1 103 1
1 2 204 1 205 2 214 3
2 2 215 3
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 123.240.166.244