• SQL 服务器
• 甲骨文
• Postgres
• Sybase
• MySQL 8.0+
• MariaDB 10.2+
• SQLite 3.25+

这涵盖了大多数基础。

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

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;

特定于 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 ;

然后，我们可以调用：

CALL NumberRowsByGroup();

根据 xaprb.com/blog 帖子： how-to-number-rows-in-mysql ，第二种速度更快。