学习数据库后,刷了很多题,在这里记录一些有意思的题。
一、求班里第一高的分数
编写一个 SQL 查询,获取 StudentGrade 表中第一高的分数(Grade)。SQL查询应该返回99作为第一高的分数。如果不存在第一高的分数,那么查询应返回 null。
SELECT DISTINCT MAX(Grade) HighestGrade FROM StudentGrade
二、求班里第二高的分数
编写一个 SQL 查询,获取 StudentGrade 表中第二高的分数(Grade)。SQL查询应该返回98 作为第二高的分数。如果不存在第二高的分数,那么查询应返回 null。
解题思路:第二高的分数可以理解为不是最大数的最大数,落实到命令中可以通过子查询去掉第一大的分数,子查询求出最大的分数,再通过"!="去掉第一大的分数,父查询求出第二大的分数。考虑有多个第一大的分数,所以要用distinct去重就不用管几个第一,几个第二之类的。
子连接方法 SELECT DISTINCT MAX(Grade) SecondHighestGrade FROM StudentGrade WHERE Grade!=(SELECT MAX(Grade) FROM StudentGrade)
引入IFNULL(x,y),若x不为空则返回x,否则返回y,这道题y=null;LIMIT x,y,找到对应的记录就停止
使用 IFNULL 和 LIMIT 子句 SELECT IFNULL( (SELECT DISTINCT Grade FROM StudentGrade ORDER BY Grade desc --根据分数倒序 LIMIT 1 OFFSET 1), null) AS SecondHighestGrade
Limit子句
Limit子句可以被用于强制 SELECT 语句返回指定的记录数。Limit接受一个或两个数字参数,参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
三、求班里第N高的分数
编写一个 SQL 查询,获取 StudentGrade 表中第N高的分数(Grade)。若N=2,SQL查询应该返回98 作为第二高的分数。如果不存在第N高的分数,那么查询应返回 null。
解题思路:子查询做题,两表自连接,连接条件设定为表StudentGrade A的Grade小于表StudentGrade B的Grade,以表A的Grade分组,统计表A中每个Grade分组后对应表B中Grade唯一值个数(去重),限定having 计数个数为N-1,即实现了该分组中表A的Grade排名为第N个。
为什么连接用left join?
考虑N=1的特殊情形(计数要求为N-1=0),此时不存在满足条件的记录数,但仍需返回结果。
CREATE FUNCTION getNthHighestGrade(N INT) RETURNS INT BEGIN RETURN ( SELECT DISTINCT A.Grade FROM StudentGrade A LEFT JOIN StudentGrade B ON A.Grade < B.Grade GROUP BY A.Grade HAVING count(DISTINCT B.Grade) = N-1 ); END
有什么不足之处欢迎大家评论指出,一起学习。