房多多sql面试题：对数据分组后，再对每个分组内的数据按字母顺序给定编号

以上是笔试题

建立对应的数据表test

CREATE TABLE `test` (
  `name` varchar(128) DEFAULT NULL,
  `group` varchar(128) DEFAULT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

数据如下：
对应的sql（请教了一个同学，提供的方案）

  SELECT 
    t.group,
    t.name, 
    (SELECT COUNT(*) FROM test t1 WHERE t1.`group` = t.`group`) - (SELECT COUNT(*) FROM test t1 WHERE t1.`group` = t.`group` AND t1.`name` > t.name) AS i    
  FROM
    `test` t    
  ORDER BY `group` ASC,
    `name` ASC 

主要是这个sql的执行过程：从test t表中取出一行数据group, name后 将其传入as i所在那行sql，计算出结果后得到i值，然后继续重复以上过程。

更新2024-07-18：
方法2：
WITH RECURSIVE cte
AS
(
SELECT ‘’ as name, group, 1 as n from test group by group
UNION ALL
SELECT t.* ,n+1 as n FROM test t JOIN
(
select max(n), group from cte group by group
) tmp
ON (t.group = tmp.group )
)
SELECT cte.* FROM cte WHERE name != ‘’;

参考