Mysql的给分组加行号

 

（1）第一次执行时分组行号不起作用

　　1.按指定排序获取数据：

select * from table order by type

　　2.给获取的结果增加统计字段：

@num:=IF(@groups=`type`,@num+1,IF(@groups:=`type`,1,1)) as total 

　　3.组合语句：

SELECT wid,name,type,@num:=IF(@groups=`type`,@num+1,IF(@groups:=`type`,1,1)) as total FROM w_websites ORDER BY type 

　

（2）分组加行号

SELECT a.type,IF(@v<>a.type,@r:=1,@r:=@r+1) as total,IF(@v<>a.type,@v:=a.type,@v) as ctype
FROM (SELECT @r:=0,@v:=-1) as c,w_websites a 
INNER JOIN xxx b
ON xxx
WHERE xxx
ORDER BY xxx

　　

转载于:https://www.cnblogs.com/itwlp/p/11597112.html