Leetcode中的SQL题目练习(二)
阅读原文时间:2023年07月10日阅读:1

175. Combine Two Tables

https://leetcode.com/problems/combine-two-tables/description/

Person 表:

Column Name

Type

PersonId

int

FirstName

varchar

LastName

varchar

Address 表:

Column Name

Type

AddressId

int

PersonId

int

City

varchar

State

varchar

查找 FirstName, LastName, City, State 数据,而不管一个用户有没有填地址信息。

solution

SELECT
    FirstName,
    LastName,
    City,
    State
FROM
    Person P
    LEFT JOIN Address A
    ON P.PersonId = A.PersonId;

181. Employees Earning More Than Their Managers ??

https://leetcode.com/problems/employees-earning-more-than-their-managers/description/

Employee 表:

Id

Name

Salary

ManagerId

1

Joe

70000

3

2

Henry

80000

4

3

Sam

60000

NULL

4

Max

90000

NULL

查找薪资大于其经理薪资的员工信息。

solution

 SELECT
    E1.NAME AS Employee
FROM
    Employee E1
    INNER JOIN Employee E2
    ON E1.ManagerId = E2.Id
    AND E1.Salary > E2.Salary;

183. Customers Who Never Order

https://leetcode.com/problems/customers-who-never-order/description/

Curstomers 表:

Id

Name

1

Joe

2

Henry

3

Sam

4

Max

Orders 表:

Id

CustomerId

1

3

2

1

查找没有订单的顾客信息:

Customers

Henry

Max

soulution:

(1)子查询

SELECT
   Name AS Customers
FROM
   Customers
WHERE
   Id NOT IN  (SELECT CustomerId FROM Orders)

(2)

SELECT
    C.Name AS Customers
FROM
    Customers C
    LEFT JOIN Orders O
    ON C.Id = O.CustomerId
WHERE
    O.CustomerId IS NULL;

184. Department Highest Salary ???

https://leetcode.com/problems/department-highest-salary/description/

Employee 表:

Id

Name

Salary

DepartmentId

1

Joe

70000

1

2

Henry

80000

2

3

Sam

60000

2

4

Max

90000

1

Department 表:

Id

Name

1

IT

2

Sales

查找一个 Department 中收入最高者的信息:

Department

Employee

Salary

IT

Max

90000

Sales

Henry

80000

soulution:

SELECT
    D.NAME Department,
    E.NAME Employee,
    E.Salary
FROM
    Employee E,
    Department D,
    ( SELECT DepartmentId, MAX( Salary ) Salary FROM Employee GROUP BY DepartmentId ) M
WHERE
    E.DepartmentId = D.Id
    AND E.DepartmentId = M.DepartmentId
    AND E.Salary = M.Salary;

176. Second Highest Salary ??

https://leetcode.com/problems/second-highest-salary/description/

Id

Salary

1

100

2

200

3

300

查找工资第二高的员工。

solution

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

177. Nth Highest Salary

https://leetcode.com/problems/nth-highest-salary/

Description

查找工资第 N 高的员工。

CREATE FUNCTION getNthHighestSalary ( N INT )
        RETURNS INT
        BEGIN
            SET N = N - 1;
             RETURN (
                    SELECT (
                        SELECT DISTINCT Salary
                        FROM Employee
                        ORDER BY Salary DESC
                        LIMIT N, 1 )
             );
        END

178. Rank Scores ??

https://leetcode.com/problems/rank-scores/description/

Description:

Id

Score

1

3.50

2

3.65

3

4.00

4

3.85

5

4.00

6

3.65

将得分排序,并统计排名。

Score

Rank

4.00

1

4.00

1

3.85

2

3.65

3

3.65

3

3.50

4

Solution:

(1)解法一:对于每一个分数,从表中找出有多少个大于或等于该分数的不重复分数,然后降序排列

select Score,
(select count(distinct Score) from Scores s2 where s2.Score >= s1.Score) Rank
from Scores  s1
order by Score DESC;

(2)解法二:使用联结 join,条件是左表的分数小于等于右表的分数时,对右表的分数进行计数(即计算有几个不重复的分数大于自己,计算结果就是rank),然后根据id分组后,再根据分数降序排列

select s1.Score, count(distinct s2.Score) Rank
from Scores as s1 inner join Scores as s2 on s1.Score <= s2.Score
group by s1.Id
order by s1.Score DESC;

180. Consecutive Numbers

https://leetcode.com/problems/consecutive-numbers/description/

数字表:

Id

Num

1

1

2

1

3

1

4

2

5

1

6

2

7

2

编写一个 SQL 查询,查找所有至少连续出现三次的数字。

ConsecutiveNums

1

solution:

(1)用l1分别和l2, l3内交,l1和l2的Id下一个位置比,l1和l3的下两个位置比,然后将Num都相同的数字返回即可

select distinct l1.Num ConsecutiveNums from Logs l1
join Logs l2 on l1.Id = l2.Id - 1
join Logs l3 on l1.Id = l3.Id - 2
where l1.Num = l2.Num and l2.Num = l3.Num;

(2)直接在三个表的实例中查找,然后把四个条件限定上,就可以返回正确结果了 ??

SELECT DISTINCT l1.Num FROM Logs l1, Logs l2, Logs l3
WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num AND l2.Num = l3.Num;

626. Exchange Seats ???

https://leetcode.com/problems/exchange-seats/description/

seat 表存储着座位对应的学生。

id

student

1

Abbot

2

Doris

3

Emerson

4

Green

5

Jeames

要求交换相邻座位的两个学生,如果最后一个座位是奇数,那么不交换这个座位上的学生。

id

student

1

Doris

2

Abbot

3

Green

4

Emerson

5

Jeames

solution

对照上表及其查询结果可以得知,当原id为奇数时,交换座位后的id变为id+1,当原id为偶数时,交换座位后的id变为id-1,另一个方面需要考虑的是,学生人数为奇数时,最后一个学生的id不变,故应当用子查询确定学生的人数,然后分情况讨论即可

select (case
      when mod(id,2)!=0 and id!=counts then id+1
      when mod(id,2)!=0 and id=counts then id
      else id-1 end)as id,student
      from seat,(select count(*)as counts from seat)as seat_counts
                order by id;