MySQL查询列表输出行号、分组输出行号

1、MySQL查询列表输出序号

• 查询SQL：from 数据表列表添加 (SELECT @rowno := 0) AS rn
• 结果集添加：@rowno := @rowno + 1 as rowno
• 如下例子所示：

select @rowno := @rowno + 1 as rowno, c.id,c.equipment_name from (
        SELECT id, equipment_name, a.spare_id as datails
        FROM crmc_spare_project a
        where project_id = '1'
        ORDER BY a.id
    ) c, (SELECT @rowno := 0) AS rn

• 查询结果集如下表所示：

 

rowno	id	equipment_name
1	7	测试1
2	7	测试1
3	7	测试1
4	8	测试
5	8	测试

 

 

 

 

 

 

 

2、MySQL查询列表分组输出序号

• 查询SQL：from 数据表列表添加 (select @rowgroup:=1, @equipment_name:='') as rg
• 结果集添加： 
    1）@rowgroup :=CASE when @equipment_name=a.equipment_name then  @rowgroup+1 else 1 end as serial_num
    2）@equipment_name:=a.equipment_name as equipment_name_str
         注：该字段在结果集中不能省略，a.equipment_name是你的分组字段

SELECT 
        @rowgroup :=CASE when @equipment_name=a.equipment_name then  @rowgroup+1 else 1 end as serial_num,
        @equipment_name:=a.equipment_name as equipment_name_str,
        id, 
        equipment_name, 
        substring_index(substring_index(replace(regexp_replace(a.spare_id, '\\[|\\]| ', ''), '},{', '}|{'), '|', b.help_topic_id + 1), '|', -1) as datails
    FROM (select @rowgroup:=1, @equipment_name:='') as rg,
        crmc_spare_project a
        JOIN mysql.help_topic b ON b.help_topic_id <= (length(replace(regexp_replace(a.spare_id, '\\[|\\]| ', ''), '},{', '}|{')) - length(REPLACE(replace(regexp_replace(a.spare_id, '\\[|\\]| ', ''), '},{', '}|{'), '|', '')))
    where project_id = '1' 

• 查询结果集如下表所示：

  serial_num	equipment_name_str	id	equipment_name	datails
  1	测试1	7	测试1	{"id":5,"num":2}
  2	测试1	7	测试1	{"id":10,"num":4}
  3	测试1	7	测试1	{"id":2,"num":2}
  1	测试	8	测试	{"id":2,"num":1}
  2	测试	8	测试	{"id":8,"num":1}
  3	测试	8	测试	{"id":10,"num":1}
  1	123	9	123	{"id":8,"num":1}
  2	123	9	123	{"id":2,"num":1}
  3	123	9	123	{"id":10,"num":1}
  4	123	9	123	{"id":5,"num":1}
  1	qwqw	10	qwqw	{"id":2,"num":2}
  2	qwqw	10	qwqw	{"id":8,"num":1}
  3	qwqw	10	qwqw	{"id":5,"num":3}