SQL (Row_number, rank, Dense_rank) written to the Buddies in the group

<span style= "FONT-SIZE:18PX;" > recently has limited energy, and some of the most commonly used things are recorded first. Convenient to consult and summarize later. </span>

A buddy wants to achieve the following effects:

DEPTNO ename                 SAL           TOP3      Department payroll   ----------------------------------------     KING                    5000              1            8750   2     CLARK                  2450              2            8750   2     MILLER                 1300              3            8750   2     SCOTT              1         8975     1     FORD              2         8975    1     JONES                   2975              3         8975    1     BLAKE                   2850              1          5950     3     ALLEN              2         5950      3     TURNER              3         5950     3

as can be seen, he wants to achieve the department's total salary rankings. This is going to use the analytic function that we often say. What the analytic function is, there's not much to say here. Interested friends, please check the official documents yourself.
Here are some of the features we'll implement:First , we need to find out the total salary for each department first, SQL is as follows:

Select Deptno,               ename,               sal,               sum (SAL) over (partition by Deptno order by Deptno) as Sumsal from          EMP

result set of the query:

In the second step, we will implement the final function, with sql:

Select Deptno,       ename,       Sal,       sumsal,       row_number () over (partition by Deptno ORDER by Sal) as "in-sector ranking",      Dense_rank () over (order by  Sumsal) as "department rank" from  (select Deptno,               ename,               sal,               sum (SAL) over ( Partition by Deptno order by Deptno) as sumsal from          EMP)

The result set is as follows:

The effect of the image display is the final result we want. The use effect of the correlation analysis function is attached later. It's here today, to work ....

SQL (Row_number, rank, Dense_rank) written to the Buddies in the group