Hello!!我回归啦~ 今天开始继续刷SQL的题目~ 争取这个寒假可以在SQL有一点点进步!
闯关开始啦
关卡1 - 组合两个表
思路:
这道题说不管person是否有信息都应该被merge。所以我们应该把有person的那张表作为主表,然后把另一张表left join进来,这样就可以确保person都存在。
那是不是这样就好了呢?
SELECT FirstName,LastName FROM Person
LEFT JOIN Address
ON Person.PersonId = Address.PersonId
错!这里,兔子还遇到一个问题,就是我们并没有提供city和state
但是city 和 state 明明是在另外的一个表格,怎么才能让他也被选择进来呢?我们可以在第一行就输入这两个来自另一个表格的变量。
SELECT FirstName,LastName,City,State FROM Person
LEFT JOIN Address
ON Person.PersonId = Address.PersonId
这样就运行成功啦!However,为了让他更加直观,我们一般会在第一行的这些变量前面加上一个表格的首字母.
所以就是:
SELECT P.FirstName, P.LastName, A.City, A.State FROM Person P
LEFT JOIN Address A
ON P.PersonId = A.PersonId
恭喜过关!进入下一关!
关卡2 - 第二高的薪水
思路:
这道题看起来好像挺简单的!
他想要生成一个新的列,我们就用AS关键字,想要排序,我们就进行ORDER BY,想要从大到小,我们就descending order用DESC,想只生成第二个,我们就LIMIT 1 OFFSET 1,这些语法在我之前的文章都介绍过了~
所以我刷刷刷写下来如下代码
SELECT DISTINCT Salary AS SecondHighestSalary
FROM Employee
ORDER BY SecondHighestSalary DESC
LIMIT 1 OFFSET 1
但是!提交之后显示错误了。
那么问题出在哪里呢~问题就是,当不存在第二高的薪水时,系统并没有返回NULL
所以我们要把我们之前的代码作为一个临时表,我们要做一个表中表,可以用到IFNULL语句,语法是:IFNULL((语句),如果前面的语句是null的时候返回的东西)
那这里就是IFNULL((前面的代码),NULL)
组合起来就是:
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),NULL) AS SecondHighestSalary
恭喜过关!进入下一关!
关卡3 - 第N高的薪水
思路:
其实这道题和第二题很像,但是它是以一个N的形式出现
题目本身帮助我们定义了一个函数
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN ();
END
我们的任务就是在return里面写代码~
我的第一反应是,那就复制之前的代码,然后把OFFSET 那里改成(N-1),然鹅🦢,并没有那么顺利,我成功bug了
后来才知道,原来要在前面SET一个变量,让这个变量等于N-1,然后再OFFSET这个变量
那么答案就是:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET n = N-1;
RETURN (
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET n),NULL) AS SecondHighestSalary);
END
当然,也可以LIMIT n,1 而不要OFFSET,但是意思是一样的啦~LIMIT x,y ,是跳过x行,取y行数据的意思,类似于 limit y offset x
恭喜过关!进入下一关!
关卡4 - 分数排名
思路:
这道题其实说实话,兔子不懂。
哈哈哈哈哈哈哈~这么坑的嘛~~
所以呢,我就看了题解,发现了我没学过的东西~
那就是SQL的排名函数,也叫窗口函数,解释如下:
那这样一来,这道题就变得很简单啦,它需要我们用dense_rank() 函数来解决~
它的语法是直接在SELECT语句里面,在select之后,dense_rank() over (你要进行的处理) AS ‘一个新的列名’ From 表格名
如下:
SELECT Score,
dense_rank( ) over (ORDER BY Score DESC) AS 'Rank'
From Scores
恭喜过关!进入下一关!
关卡5 - 连续出现的数字
思路:
自连接(自身连接),把一张表复制出多张一模一样的表来使用。然后我们找出下面一行和上面一行,如果这三个数字相等,说明他出现了三次。
那我们开始写code!
步骤1: 连接三表
SELECT * FROM Logs AS a, Logs AS b, Logs AS c
WHERE a.Id = b.Id + 1
AND a.Id = c.Id - 1
AND a.NUM = b.NUM
AND a.NUM = c.NUM
这时,最后两行代码限制了,这三个数字必须相等才能留在这个表格
所以我们只需在SELECT语句加一个distinct,就能找出,重复三次的数字究竟是什么!
SELECT DISTINCT a.Num AS ConsecutiveNums
FROM Logs AS a,Logs AS b,Logs AS c
WHERE a.Id = b.Id + 1
AND a.Id = c.Id - 1
AND a.Num = b.Num
AND a.Num = c.Num
恭喜过关!
今天学到的新知识:SET,IFNULL 函数,limit X,Y = limit Y offset X, 窗口函数dense_rank()over(), rank()over(), row_number()over(), 自连接。
明天继续闯关~yay ~