编程知识 cdmana.com

Leetcode problem solving series 185. All employees with the top three salaries in the Department (Oracle dense)_ Rank function)

️ The original title is ️

Employee Table contains all employee information , Each employee has his / her work number Id, full name Name, Wages Salary And department number DepartmentId .

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

Department The table contains information about all departments of the company .

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

Write a SQL Inquire about , Find out all the employees who get the top three salaries in each department . for example , According to the table given above , Query results should be returned :

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

explain :

IT In the Department ,Max Got the highest wage ,Randy and Joe All got the second highest salary ,Will The third highest salary . Sales Department (Sales) There are only two employees ,Henry The highest salary ,Sam The second highest salary .

️ Their thinking ️

In order to express more clearly , I build test environment data in a local test environment .

Build test data

--create table
CREATE TABLE employee (ID NUMBER,NAME VARCHAR2(20),salary NUMBER,departmentid NUMBER);
CREATE TABLE department (ID NUMBER,NAME VARCHAR2(20));
--insert data
INSERT INTO employee VALUES (1,'Joe',85000,1);
INSERT INTO employee VALUES (2,'Henry',80000,2);
INSERT INTO employee VALUES (3,'Sam',60000,2);
INSERT INTO employee VALUES (4,'Max',90000,1);
INSERT INTO employee VALUES (5,'Janet',69000,1);
INSERT INTO employee VALUES (6,'Randy',85000,1);
INSERT INTO employee VALUES (7,'Will',70000,1);

INSERT INTO department VALUES (1,'IT');
INSERT INTO department VALUES (2,'Sales');
commit;

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

️ Start solving the problem

It's about ranking again , Or the same score and ranking , I can't help thinking of dense_rank() function , However, the way of use this time is different from the previous two articles , Because the result set needs to be partitioned , Therefore, we also need the function's <partition_by_clause>.

For the usage of this function, please refer to : dense_rank() function

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

      
  • 1.

First , We need to associate the two tables :

SELECT d.name   dname,
       e.name   AS ename,
       e.salary
  FROM employee   e,
       department d
 WHERE e.departmentid = d.id

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

 Insert picture description here
Next , Need to use dense_rank() Function to sort , And group the result set :

SELECT d.name dname,
       e.name AS ename,
       e.salary,
       dense_rank() over(PARTITION BY e.departmentid ORDER BY e.salary DESC) dr
  FROM employee   e,
       department d
 WHERE e.departmentid = d.id

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

 Insert picture description here
With the above results, the problem has been solved , Finally, you only need to take the first three results :

The complete code is as follows :

SELECT t.dname  AS "Department",
       t.ename  AS "Employee",
       t.salary AS "Salary"
  FROM (SELECT d.name dname,
               e.name AS ename,
               e.salary,
               dense_rank() over(PARTITION BY e.departmentid ORDER BY e.salary DESC) dr
          FROM employee   e,
               department d
         WHERE e.departmentid = d.id) t
 WHERE t.dr < 4;

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

 Insert picture description here
Go to LeetCode Execute it and see the results :
 Insert picture description here

️ At the end ️

I have written three questions about ranking , It seems LeetCode I like to ask questions about ranking , Everyone should be right about dense_rank() Function is well understood .


This sharing is over ~

If you think the article will help you , give the thumbs-up 、 Collection 、 Focus on 、 Comment on , One button four connection support , Your support is the biggest motivation for my creation .

版权声明
本文为[Lucifer, think twice]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/08/20210804210605402t.html

Scroll to Top