跳至主要內容
翻譯頁面

本頁面由英文在 AI 協助下翻譯而成。若兩者有出入,請以英文版本為準。

ROW_NUMBER

傳回資料列在目前分割區內的編號。資料列從 1 開始編號,依照視窗定義中 ORDER BY 子句所定義的順序,否則依任意順序。

另請參閱: [SELECT] [RANK] [DENSE_RANK]


語法​

SELECT ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column [ASC|DESC])
FROM <multipartIdentifier>;

引數​

PARTITION BY column 選用。將結果集分成多個分割區。ROW_NUMBER 函式會分別套用至每個分割區,並在每個分割區重設資料列編號。

ORDER BY column 指定指派資料列編號的順序。必須指定才能得到有意義的結果。

傳回值​

傳回整數,代表分割區內從 1 開始的連續資料列編號。


範例​

在存放庫內為貢獻者排名​

-- Rank contributors within a repository
SELECT
login,
contributions,
ROW_NUMBER() OVER (ORDER BY contributions DESC) as contribution_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';

追蹤 issue 隨時間的順序​

-- Track issue growth over time
SELECT
number,
title,
state,
created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY created_at;

將 ROW_NUMBER 與 RANK 和 DENSE_RANK 比較​

-- Compare RANK vs DENSE_RANK vs ROW_NUMBER for contributors
SELECT
login,
contributions,
RANK() OVER (ORDER BY contributions DESC) as rank,
DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank,
ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num
FROM github.repos.contributors
WHERE owner = 'stackql'
AND repo = 'stackql';

如需詳細資訊,請參閱 https://sqlite.org/windowfunctions.html#built-in_window_functions。