mysql 显示行号，以及分组排序

短命女 2022-08-21 00:13 214阅读 0赞

#     #

建表：

![复制代码][copycode.gif]

CREATE TABLE `my_tb` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `parent_code` varchar(255) DEFAULT NULL,
      `code` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

![复制代码][copycode.gif]

初始数据：

![复制代码][copycode.gif]

INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('01', '001');
    INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('01', '002');
    INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('02', '001');
    INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('01', '003');
    INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('02', '002');
    INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('03', '001');
    INSERT INTO `my_tb` (  `parent_code`, `code`) VALUES ('04', '001');

![复制代码][copycode.gif]

![43766-20150914130439648-1356322909.jpg][]

查询行号：

-- 生成 行号
    select @r:=@r+1 as row_num , a.* from  my_tb a ,(select @r:=0) b

![43766-20150914130525336-1814866695.jpg][]

显示分组号：

![复制代码][copycode.gif]

-- 生成 分组排序号
    
    select  
         @group_row:=CASE when @parent_code=a.parent_code then  @group_row+1 else 1 end as groupRow,
         @parent_code:=a.parent_code as parent_code,
         a.code  
    
      from  my_tb a ,( select @group_row:=1, @parent_code:='') as b
     ORDER BY   a.parent_code , a.code

![复制代码][copycode.gif]

![43766-20150914130537976-317082543.jpg][]

[copycode.gif]: /images/20220731/7d1244f5ceb74623a076ae05efcaf46a.png
[43766-20150914130439648-1356322909.jpg]: /images/20220731/78f861c645c74657bd7ec38fef2c023a.png
[43766-20150914130525336-1814866695.jpg]: /images/20220731/b2c273c8caaf41a8af6e55bf51483ba7.png
[43766-20150914130537976-317082543.jpg]: /images/20220731/c8217766c21a4a4b984b226d04badfed.png