Shulou(Shulou.com)05/31 Report--

This article mainly explains "MYSQL how to achieve grouping sorting function", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MYSQL how to achieve grouping sorting function" bar!

Because MYSQL does not provide such rich analysis functions as OVER () in ORACLE. So we need to implement this function in MYSQL, and we can only use some flexible methods:

1. First, let's create the instance data:

Drop table if exists heyf_t10

Create table heyf_t10 (empid int, deptid int, salary decimal (10pm 2))

Insert into heyf_t10 values

(1, 10, 10, 5, 500.00)

(2, 10, 10, 4, 500.00)

(3pd20, 1900.00)

(4dint 20pr 4800.00)

(5pr 40pr 6500.00)

(6pas 40pas 14500.00)

(7pr 40pr 44500.00)

(85050500.00)

(95050500.00)

two。 Determine demand: grouped by department, showing that each employee is ranked by salary in the department.

The expected results are as follows:

+-+

| | empid | deptid | salary | rank | |

+-+

| | 1 | 10 | 5500.00 | 1 |

| | 2 | 10 | 4500.00 | 2 |

| | 4 | 20 | 4800.00 | 1 |

| | 3 | 20 | 1900.00 | 2 |

| | 7 | 40 | 44500.00 | 1 | |

| | 6 | 40 | 14500.00 | 2 |

| | 5 | 40 | 6500.00 | 3 |

| | 9 | 50 | 7500.00 | 1 |

| | 8 | 50 | 6500.00 | 2 |

+-+

3. SQL implementation

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

4. Result demonstration

Mysql > 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

->

+-+

| | empid | deptid | salary | rank | |

+-+

| | 1 | 10 | 5500.00 | 1 |

| | 2 | 10 | 4500.00 | 2 |

| | 4 | 20 | 4800.00 | 1 |

| | 3 | 20 | 1900.00 | 2 |

| | 7 | 40 | 44500.00 | 1 | |

| | 6 | 40 | 14500.00 | 2 |

| | 5 | 40 | 6500.00 | 3 |

| | 9 | 50 | 7500.00 | 1 |

| | 8 | 50 | 6500.00 | 2 |

+-+

9 rows in set (0.00 sec)

Thank you for your reading, the above is the content of "how to achieve grouping sorting function in MYSQL". After the study of this article, I believe you have a deeper understanding of how to achieve grouping sorting function in MYSQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.