在 SQL 中按键组对行进行顺序编号？

SQL中有没有办法按顺序添加行号按关键组?

假设一个表包含任意 (CODE,NAME) 元组。示例表：

CODE NAME    
---- ----
A    Apple
A    Angel
A    Arizona
B    Bravo
C    Charlie
C    Cat
D    Dog
D    Doppler
D    Data
D    Down

使用 CODE 作为分组属性的所需投影：

CODE C_NO NAME    
---- ---- ----
A    0    Apple
A    1    Angel
A    2    Arizona
B    0    Bravo
C    1    Charlie
C    0    Cat
D    0    Dog
D    1    Data
D    2    Down
D    3    Doppler

Thanks,

• SQL服务器 http://msdn.microsoft.com/en-us/library/ms186734.aspx
• Oracle http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm
• Postgres http://www.postgresql.org/docs/8.4/interactive/functions-window.html
• Sybase http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1520/html/iqrefbb/iqrefbb262.htm
• MySQL 8.0+ https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
• 玛丽亚数据库 10.2+ https://mariadb.com/kb/en/row_number/
• SQLite 3.25+ https://www.sqlite.org/windowfunctions.html

这涵盖了大多数基础。

SELECT
    CODE,
    ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY NAME) - 1 As C_NO,
    NAME
FROM
    MyTable