mysql-implementation of Oracle and SQL Server data-row_number () over (partition by) grouping sorting feature

See a lot of examples on the net is basically the same, no too much explanation, for a beginner MySQL is a bit difficult, I transferred part of the following text: http://www.cnblogs.com/buro79xxd/archive/2012/08/29/2662489.html

Target: 1. Identify requirements: Group According to the department, showing the employees in the department by salary ranking.

Create a table: 2. To create the instance data:

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

Data:

Implementation 3. Implementation of SQL in http://www.kaishixue.com/mysql/14.html Post

SELECT
Empid
DeptID,
Salary
Rank
From
(
SELECT
Heyf_tmp.empid,
Heyf_tmp.deptid,
Heyf_tmp.salary,

IF (

@pdept = Heyf_tmp.deptid, @rank: [email protected] + 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
@pdept: = NULL, @rank: = 0
) A
) result;

For this paragraph I was shy difficult to understand, although the realization of the results of demand, looked at a long time to understand, and now I small changes to use stored procedures to implement

CREATE PROCEDURE Testrank ()
BEGIN
SET @num = 0;
SET @pdept = NULL;
SELECT
Result.empid,
Result.deptid,
Result.salary,
Result.rank
From
(
SELECT
S.empid,
S.deptid,
S.salary,

IF (
@pdept = S.deptid, @num: [email protected] + 1, @num: = 1
) as rank,
@pdept: = S.deptid
From
Heyf_t10 s
ORDER by
S.deptid ASC,
S.salary DESC
) result;

END

Execute statement call Testrank ();

Result diagram:

Another way of thinking is that the authors of the links above are as follows:

SELECT
H. ' Empid ',
H. ' DeptID ',
H. ' Salary ',
COUNT (*) as Rank
From
Heyf_t10 as H
Left OUTER joins 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;

They who are good who poor unclear anyway more a train of thought. This is good.