第一题
现有表Person,字段为PersonId、FirstName、LastName;表Address,字段为Address、PersonId、City、State
要求:请编写一个SQL查询,满足条件:无论Person是否有地址信息,都需要基于两表提供Person的FirstName、 LastName、 City、 State
select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId;
解析:当Address无地址信息时,仍然需要提供上述信息,所以需要使用left join,当使用join时,条件关键字需要使用on
第二题
现有表Employee,字段为Id、Salary
要求:显示第二高的薪水,如果不存在第二高的薪水,那么查询应返回Null值
select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset 1), null) as SecondHighestSalary;
解析:当使用排序时,select关键字后需要使用distinct进行去重,对于空值筛选可以使用ifnull(值1,值2),表示值1为空时,显示值2的数值
第三题
现有表Employee,字段为Id、Salary
要求:显示第N高的薪水,如果不存在第N高的薪水,则返回Null值
create function getNthHighestSalary(N INT) returns int
begin
declare m int;
set m = N-1;
return(
select ifull((select distinct Salary from Employee order by Salary limit m, 1), null) as getNthHighestSalary
);
end
解析:在limit中无法进行计算,所以如果需要计算需要提前定义变量,定义变量需要使用declare关键字进行声明,随后使用set进行赋值
第四题
现有表Scores,字段为Id、Score
要求:请为分数排名,排名连续,相同成绩相同拍醒
select Score, (select count(distinct score) from Scores where score>=s.score) rank from Scores as s order by score desc;
解析:相对分数进行排序,随后逐个对其进行排名比较赋值
第五题
现有表Person,字段为Id、Email
要求:获取Person中的重复邮箱
select Email from Person group by Email having count(Email)>1;
解析:当使用group by 、order by 之后使用条件筛选时,需要使用having