无分组
select @rownum:=@rownum+1 as autoSeq,
sid,pid,oprimary,seq
from singlechoice,(select @rownum:=0) r
order by pid,sid
单字段分组
select (@i := case when @tableName=a.tablename then @i + 1 else 1 end ) as rowIndex,
a.*,(@tableName:=a.tablename)
from tablestyle a,(select @i:=0,@tableName:=’’) as t
group by tablename,fieldname
order by tablename,(@i := case when @tableName=a.tablename then @i + 1 else 1 end )
多字段分组
select (@i := case when @tableName=concat(a.dbname,a.tablename) then @i + 1 else 1 end ) as rowIndex,
a.*,(@tableName:=concat(a.dbname,a.tablename)) as temp
from tablestyle a,(select @i:=0,@tableName:=’’) as t
group by dbname,tablename,fieldname
order by dbname,tablename,(@i := case when @tableName=concat(a.dbname,a.tablename) then @i + 1 else 1 end )
后续补充实际运用:
上面是我查到的别人写的一些方法,但是实际使用过程中还是有区别的,
直接举例说明:
先实现一个简单的效果:递增编号,理解一下第一个点(无分组)
SELECT
@r:= @r + 1 AS rowNum,
a.*
FROM
tls_certificate a,( SELECT @r:= 0 ) b
查询结果:
可以看到前面增加了一列 rowNum 字段。
第二种,根据 certificate_type 字段 分组排序
根据上面的方法 我摸索试验出多种写法:
先来一段代码:
SELECT
@r:= case when @tableName=a.certificate_type then @r+1 else 1 end as rowNum,
@tableName:=a.certificate_type as temp,
a.*
from
tls_certificate a ,(select @r:=0 ,@tableName:=’’) b
结果:
这时你会发现跟我们预想的结果不一样的,怎么rowNum 值都是1,而不是我们预期的根据 certificate_type 分组排序 显示结果 应该为 1,2,3
预期结果如下:
那怎么实现如上结果,不墨迹,直接上sql:
SELECT
@r:= case when @tableName=a.certificate_type then @r+1 else 1 end as rowNum,
@tableName:=a.certificate_type as temp,
a.*
from
tls_certificate a ,(select @r:=0 ,@tableName:=’’) b
**ORDER BY temp**
增加order by 字段, 重中之重。