大家好,我是宁一。
最近会在群里每天更新SQL题目。
又间接督促我勤奋了许多——
题目一:
182. 查找重复的电子邮箱(简单)
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
+----+---------+
| Id | Emai |
+----+---------+
|1 | a@b.com |
| 2 | c@d.com |
|3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+
| Email |
+---------+
| a@b.com |
+---------+
解题思路:
可以先用子查询,用 count 聚合函数结合 group by 查询出每个邮箱的个数。再用母查询,来判断邮箱个数大于1的记录,就是我们最终想要得到的结果。
第一步:
用子查询查出每个邮箱的个数。
SELECT Email FROM(
SELECT Email, COUNT(Email) AS num
FROM Person
GROUP BY Email
第二步:
根据第一步得到的结果,我们来判断邮箱个数num大于1的记录。
SELECT Email FROM(
SELECT Email, COUNT(Email) AS num
FROM Person
GROUP BY Email
) AS a
WHERE num > 1;
想要自己电脑本地测试,可以用这个快速创建数据库语句:
-- 创建数据库
CREATE database SQLCode;
-- 选择数据库
USE SQLCode;
-- 创建科目表Person
CREATE TABLE Person(
Id INT,
Email VARCHAR(10));
-- 插入语句
INSERT INTO Person VALUES
(1,'a@b.com'),
(2,'c@d.com'),
(3,'a@b.com');
题目二:
176. 第二高的薪水(简单)
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。
示例 1:
输入:Employee 表
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
输出:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
示例 2:
输入:Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
输出:
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+
解题思路:
将不同的薪资按降序排序,然后使用 LIMIT OFFSET 获得第二高的薪资。
但是如果表中 salary 不同的数据小于2条的时候,则会出现空的情况,因此加一个 IFNULL 进行判断。
知识点:
(1)LIMIT子句基本用法:
用来限制返回记录的数量。
可以通过LIMIT m,n 跳过结果集中的前m条记录,取n条记录。这句话有点绕,我们举个例子。
取第7~9条记录,也就是跳过前6条,从第7条开始取,取7、8、9这3条记录。那应该用LIMIT 6,3来实现。
(2)IFNULL语句基本语法:
IFNULL(值1,值2)
如果值1为NULL,返回值2;如果值1不为NULL,返回值1。
第一步:
将工资倒序排序,并用DISTINCT去重,使用LIMIT 1,1获取第二高的工资。
SELECT DISTINCT Salary
FROM Employee
ORDER by Salary DESC
LIMIT 1,1 # 获取第二高的salary
第二步:
如果没有第二高的工资,返回null,使用IFNULL来实现。
SELECT IFNULL(
(
SELECT DISTINCT Salary
FROM Employee
ORDER by Salary DESC
LIMIT 1,1 # 获取第二高的salary
),
NULL # 如果没有,则为 NULL
)
想要自己电脑本地测试,可以用这个快速创建数据表语句:
-- 创建员工表Employee
CREATE TABLE Employee(
Id INT,
salary INT);
-- 插入语句
INSERT INTO Employee VALUES
(1,100),
(2,200),
(3,300);
题目三:
180. 连续出现的数字(中等)
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
示例 1:
输入:Logs 表
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 3 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
输出:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
| 2 |
+-----------------+
解题思路:
通过id+1的方式查找下一次出现的数字,通过id+2的方式查找下下次出现的数字,主要下次和下下次出现的数字和当前的数字相同,就满足了出现至少3次的条件。
知识点:JOIN连接
多张数据表联合查询,需要用到JOIN连接,JOIN连接分为好几种,我们这道题用到了内连接。
INNER JOIN:内连接,也可以只写JOIN。只有进行连接的两个表中,都存在与连接标准相匹配的数据才会被保留下来,相当于两个表的交集。如果前后连接同一张表,也叫自连接。
第一步:
将下次出现的数字和下下次出现的数字,放在同一个表中,方便我们第二步进行比较。
SELECT *
FROM Logs t1
JOIN Logs t2
ON t1.id+1=t2.id
JOIN Logs t3
ON t1.id+2=t3.id
第二步:
加上筛选条件,t1表中的数字,等于t2表中的数字也等于t3表中的数字。
SELECT *
FROM Logs t1
JOIN Logs t2
ON t1.id+1=t2.id
JOIN Logs t3
ON t1.id+2=t3.id
WHERE t1.Num=t2. Num
AND t1.Num=t3.Num;
第三步:
修改SELECT后面的字段,只将NUM输出就可以了。
SELECT DISTINCT t1.Num
FROM Logs t1
JOIN Logs t2
ON t1.id+1=t2.id
JOIN Logs t3
ON t1.id+2=t3.id
WHERE t1.Num=t2. Num
AND t1.Num=t3.Num;
想要自己电脑本地测试,可以用这个快速创建数据表语句:
-- 创建表
CREATE TABLE Logs(
Id INT,
Num INT);
-- 插入语句
INSERT INTO Logs VALUES
(1,1),
(2,1),
(3,1),
(4,3),
(5,2),
(6,2),
(7,2);
题目四:
1454. 活跃用户(中等)
现在有Accounts表:该表包含账户 id 和账户的用户名 name。
Logins表:包含登录用户的账户 id 和登录日期 login_date。(用户也许一天内登录多次)
写一个 SQL 查询, 找到活跃用户的 id 和 name,活跃用户是指那些至少连续 3 天登录账户的用户。返回的结果表按照 id 排序。
结果表格式如下例所示:
Accounts 表:
+----+----------+
| id | name |
+----+----------+
| 1 | '小王' |
| 7 | ‘小李' |
+----+----------+
Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7 | 2020-05-30 |
| 1 | 2020-05-30 |
| 7 | 2020-05-31 |
| 7 | 2020-06-01 |
| 7 | 2020-06-03 |
| 1 | 2020-06-07 |
+----+------------+
id = 1 的用户小王仅仅登录了 2 次, 所以小王不是活跃用户。
id = 7 的用户小李是连续3天登录的, 所以小李是活跃用户.
解题思路:
这个也是连续的问题,跟上面找连续数字的思路是一个样子,用自连接先将Accounts表和Logins表连接。
再连接一次Logins表,将别名命名为L2,只连接L1表中,每个id相同且相差2天之内的日期。这样与L1表中所有相差两天的日期都会显示出来。
比如:L1表中是5月30号,L2表中最多会查出5月30号、5月31号、6月1号的日期。如果的对应了3个日期,说明用户连续3天都登录了。如果对应了2个日期,说明用户在3天内只登录了两天。
知识点:
DATEDIFF 计算两个日期间隔天数:
SELECT
DATEDIFF('2022-04-11','2021-04-11') AS "间隔天数",
DATEDIFF('2022-04-11 01:00','2022-04-10 23:00') AS "间隔天数"
第一步:
先将表格连接起来,并找出L1、L2表中每个id相同,且相差2天之内的日期。
SELECT A.*,L1.login_date,L2.login_date
FROM Accounts A
JOIN logins L1 ON A.id = L1.id
JOIN Logins L2 ON L1.id=L2.id
AND DATEDIFF(L2.login_date,L1.login_date) BETWEEN 0 AND 2
第二步:
将上方查出的结果分组,看看L1表中相同的日期,对应到L2表中日期的个数。
SELECT A.*,L1.login_date
FROM Accounts A
JOIN logins L1 ON A.id = L1.id
JOIN Logins L2 ON L1.id=L2.id
AND DATEDIFF(L2.login_date,L1.login_date) BETWEEN 0 AND 2
GROUP BY A.id,A.name,L1.login_date
第三步:
上方表中个数为3的记录,对应的account账户名,就是我们要找的。再将SELECT后面的字段去掉,只剩下Account账户的字段就可以了。
SELECT A.*
FROM Accounts A
JOIN logins L1 ON A.id = L1.id
JOIN Logins L2 ON L1.id=L2.id
AND DATEDIFF(L2.login_date,L1.login_date) BETWEEN 0 AND 2
GROUP BY A.id,A.name,L1.login_date
HAVING COUNT(DISTINCT L2.login_date)=3
想要自己电脑本地测试,可以用这个快速创建数据表语句:
-- 创建表
CREATE TABLE Accounts(
Id INT,
name VARCHAR(10));
-- 插入语句
INSERT INTO Accounts VALUES
(1,'小王' ),
(7, '小李');
-- 创建表
CREATE TABLE Logins(
Id INT,
login_date DATETIME);
-- 插入语句
INSERT INTO Logins VALUES
(7,'2020-05-30' ),
(1,'2020-05-30' ),
(7,'2020-05-31' ),
(7,'2020-06-01' ),
(7,'2020-06-03' ),
(1,'2020-06-07' );
点击关注,编程入门不迷路~