[LeetCode] 185. Department Top Three Salaries
發表更新LeetCode3 分鐘讀完 (大約412個字)

[LeetCode] 185. Department Top Three Salaries

問題描述

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

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

The Department table holds all departments of the company.

Id Name
1 IT
2 Sales

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

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

翻譯

  • Employee 表格紀錄員工資訊，每位員工擁有 Id, 及所屬部門代碼。
  • Department 表格紀錄公司所有部門資訊。
  • 請撰寫一段 SQL 查詢，找出各部門業績排行的前三名，同名並列。

解題思維

利用 DENSE_RANK()函數以部門代號切割，業績從高至低排名
最後撈取前三名(含)，就可得出答案

解題報告

Level: Hard
Runtime: 1173 ms, faster than 80.22% of Oracle online submissions for Department Top Three Salaries.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Department Top Three Salaries.

程式完整解題

1
2
3
4
5
6
7
8
9
10
11
12
/* Write your PL/SQL query statement below */
SELECT DEPARTMENT
      ,EMPLOYEE
      ,SALARY
  FROM(SELECT DP.NAME   AS DEPARTMENT
             ,EM.NAME   AS EMPLOYEE
             ,EM.SALARY AS SALARY
             ,DENSE_RANK() OVER(PARTITION BY EM.DEPARTMENTID ORDER BY EM.SALARY DESC) AS RANK
         FROM EMPLOYEE   EM
         JOIN DEPARTMENT DP
           ON EM.DEPARTMENTID = DP.ID)
 WHERE RANK <= 3;

SQL Schema

1
2
3
4
5
6
7
8
9
10
11
12
13
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '85000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Janet', '69000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('6', 'Randy', '85000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('7', 'Will', '70000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')
[LeetCode] 262. Trips and Users
發表更新LeetCode7 分鐘讀完 (大約1007個字)

[LeetCode] 262. Trips and Users

問題描述

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

Id Client_Id Driver_Id City_Id Status Request_at
1 1 10 1 completed 2013-10-01
2 2 11 1 cancelled_by_driver 2013-10-01
3 3 12 6 completed 2013-10-01
4 4 13 6 cancelled_by_client 2013-10-01
5 1 10 1 completed 2013-10-02
6 2 11 6 completed 2013-10-02
7 3 12 6 completed 2013-10-02
8 2 12 12 completed 2013-10-03
9 3 10 12 completed 2013-10-03
10 4 13 12 cancelled_by_driver 2013-10-03

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

Users_Id Banned Role
1 No client
2 Yes client
3 No client
4 No client
10 No driver
11 No driver
12 No driver
13 No driver

