Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf53ed21

Browse files
add 185
1 parent546c252 commitf53ed21

File tree

2 files changed

+43
-0
lines changed

2 files changed

+43
-0
lines changed

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -645,6 +645,7 @@ Your ideas/fixes/algorithms are more than welcome!
645645
|262|[Trips and Users](https://leetcode.com/problems/trips-and-users/)|[Solution](../master/database/_262.sql)||| Hard| Inner Join
646646
|197|[Rising Temperature](https://leetcode.com/problems/rising-temperature/)|[Solution](../master/database/_197.sql)| O(n^2)|O(n)| Easy|
647647
|196|[Delete Duplicate Emails](https://leetcode.com/problems/delete-duplicate-emails/)|[Solution](../master/database/_196.sql)| O(n^2)|O(n)| Easy|
648+
|185|[Department Top Three Salaries](https://leetcode.com/problems/department-top-three-salaries)|[Solution](../master/database/_185.sql)||| Hard|
648649
|184|[Department Highest Salary](https://leetcode.com/problems/department-highest-salary)|[Solution](../master/database/_184.sql)| O(n^2)|O(n)| Medium|
649650
|183|[Customers Who Never Order](https://leetcode.com/problems/customers-who-never-order/)|[Solution](../master/database/_183.sql)| O(n^2)|O(n)| Easy|
650651
|182|[Duplicate Emails](https://leetcode.com/problems/duplicate-emails/)|[Solution](../master/database/_182.sql)| O(n^2)|O(n)| Easy|

‎database/_185.sql

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
--185. Department Top Three Salaries
2+
-- The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
3+
--
4+
--+----+-------+--------+--------------+
5+
--| Id | Name | Salary | DepartmentId |
6+
--+----+-------+--------+--------------+
7+
--| 1 | Joe | 70000 | 1 |
8+
--| 2 | Henry | 80000 | 2 |
9+
--| 3 | Sam | 60000 | 2 |
10+
--| 4 | Max | 90000 | 1 |
11+
--| 5 | Janet | 69000 | 1 |
12+
--| 6 | Randy | 85000 | 1 |
13+
--+----+-------+--------+--------------+
14+
--
15+
--The Department table holds all departments of the company.
16+
--
17+
--+----+----------+
18+
--| Id | Name |
19+
--+----+----------+
20+
--| 1 | IT |
21+
--| 2 | Sales |
22+
--+----+----------+
23+
--
24+
--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.
25+
--
26+
--+------------+----------+--------+
27+
--| Department | Employee | Salary |
28+
--+------------+----------+--------+
29+
--| IT | Max | 90000 |
30+
--| IT | Randy | 85000 |
31+
--| IT | Joe | 70000 |
32+
--| Sales | Henry | 80000 |
33+
--| Sales | Sam | 60000 |
34+
--+------------+----------+--------+
35+
--
36+
37+
selectD.Nameas Department,E.Nameas Employee,E.Salaryas Salary
38+
from Department D, Employee E
39+
where (select(count(distinct(salary)))from Employee
40+
where DepartmentId=E.DepartmentIdand Salary>E.Salary)<3
41+
andE.DepartmentId=D.Id
42+
order byE.DepartmentId,E.Salarydesc;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp