mysql分页排序带参数

http://www.dewen.io/q/4248


select empid,deptid,rank from (
select c.empid,c.deptid,
if(@pdept=c.deptid,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=c.deptid
from ( 
select catid AS empid,siteid as deptid from v9_category 
) c ,(select  @pdept := null ,@rank:=0) a ) result 




SELECT empid,deptid,salary,rank FROM 
    (SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=ff.deptid,@rank:=@rank+1,@rank:=1) AS rank,@pa:=ff.deptid 
    FROM 
    (SELECT empid,deptid,salary FROM employee GROUP BY deptid,salary ORDER BY deptid ASC, salary DESC) ff,(SELECT @rank:=0,@rownum:=0,@pa=NULL) tt) result


这个可以：行号和排序序号都有：
select * from (
select c.empid,c.deptid,@rownum:=@rownum+1 AS rownum,
-- if(@pdept=c.deptid,@rank:=@rank+1,@rank:=1) as rank,@pdept:=c.deptid 相同不并列
if(@pdept=c.deptid,@rank:=@rank,@rank:=@rank+1) as rank,@pdept:=c.deptid -- 相同的并列排序
from ( 
-- select catid AS empid,siteid as deptid from v9_category 
 SELECT n.id empid, n.catid deptid,c.catname FROM v9_news n JOIN v9_category c ON c.catid=n.catid -- LIMIT 1,15


) c ,(select  @pdept := null ,@rank:=0,@rownum:=0) a ) result