175、组合两张表
1、题目:
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:FirstName, LastName, City, State
2、代码:
select Person.FirstName,Person.LastName,Address.City,Address.state
from Person left join Address on Person.PersonId = Address.PersonId
3、题解
1)注意审题: 题目说:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息: FirstName, LastName, City, State 也就是说,地址信息(City, State)的查询结果是Null是OK的。但是,姓名(FirstName, LastName)必须有。
2) 为啥不用Where? 因为where的实质就是根据你给的条件(personID相等),选取两表的公共部分。但是,因为PERSON表不是所有人都有地址信息的,但是ADDRESS表只显示有地址信息的人,这样选取出来的就是有地址信息的人,漏掉了没有地址信息的人。所以大家注意,where的本质就是过滤。
3) 如何连接?应该用PERSON表左连接(left join)ADDRESS表,保留person表的所有信息
176、第二高的薪水
1、题目:
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary)命名为SecondHighestSalary
2、代码:
## MySQL解法一
select ifnull((select distinct Salary from Employee
order by Salary desc limit 1 offset 1),null) as SecondHighestSalary
## MySQL解法二
select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary
## SQL解法:用top,没弄懂,但这种方法可行
select max(Salary) as SecondHighestSalary from Employee where Salary<(select max(Salary) from Employee);
3、题解
1)要取出第二高的薪水值,首先对数据进行排序用 order by desc 进行降序排序;
2)由于可能存在重复值采用distinct去重;
3)显示第二高的薪水,可采用limit n offset m
limit 的用法为: select * from tableName limit i,n
i:为查询结果的索引值(默认从0开始),当i=0时可省略i;
n:为查询结果返回的数量;
i与n之间使用英文逗号","隔开
🐷因为去了重,又按顺序排序,使用 limit()方法,查询第二大的数据,即第二高的薪水,即 limit(1,1) (因为默认从0开始,所以第一个1是查询第二大的数,第二个1是表示往后显示多少条数据,这里只需要一条)
4)考虑特殊情况若是第二高的不存在,则会报错,需要采用ifnull(a,b)函数进行判断,ifnull(a,b)表示若存在显示a,不存在显示b的结果。
5)注意题目要求重命名
177、第n高的薪水
1、题目:
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
2、代码:
# MySQL解法一:
select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary )
# MySQL解法二:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1; # limit()方法中不能参与运算,因为索引从0开始,
#所以要 - 1,最好在外面就设定好 set N = N - 1
RETURN (
# Write your MySQL query statement below.
select ifnull((
select distinct Salary
from Employee
order by Salary desc limit N,1),null) as getNthHighestSalary
);
END
3、题解
1)该题与上题类似,不同的是多了个函数,其中主要涉及的知识点如下:
#1、创建函数的格式
create function function_name(para para_type) return return_type
begin
function_body
end
#2、定义变量
declare value value_type
#3、每个语句结束用“;”结尾
2)别名中不能带参数,一开始看到测试用例表,使用的别名是getNthHighestSalary(2),就用了getNthHighestSalary(N)做别名,一开始报错还不知道是哪,后面删去变量即可
limit()方法中不能参与运算,因为索引从0开始,所以要 - 1,最好在外面就设定好 set N = N - 1
3)查找比当前薪资大与等于的个数,个数即排名。distinct使salary相同的排同一个名次。
(select count(distinct Salary) from Employee where Salary >= e.Salary )
1、题目:
2、代码:
3、题解