Sequence
相對於父上下文中的同級對行位置索引進行編號
因此,我有一個包含大量文本條目的表,但每個條目都有一個父上下文,用於標識組內的條目,例如:
+------+-------+------+ | PKID | GrpID | Body | +------+-------+------+ | 1 | 1 | a... | +------+-------+------+ | 2 | 2 | b... | +------+-------+------+ | 3 | 2 | c... | +------+-------+------+ | 4 | 1 | d... | +------+-------+------+ | 5 | 3 | e... | +------+-------+------+ | 6 | 3 | f... | +------+-------+------+ | 7 | 2 | g... | +------+-------+------+ | 8 | 1 | h... | +------+-------+------+ | 9 | 1 | i... | +------+-------+------+
現在,我正在嘗試為每行的位置獲取一個數值(根據結果中的行位置計算,因此每次根據使用的謂詞可能不同)。所以(我已經通過 GrpID 對其進行了排序以使其更易於閱讀,但想像一下它是自然排序的):
+------+-----+-------+------+ | PKID | Idx | GrpID | Body | +------+-----+-------+------+ | 1 | 1 | 1 | a... | +------+-----+-------+------+ | 4 | 2 | 1 | d... | +------+-----+-------+------+ | 8 | 3 | 1 | h... | +------+-----+-------+------+ | 9 | 4 | 1 | i... | +------+-----+-------+------+ | 2 | 1 | 2 | b... | +------+-----+-------+------+ | 3 | 2 | 2 | c... | +------+-----+-------+------+ | 7 | 3 | 2 | g... | +------+-----+-------+------+ | 5 | 1 | 3 | e... | +------+-----+-------+------+ | 6 | 2 | 3 | f... | +------+-----+-------+------+
可以對這樣的數據集執行什麼查詢才能得到這樣的結果?
在 SQL Server 和其他支持 ROW_NUMBER 的系統中…
SELECT *, ROW_NUMBER() OVER ( PARTITION BY GrpID ORDER BY PKID ) AS Idx FROM YourTable;
適用於 MS SQL 2012 伺服器
select id, grp , grpn = ROW_NUMBER() OVER ( PARTITION BY [grp] ORDER BY [grp] ) from list order by grp,id