Write a SQL query to find the cancellation rate of requests made by unbanned users (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.

For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

Day Cancellation Rate
2013-10-01 0.33
2013-10-02 0.00
2013-10-03 0.50

翻譯

  • Trips 表格紀錄所有計程車的乘車紀錄，每一筆資料 Id 為唯一值, 其中 Client_Id, Driver_Id 為外部鍵，參照 Users 表格的 Users_Id，Status 為列舉型態，表示三種狀態，分別為”完成乘車”、”司機取消載客”、”乘客取消乘車”。
  • Users 表格紀錄所有使用者，每一筆紀錄 Users_Id 為唯一值，其中 Role 欄位為列舉型態，分別為”司機”、”乘客”、”夥伴”。
  • 請撰寫一段 SQL 查詢，找出 2013 年 10 月 1 日至 2013 年 10 月 3 日三日的當日乘車取消率，計算公式如下。
  • 當日由”司機取消載客”、”乘客取消乘車”乘車紀錄次數 / 當日乘車紀錄總次數，且司機與乘客不得為禁用狀態。

解題思維

TRIPS 以 JOIN 方式關聯 Users 兩次，條件分別為乘客、司機非禁用狀態，時間篩選在 2013 年 10 月 1 日至 2013 年 10 月 3 日三日
再 COUNT, SUM 函數分別對第一步驟的結果計算出乘車紀錄總次數、取消乘車紀錄次數
最後使用 GROUP BY 方式，就可以得出三日當日的乘車取消率

解題報告

Level: Hard
Runtime: 796 ms, faster than 82.29% of Oracle online submissions for Trips and Users.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Trips and Users.

程式完整解題

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
/* Write your PL/SQL query statement below */
WITH TAXI AS(SELECT  TRIPS.STATUS       AS STATUS
                    ,TRIPS.REQUEST_AT   AS REQUESTED_DATE
               FROM TRIPS
               JOIN Users CLIENTS
                 ON (CLIENTS.Users_Id = TRIPS.CLIENT_ID)
                AND CLIENTS.BANNED = 'No'
               JOIN Users DRIVERS
                 ON (DRIVERS.Users_Id = TRIPS.CLIENT_ID)
                AND DRIVERS.BANNED = 'No'
              WHERE TO_DATE(TRIPS.REQUEST_AT, 'YYYY-MM-DD') BETWEEN TO_DATE('2013-10-01', 'YYYY-MM-DD') AND TO_DATE('2013-10-03', 'YYYY-MM-DD'))

SELECT TOTAL_REQ.REQ_DATE                                    AS "Day"
      ,ROUND(CANCELLEDL_REQ.TIMES *1.0/ TOTAL_REQ.TIMES, 2)  AS "Cancellation Rate"
  FROM  (SELECT REQUESTED_DATE AS REQ_DATE
               ,COUNT(*)       AS TIMES
          FROM TAXI
          GROUP BY REQUESTED_DATE) TOTAL_REQ
  JOIN(SELECT REQUESTED_DATE  AS REQ_DATE
             ,SUM(CASE WHEN (STATUS = 'cancelled_by_client' OR STATUS = 'cancelled_by_driver') THEN 1
                           ELSE 0 END)      AS TIMES
         FROM TAXI
        GROUP BY REQUESTED_DATE) CANCELLEDL_REQ
    ON TOTAL_REQ.REQ_DATE = CANCELLEDL_REQ.REQ_DATE
 ORDER BY TOTAL_REQ.REQ_DATE

SQL Schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), Request_at varchar(50))
Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client', 'driver', 'partner'))
Truncate table Trips
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03')
Truncate table Users
insert into Users (Users_Id, Banned, Role) values ('1', 'No', 'client')
insert into Users (Users_Id, Banned, Role) values ('2', 'Yes', 'client')
insert into Users (Users_Id, Banned, Role) values ('3', 'No', 'client')
insert into Users (Users_Id, Banned, Role) values ('4', 'No', 'client')
insert into Users (Users_Id, Banned, Role) values ('10', 'No', 'driver')
insert into Users (Users_Id, Banned, Role) values ('11', 'No', 'driver')
insert into Users (Users_Id, Banned, Role) values ('12', 'No', 'driver')
insert into Users (Users_Id, Banned, Role) values ('13', 'No', 'driver')
[LeetCode]180. Consecutive Numbers
發表更新LeetCode2 分鐘讀完 (大約257個字)

[LeetCode]180. Consecutive Numbers

問題描述

Write a SQL query to find all numbers that appear at least three times consecutively.

Id Num
1 1
2 1
3 1
4 2
5 1
6 2
7 2

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

ConsecutiveNums
1

翻譯

請寫出一段 SQL 查詢能找出所有連續出現三次以上的數字

解題思維

利用 LEAD(), LAG() Oracle 分析函數可分別得出該數字的上下數字，再判斷上下數字、與該數字都是否相等，即為答案。

解題報告

Level: Medium
Runtime: 678 ms, faster than 98.93% of Oracle online submissions for Consecutive Numbers.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Consecutive Numbers.

程式完整解題

1
2
3
4
5
6
7
8
9
/* Write your PL/SQL query statement below */
SELECT NUM AS ConsecutiveNums
  FROM(SELECT NUM
             ,LEAD(NUM) OVER (ORDER BY ID) AS NEXT_NUM
             ,LAG(NUM)  OVER (ORDER BY ID) AS LAST_NUM
         FROM Logs)
 WHERE NEXT_NUM = LAST_NUM
   AND NEXT_NUM = NUM
 GROUP BY NUM;

SQL Schema

1
2
3
4
5
6
7
8
9
Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')
[Oracle SQL] rank()分析函數用法
發表更新Oracle SQL7 分鐘讀完 (大約1110個字)

[Oracle SQL] rank()分析函數用法

分析函數簡介

