Database

175 Combine Two Tables (Easy)¶

如下两张表，合并起来

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

返回如下字段

FirstName, LastName, City, State

select Person.FirstName, Person.LastName, Address.City, Address.State
    from Person left join Address
    on Person.PersonId = Address.PersonId
    order by Person.PersonId

176. Second Highest Salary (Easy)¶

Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
如上数据库，应返回
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

思路

注意

select as 可以指定字段名
(SQL)可以起和shell中`Shell`相同的作用

select MAX(Salary) as SecondHighestSalary from Employee 
where Salary not in
(select MAX(Salary) from Employee)

177. Nth Highest Salary (Medium)¶

返回第N高的工资

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

思路

了解如何写函数
如何定义变量
Group by 即按照这个字段不同值分组
ORDER BY DESC 降序排列
LIMIT 1 只输出一个
OFFSET N 去除前N位

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

178. Rank Scores (Medium)¶

SELECT Score,
(SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score) Rank 
FROM Scores s ORDER BY Score DESC;