mysql over 排序_mysql 实现按分组排序row_number()over(partition by order by) | 学步园

DROP TABLE IF EXISTS heyf_t10;

CREATE TABLE heyf_t10 (

empid INT ,

deptid INT ,

salary DECIMAL(10,2)

);

INSERT INTO heyf_t10 VALUES

(1,10,5500.00),

(2,10,4500.00),

(3,20,1900.00),

(4,20,4800.00),

(5,40,6500.00),

(6,40,14500.00),

(7,40,44500.00),

(8,50,6500.00),

(9,50,7500.00);

SELECT empid,deptid,salary,rank FROM (

SELECT heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 ,

IF(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) AS rank,

@pdept:=heyf_tmp.deptid

FROM (

SELECT empid,deptid,salary FROM heyf_t10 ORDER BY deptid ASC ,salary DESC

) heyf_tmp ,(SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) a ) result;

排序,变量存储上一行数据,实现行间数据的比较.