RANK(), DENSE_RANK(), ROW_RANK()都是 Oracle SQL 的分析函數，可根據分群、排序依據分配序列給每一筆資料，可以套用在成績、業績排名、或是群組內排名等等，以下使用員工薪水作為範例，範例會有兩張表格分別為 EMPLOYEE, DEPARTMENT，實作部分會告訴各位三種函數有何差別。

TABLE SCHEMA

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE DEPARTMENT (
  DEPT_ID     NUMBER            NOT NULL
, DEPT_NAME   VARCHAR2(10 CHAR)
, CONSTRAINT  DEPARTMENT_PK PRIMARY KEY(DEPT_ID)
);

CREATE TABLE EMPLOYEE (
  EMPLOYEE_ID NUMBER NOT NULL
, DEPT_ID     NUMBER NOT NULL
, NAME        VARCHAR2(20 CHAR)
, SALARY      NUMBER
, CONSTRAINT EMPLOYEE_PK   PRIMARY KEY (EMPLOYEE_ID)
, CONSTRAINT DEPARTMENT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);

INSERT INTO DEPARTMENT VALUES( 1, 'IT');
INSERT INTO DEPARTMENT VALUES( 2, 'ACCT');
INSERT INTO DEPARTMENT VALUES( 3, 'MAINT' );

INSERT INTO EMPLOYEE VALUES( 1, 1, 'Terry' , 50000);
INSERT INTO EMPLOYEE VALUES( 2, 1, 'Emily' , 50000);
INSERT INTO EMPLOYEE VALUES( 3, 2, 'Joyce' , 38600);
INSERT INTO EMPLOYEE VALUES( 4, 2, 'Bob'   , 32800);
INSERT INTO EMPLOYEE VALUES( 5, 2, 'Phoebe', 32800);
INSERT INTO EMPLOYEE VALUES( 6, 3, 'Olive' , 28000);
COMMIT;

語法使用

ROW_NUM()、RANK()、DENSE_RANK()是分析函數用來分派序列給每一筆資料，ORDER BY 欄位名稱 [DESC|ASC]指的是要依據什麼欄位進行遞減[增]排序，此為必填子句，[PARTITION BY 欄位名稱]指的是要依照什麼欄位進行分群，例如：班級、單位內排序，此為選填子句，若沒有這段子句，則會針對所有的資料分派序列。

1
2
[ROW_NUM()|RANK()|DENSE_RANK()]
OVER([PARTITION BY 欄位名稱] ORDER BY 欄位名稱 [DESC|ASC])

RANK()使用方法

範例是依據員工薪水高低做排名，若排序資料值趨近一樣，RANK()函數會將排序名次並列，並列後的排名則佔用名次，再往後排序。

1
2
3
4
5
SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

查詢結果

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SALARY SALARY_RANK
1 Terry 50000 1
2 Emily 50000 1
3 Joyce 38600 3
4 Bob 32800 4
5 Phoebe 32800 4
6 Olive 28000 6

DENSE_RANK()使用方法

DENSE_RANK()函數與 RANK()函數使用方式很相似，兩者差別在於並列後的名次並不會佔用。

1
2
3
4
5
SELECT EM.EMPLOYEE_ID                              AS EMPLOYEE_ID
      ,EM.NAME                                     AS EMPLOYEE_NAME
      ,EM.SALARY                                   AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

查詢結果

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SALARY SALARY_RANK
1 Terry 50000 1
2 Emily 50000 1
3 Joyce 38600 2
4 Bob 32800 3
5 Phoebe 32800 3
6 Olive 28000 4

ROW_NUMBER()使用方法

ROW_NUMBER()函數是並不考慮排序並列的情況，序列依序分派下來。

1
2
3
4
5
SELECT EM.EMPLOYEE_ID                              AS EMPLOYEE_ID
      ,EM.NAME                                     AS EMPLOYEE_NAME
      ,EM.SALARY                                   AS EMPLOYEE_SALARY
      ,ROW_NUMBER() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

查詢結果

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SALARY SALARY_RANK
1 Terry 50000 1
2 Emily 50000 2
3 Joyce 38600 3
4 Bob 32800 4
5 Phoebe 32800 5
6 Olive 28000 6

分群排名

以下範例依據部門分群排名

