MYSQL-实现ORACLE- row

今天看了篇帖子，被几个地方转载，但是性能不太好，因为不能评论，所以把优化思路写在这里。

1.确定需求: 根据部门来分组,显示各员工在部门里按薪水排名名次.

2.来创建实例数据:

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);

3. http://www.kaishixue.com/mysql/14.html 帖子中SQL的实现

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;

执行计划如下

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1PRIMARY<derived2>ALLNULLNULLNULLNULL9100.00 2DERIVED<derived4>systemNULLNULLNULLNULL1100.00 2DERIVED<derived3>ALLNULLNULLNULLNULL9100.00 4DERIVEDNULLNULLNULLNULLNULLNULLNULLNULLNo tables used3DERIVEDheyf_t10ALLNULLNULLNULLNULL9100.00Using filesort

如果大数据量直接就挂了。换个思路来写

select h.`empid`,h.`deptid`,h.`salary`,count(*) as rank from heyf_t10 as h left outer join heyf_t10 as r on h.deptid = r.deptid and h.`salary`<= r.`salary` group by h.`empid`,h.`deptid`,h.`salary` order by h.deptid, h.salary desc;

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEhALLNULLNULLNULLNULL9100.00Using temporary; Using filesort1SIMPLErALLNULLNULLNULLNULL9100.00

需要优化的时候建立个索引优化掉filesort，很直观，效率也提高了很多。有时候不能太赶需求，遇到复杂的SQL还是让DBA来优化一下。

--EOF--

 

转载于:https://www.cnblogs.com/buro79xxd/archive/2012/08/29/2662489.html

相关资源：DirectX修复工具V4.0增强版