ROW_NUMBER及RANK

ROW_NUMBER：

兩個TABLE，T1為Master，T2為Detail，取出同一PK在T2的第N筆資料

SELECT *
FROM Table_1 T1
   INNER JOIN (
      SELECT *
             , ROW_NUMBER() OVER (PARTITION BY PK ORDER BY PK) RID
      FROM Table_2) T2
   ON T1.PK = T2.PK
      AND T2.RID = 取第N筆

參考：ROW_NUMBER (Transact-SQL)

RANK：

很類似ROW_NUMBER的用法，但相同的資料相同排名，下一個不同會【跳脫】

SELECT *
FROM Table_1 T1
   INNER JOIN (
      SELECT *
             , RANK() OVER (PARTITION BY PK ORDER BY PK) RID
      FROM Table_2) T2
   ON T1.PK = T2.PK
      AND T2.RID = 取第N筆

參考：RANK (Transact-SQL)

其他參考：[SQL]為查詢的結果加上序號(ROW_NUMBER,RANK,OVER)