1
2
3
4
5
6
7
8
SELECT EM.EMPLOYEE_ID                                                      AS EMPLOYEE_ID
      ,EM.NAME                                                             AS EMPLOYEE_NAME
      ,DP.DEPT_NAME                                                        AS DEPT_NAME
      ,EM.SALARY                                                           AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (PARTITION BY EM.DEPT_ID ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM
 INNER JOIN DEPARTMENT DP
         ON EM.DEPT_ID = DP.DEPT_ID;

查詢結果

EMPLOYEE_ID EMPLOYEE_NAME DEPT_NAME EMPLOYEE_SALARY SALARY_RANK
1 Terry IT 50000 1
2 Emily IT 50000 1
3 Joyce ACCT 38600 1
4 Bob ACCT 32800 2
5 Phoebe ACCT 32800 2
6 Olive MAINT 28000 1

完整程式碼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
CREATE OR REPLACE PROCEDURE PROC_DROP_TAB_IF_EXISTS(
    v_table_name IN VARCHAR2 --TABLE NAME
) IS
    --INITIALIZE ROW NUMBER VARIABLE
    v_count NUMBER;
BEGIN
    v_count := 0;
    SELECT COUNT(1)
      INTO v_count
      FROM USER_TABLES
     WHERE TABLE_NAME = v_table_name;

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 'DROP TABLE '|| v_table_name ||' CASCADE CONSTRAINTS PURGE';
    END IF;
END;

EXECUTE PROC_DROP_TAB_IF_EXISTS('DEPARTMENT');
EXECUTE PROC_DROP_TAB_IF_EXISTS('EMPLOYEE');


CREATE TABLE DEPARTMENT (
  DEPT_ID     NUMBER            NOT NULL
, DEPT_NAME   VARCHAR2(10 CHAR)
, CONSTRAINT  DEPARTMENT_PK PRIMARY KEY(DEPT_ID)
);

CREATE TABLE EMPLOYEE (
  EMPLOYEE_ID NUMBER NOT NULL
, DEPT_ID     NUMBER NOT NULL
, NAME        VARCHAR2(20 CHAR)
, SALARY      NUMBER
, CONSTRAINT EMPLOYEE_PK   PRIMARY KEY (EMPLOYEE_ID)
, CONSTRAINT DEPARTMENT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);

INSERT INTO DEPARTMENT VALUES( 1, 'IT');
INSERT INTO DEPARTMENT VALUES( 2, 'ACCT');
INSERT INTO DEPARTMENT VALUES( 3, 'MAINT' );

INSERT INTO EMPLOYEE VALUES( 1, 1, 'Terry' , 50000);
INSERT INTO EMPLOYEE VALUES( 2, 1, 'Emily' , 50000);
INSERT INTO EMPLOYEE VALUES( 3, 2, 'Joyce' , 38600);
INSERT INTO EMPLOYEE VALUES( 4, 2, 'Bob'   , 32800);
INSERT INTO EMPLOYEE VALUES( 5, 2, 'Phoebe', 32800);
INSERT INTO EMPLOYEE VALUES( 6, 3, 'Olive' , 28000);
COMMIT;

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,ROW_NUMBER() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

SELECT EM.EMPLOYEE_ID                                                      AS EMPLOYEE_ID
      ,EM.NAME                                                             AS EMPLOYEE_NAME
      ,DP.DEPT_NAME                                                        AS DEPT_NAME
      ,EM.SALARY                                                           AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (PARTITION BY EM.DEPT_ID ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM
 INNER JOIN DEPARTMENT DP
         ON EM.DEPT_ID = DP.DEPT_ID;

參考

Oracle ROW_NUMBER

https://www.oracletutorial.com/oracle-analytic-functions/oracle-row_number/

Oracle PL/SQL: Rank 排名次查詢

https://tomkuo139.blogspot.com/2009/04/plsql-rank.html

Oracle 中 rank() over, dense_rank(), row_number() 的区别

https://blog.csdn.net/baidu_37107022/article/details/78033513

[LeetCode]178. Rank Scores
發表更新LeetCode3 分鐘讀完 (大約390個字)

[LeetCode]178. Rank Scores

問題描述

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

Id Score
1 3.50
2 3.65
3 4.00
4 3.85
5 4.00
6 3.65

For example, given the above Scores table, your query should generate the following report (order by highest score):

score Rank
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4

Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example Rank.

翻譯

請撰寫一段 SQL 查詢排名以下分數，若兩個分數趨近一樣，將並列為同一名次，下一個名次取次排名，換言之，並列後的分數後不會空出排名。
您必須產出如以下的依據分數從高至低的查詢結果。
注意：如果是以 MySql 作答，你可以在 Rank 前後加入撇號(`)來跳脫保留字當作是欄位名稱。

解題思維

用法： [ROW_NUM()|RANK()|DENSE_RANK()] OVER([PARTITION BY 欄位名稱] ORDER BY 欄位名稱 [DESC|ASC])
解題是利用 Oracle 分析函數進行排名，詳細可參考[Oracle SQL] rank(), dense_rank(), row_number()分析函數用法

解題報告

Level: Medium
Runtime: 544 ms, faster than 93.42% of Oracle online submissions for Rank Scores.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Rank Scores.

程式完整解題

1
2
3
4
/* Write your PL/SQL query statement below */
SELECT SCORE
      ,DENSE_RANK() OVER (ORDER BY SCORE DESC) AS RANK
  FROM SCORES;

SQL Schema

1
2
3
4
5
6
7
8
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))
Truncate table Scores
insert into Scores (Id, Score) values ('1', '3.5')
insert into Scores (Id, Score) values ('2', '3.65')
insert into Scores (Id, Score) values ('3', '4.0')
insert into Scores (Id, Score) values ('4', '3.85')
insert into Scores (Id, Score) values ('5', '4.0')
insert into Scores (Id, Score) values ('6', '3.65')
[LeetCode]184. Department Highest Salary
發表更新LeetCode3 分鐘讀完 (大約523個字)

[LeetCode]184. Department Highest Salary

問題描述

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

Id Name Salary DepartmentId
1 Joe 70000 1
2 Jim 90000 1
3 Henry 80000 2
4 Sam 60000 2
5 Max 90000 1

The Department table holds all departments of the company.

Id Name
1 IT
2 Sales

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

Department Employee Salary
IT Max 90000
IT Jim 90000
Sales Henry 80000

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

翻譯

Employee 這張資料表擁有所有員工資訊，每一筆資料包含員工編號、名稱、薪水、以及部門代碼
Department 這張資料表擁有公司部門資訊
請撰寫一段 SQL 查詢能找出各個部門薪資最高的員工，並參考以下查詢結果格式作為解答標準（次序非必要條件）。

解題思維

  1. 部門代號可以利用 JOIN 方式將 Employee 與 Department 兩張作關聯，如此以來就可以取得部門名稱
  2. 要取得部門中最高薪資，使用 Group By 部門方式，則可以取到該部門最高薪資
  3. 最後  將 1.查詢結果 JOIN2.，以薪水、部門代號作為關聯條件，就可以得出答案

解題報告

Level: Medium
Runtime: 983 ms, faster than 89.78% of Oracle online submissions for Department Highest Salary.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Department Highest Salary.

程式完整解題

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/* Write your PL/SQL query statement below */

SELECT DP.NAME   AS DEPARTMENT -- 部門名稱
      ,EM.NAME   AS EMPLOYEE   -- 員工名稱
      ,EM.SALARY AS SALARY     -- 員工薪水
  FROM EMPLOYEE EM
 INNER JOIN DEPARTMENT DP
         ON EM.DEPARTMENTID = DP.ID
 INNER JOIN (SELECT    DEPARTMENTID
                      ,MAX(SALARY)   AS SALARY
               FROM    EMPLOYEE
              GROUP BY DEPARTMENTID) MAX_SALARY
         ON EM.SALARY = MAX_SALARY.SALARY
        AND EM.DEPARTMENTID = MAX_SALARY.DEPARTMENTID;

SQL Schema

1
2
3
4
5
6
7
8
9
10
11
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')
追蹤

分類

最新文章

彙整

標籤

Array3
Azure DevOps1
BlockChain1
Blog1
C++1
Database1
Flask5
Git1
GitHub1
Heroku1
Hexo2
Image Processing1
Java10
LikeCoin1
LinkedList1
Number1
PL/SQL6
PostgreSQL1
Python5
Queue1
Spring Boot1
Tree1
python1
vscode2
演算法1
軟體工程4

訂閱 Email

×