MySQL练习题
连接查询
175 组合两个表
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int | Priamry key
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int | Priamry key
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
分析
本题主要考察的是连接查询,它的要求是无论Address表中是否有对应Person的地址,都需要将城市City和居住地State表示出来,为空(NULL)也可以。sq199语法支持内连接和外连接,两个表进行组合,需要的是外连接,并且Person表的信息需要全部保留,而Address表对Person表进行匹配,那么Person 表应该为主表,Address 表为从表,使用 left join
或 right join
,匹配条件即 p.PersonId = a.PersonId
。
代码如下
# 执行用时:262 ms, 在所有 MySQL 提交中击败了 64.61% 的用户
# 内存消耗:0 B, 在所有 MySQL 提交中击败了 100.00% 的用户
select p.FirstName,p.LastName,a.City,a.State
from Person p
left join Address a
on p.PersonId = a.PersonId;
子查询
176 第二高的薪水
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
分析
本题是查询第二高薪水,那么很自然就会想到排序,然后用 limit 分页查询限制输出数据。这里我没有考虑到去重。不过题目的难点可能在于没有第二高薪水就要输出NULL,正常的查询语句没有查询到结果是不会输出NULL值的,官方题解有两种思路,利用子查询或者IFNULL函数。
SQL语句查询为空时如何输出NULL?
select null as c;
将NULL值用对应的select语句替换即可IFNULL(expression_1,expression_2);
如果expression_1不为NULL,则IFNULL函数返回expression_1; 否则返回expression_2的结果。
# 执行用时:157 ms, 在所有 MySQL 提交中击败了 81.50% 的用户
# 内存消耗:0 B , 在所有 MySQL 提交中击败了 100.00% 的用户
select
(select DISTINCT salary
from Employee
order by salary DESC
limit 1,1)
as SecondHighestSalary ;
或
# 执行用时:160 ms, 在所有 MySQL 提交中击败了 71.54% 的用户
# 内存消耗:0 B, 在所有 MySQL 提交中击败了 100.00% 的用户
# IFNULL函数中用逗号分隔表达式,若表达式过长,用括号
select
IFNULL((select DISTINCT salary
from Employee
order by salary DESC
limit 1,1),NULL)
as SecondHighestSalary ;
存储函数
177 第N高的薪水
注意:
- 由于无法直接用limit N-1,所以需先在函数开头处理N为N=N-1。
这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数(意味着0、负数、小数都不行)或者单一变量(意味着不能用表达式)
/*
执行用时:289 ms, 在所有 MySQL 提交中击败了 40.07% 的用户
内存消耗: 0 B, 在所有 MySQL 提交中击败了 100.00% 的用户
*/
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
# Write your MySQL query statement below.
select distinct salary
from Employee
order by salary desc
limit N,1
);
END