在 SQL 中按键组对行进行顺序编号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5463384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sequentially number rows by keyed group in SQL?
提问by Jé Queue
Is there a way in SQL to sequentially add a row number by key group?
SQL 中有没有办法按键组按顺序添加行号？
Assume a table with arbitrary (CODE,NAME) tuples. Example table:
假设一个包含任意 (CODE,NAME) 元组的表。示例表：
CODE NAME
---- ----
A Apple
A Angel
A Arizona
B Bravo
C Charlie
C Cat
D Dog
D Doppler
D Data
D Down
Desired projection using CODE as the grouping attribute:
使用 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,
谢谢，
回答by gbn
回答by ypercube??
MySQL (and probably most other databases):
MySQL（可能还有大多数其他数据库）：
select g.CODE
, count(*)-1 as C_NO
, g.NAME
from MyTable as g
left join MyTable as o
on g.CODE = o.CODE
and g.NAME >= o.NAME
group by g.CODE
, g.NAME;
Specific to MySQL:
特定于 MySQL：
DELIMITER $$
CREATE PROCEDURE NumberRowsByGroup()
BEGIN
SET @code := 0;
SET @num := 0;
SELECT CODE, C_NO, NAME FROM
( select q.CODE
, q.NAME
, @num := IF(q.CODE = @code, @num + 1, 0) as C_NO
, @code := q.CODE as previous
from yourTable q
order by CODE
, NAME
) as p
;
END$$
DELIMITER ;
Then, we can call:
然后，我们可以调用：
CALL NumberRowsByGroup();
According to xaprb.com/blog post: how-to-number-rows-in-mysql, the second is faster.
根据 xaprb.com/blog post: how-to-number-rows-in-mysql，第二个更快。
