赞
踩
表: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+personId 是该表的主键列。
该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+addressId 是该表的主键列。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中,则报告为空 null 。
以 任意顺序 返回结果表。
查询结果格式如下所示。
输入:
Person表:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1 | Wang | Allen |
| 2 | Alice | Bob |
+----------+----------+-----------+
Address表:
+-----------+----------+---------------+------------+
| addressId | personId | city | state |
+-----------+----------+---------------+------------+
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
+-----------+----------+---------------+------------+
输出:
+-----------+----------+---------------+----------+
| firstName | lastName | city | state |
+-----------+----------+---------------+----------+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
+-----------+----------+---------------+----------+
解释:
地址表中没有 personId = 1 的地址,所以它们的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息。
- SELECT
- p.firstName,
- p.lastName,
- a.city,
- a.state
- FROM
- person p
- LEFT JOIN address a ON p.PersonId = a.PersonId
解题步骤:
用左外连接,以左表为准,右表不符合条件默认会返回null。比如:PersonId=2的city、state字段都是空的
- SELECT
- p.firstName,
- p.lastName,
- ( SELECT a.city FROM address a WHERE p.PersonId = a.PersonId ) AS city,
- ( SELECT a.state FROM address a WHERE p.PersonId = a.PersonId ) AS state
- FROM
- person p
最佳选择:左外连接,因为子查询会多次遍历数据,而连接查询只遍历一次。
Employee 表:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。
查询结果如下例所示。
示例 1:
输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
输出:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
示例 2:
输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
输出:
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+
- select (
- select DISTINCT salary
- from Employee
- Order by salary desc
- limit 1,1
- ) as SecondHighestSalary ;
根据sql语句的执行顺序,distinct的执行顺序优先于order by,因此distinct对select的字段去重后,order by只能在distinct后返回的结果集进行排序。再limit进行分页查询,就是取第2页的第一行数据。第一个1:第二行(默认第一行为0),第二个1:获取行数。
- select ifnull(
- (select max(salary)
- from Employee
- where salary < (select max(salary) from Employee)
- ),null
- ) as SecondHighestSalary;
where子查询语句中,max聚合获取最大值,再过滤最大值;再select max(salary)此时是在过滤了最大值的结果集上做max聚合,拿到的就是第二大的。
- SELECT
- IFNULL((
- SELECT
- salary
- FROM
- ( SELECT DISTINCT ( salary ), dense_rank ( ) over ( ORDER BY salary DESC ) AS ranking FROM Employee ) AS rankTable
- WHERE
- ranking = 2
- ),null) AS SecondHighestSalary;
DISTINCT将salary字段去重,在此基础上,dense_rank() over():处理连续排序,如果有两个第一级时,接下来仍然是第二级。1,1,2,3,4。这里经过去重,每一级只有一个,再按照降序排列,也就是第一级是最大的,第二级是第二大,以此类推。
然后再根据别名查询结果集,在外层的where子句中, ranking=2查询到的就是第二级,也就是salary第二大的数据。
开窗函数查询的结果如果不存在不返回NULL,而是N/A,所以要加IFNULL()将N/A的结果返回为NULL,不然会执行失败。
表: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。
查询结果格式如下所示。
示例 1:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
示例 2:
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
- CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
- BEGIN
- SET N := N-1;
- RETURN (
- # Write your MySQL query statement below.
- select salary
- from employee
- GROUP BY salary
- ORDER BY salary DESC
- limit N, 1
- );
- END
同薪同名且不跳级的问题,解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水,由于无法直接用limit N-1,所以需先在函数开头处理N为N=N-1。
注:这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数(意味着0、负数、小数都不行)或者单一变量(意味着不能用表达式),也就是说想取一条,limit 2-1、limit 1.1这类的写法都是报错的。
- CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
- BEGIN
- RETURN (
- # Write your MySQL query statement below.
- SELECT
- DISTINCT e.salary
- FROM
- employee e
- WHERE
- (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
- );
- END
排名第N的薪水意味着该表中存在N-1个比其更高的薪水,假如N为2,那就有至少1个级别比他高;比如:1=100,2=100,3=200;那N=2,就N-1=2-1=1。
注意这里的N-1个更高的薪水是指去重后的N-1个,实际对应人数可能不止N-1个,比如1=100,2=100。
最后返回的薪水也应该去重,因为可能不止一个薪水排名第N,这里的逻辑是count()的数量去重后等于前面几级的数量,也就是N=2,那么count=1,此时salary是最大的,那么e.salary是次一级就正好符合条件;
由于对于每个薪水的where条件都要执行一遍子查询,所以当salary为第二级时,count=2,e.salary为第三极,此时的count!=2-1,所以排除。最终得到唯一一个满足条件的结果。但,这样where子句的多次执行会导致效率低下。
- CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
- BEGIN
- RETURN (
- # Write your MySQL query statement below.
- SELECT
- e1.salary
- FROM
- employee e1 JOIN employee e2 ON e1.salary <= e2.salary
- GROUP BY
- e1.salary
- HAVING
- count(DISTINCT e2.salary) = N
- );
- END
一般来说,能用子查询解决的问题也能用连接解决。具体到本题:
两表自连接,连接条件设定为表1的salary小于表2的salary;
以表1的salary分组,统计表1中每个salary分组后对应表2中salary唯一值个数,即去重;
限定步骤2中having 计数个数为N-1,即实现了该分组中表1salary排名为第N个;
考虑N=1的特殊情形(特殊是因为N-1=0,计数要求为0),此时不存在满足条件的记录数,但仍需返回结果,所以连接用left join;
如果仅查询薪水这一项值,那么不用left join当然也是可以的,只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号,所以一定不为空,用join即可。
- CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
- BEGIN
- RETURN (
- # Write your MySQL query statement below.
- SELECT
- DISTINCT salary
- FROM
- (SELECT
- salary, dense_rank() over(ORDER BY salary DESC) AS rnk
- FROM
- employee) tmp
- WHERE rnk = N
- );
- END
在mysql8.0中有相关的内置函数,而且考虑了各种排名问题:
row_number(): 同薪不同名,相当于行号,例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名,有跳级,例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名,无跳级,例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名,即首先按桶的个数分出第一二三桶,然后各桶内从1排名,实际不是很常用
显然,本题是要用第三个函数。
另外这三个函数必须要要与其搭档over()配套使用,over()中的参数常见的有两个,分别是
partition by,按某字段切分
order by,与常规order by用法一致,也区分ASC(默认)和DESC,因为排名总得有个依据
注:下面代码仅在mysql8.0以上版本可用,最新OJ已支持。
表: Scores
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| score | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。
编写 SQL 查询对分数进行排序。排名按以下规则计算:
分数应按从高到低排列。
如果两个分数相等,那么两个分数的排名应该相同。
在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 score 降序返回结果表。
查询结果格式如下所示。
示例 1:
输入:
Scores 表:
+----+-------+
| 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 |
+-------+------+
- SELECT
- score,
- dense_rank ( ) over ( ORDER BY score DESC ) AS "rank"
- FROM
- scores
将查询到的数据根据顺序排号,相同分数的序号相同, 序号不间断,1 1 2 3 3 4这种
利用 dense_rank() over函数可以实现
与另外两个计算序号的函数的对比
1.rank() over:排名相同的两名是并列,但是占两个名次,1 1 3 4 4 6这种
2.dense_rank() over:排名相同的两名是并列,共占一个名词,1 1 2 3 3 4这种
3.row_number() over这个函数不需要考虑是否并列,哪怕根据条件查询出来的数值相同也会进行连续排名 1 2 3 4 5
表:Logs
- +-------------+---------+
- | Column Name | Type |
- +-------------+---------+
- | id | int |
- | num | varchar |
- +-------------+---------+
- id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
示例 1:
- 输入:
- Logs 表:
- +----+-----+
- | Id | Num |
- +----+-----+
- | 1 | 1 |
- | 2 | 1 |
- | 3 | 1 |
- | 4 | 2 |
- | 5 | 1 |
- | 6 | 2 |
- | 7 | 2 |
- +----+-----+
- 输出:
- Result 表:
- +-----------------+
- | ConsecutiveNums |
- +-----------------+
- | 1 |
- +-----------------+
- 解释:1 是唯一连续出现至少三次的数字。
- SELECT DISTINCT
- l1.Num AS ConsecutiveNums
- FROM
- Logs l1
- LEFT JOIN Logs l2 ON l1.id = l2.id - 1
- LEFT JOIN Logs l3 ON l2.id = l3.id - 1
- WHERE
- l1.Num = l2.Num
- AND l2.Num = l3.Num
左外连接关联查询三个连续的id,where字句过滤出num相同的连续id。
- SELECT
- l1.Num
- FROM
- LOGS l1,
- LOGS l2,
- LOGS l3
- WHERE
- l1.Num = l2.Num
- AND l2.Num = l3.Num
- AND l1.Id = l2.Id - 1
- AND l2.id = l3.id - 1
使用自连接查询只需要检索到哪个Num连续出现三次即可,如果连续出现两次Num说明Num连续出现的次数大于4为了避免相同的Num重复出现,使用ditinct去除相同的Num即可得解。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。