SELECT
(@i:=case when @col3 = col3 then @i+1 else 1 end) as rownum,
(@col3:= col3 ), //为变量赋值等于上一个字段的值
col3,
col4,
id
FROM `test_table` ,
(select @i:=0, @col3:=0) t1 //初始化变量值
group by col3, col4 // !!!分组条件内必须包含不重复列,不然就先分组在编号
order by col3, col4
此方法编号顺序是按照数据插入顺序编号,例:id = 1,2,3,4, rownum =1,2,3,4
id = 2,1,3,4,rownum = 2,1,3,4