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

Question

在 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

感谢。

- Jé Queue

Answer 1

• SQL Server
• Oracle
• 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

- gbn

---

想知道这是否可以是不可知论的，但它是在Oracle上。 - Jé Queue

Answer 2

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文章：如何在MySQL中为行编号，第二种方法更快。

- ypercubeᵀᴹ

---

其他具有窗口函数的关系型数据库不需要第一种解决方案。 - gbn