Leetcode中的SQL题目练习（二）

175. Combine Two Tables

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

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/

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/

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/

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/

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/

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;