大家好，我是宁
最近会在群里每天更新SQL题目。
又间接督促我勤奋了许多——
题目
18查找重复的电子邮箱
编写一个SQL查询，查找Person表中所有重复的电子邮箱。
示例：
+----+---------+
| Id | Emai |
+----+---------+
|1 | a@b.com |
| 2 | c@d.com |
|3 | a@b.com |
+----+---------+根据以上输入，你的查询应返回以下结果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+解题思路：
可以先用子查询，用count聚合函数结合groupby查询出每个邮箱的个数。再用母查询，来判断邮箱个数大于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");题目
17第二高的薪水
编写一个SQL查询，获取并返回Employee表中第二高的薪水。如果不存在第二高的薪水，查询应该返回null。
示例
输入：Employee表
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+示例
输入：Employee表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+解题思路：
将不同的薪资按降序排序，然后使用LIMITOFFSET获得第二高的薪资。
但是如果表中salary不同的数据小于2条的时候，则会出现空的情况，因此加一个IFNULL进行判断。
知识点：
LIMIT子句基本用法：
用来限制返回记录的数量。
可以通过LIMITm,n跳过结果集中的前m条记录，取n条记录。这句话有点绕，我们举个例子。
取第7~9条记录，也就是跳过前6条，从第7条开始取，取9这3条记录。那应该用LIMIT6,3来实现。
IFNULL语句基本语法：
IFNULL
如果值1为NULL，返回值2；如果值1不为NULL，返回值
第一步：
将工资倒序排序，并用DISTINCT去重，使用LIMIT1,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连接分为好几种，我们这道题用到了内连接。
INNERJOIN：内连接，也可以只写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);题目
145活跃用户
现在有Accounts表：该表包含账户id和账户的用户名name。
Logins表：包含登录用户的账户id和登录日期login_date。
写一个SQL查询,找到活跃用户的id和name，活跃用户是指那些至少连续3天登录账户的用户。返回的结果表按照id排序。
结果表格式如下例所示:
+----+----------+
| id | name |
+----+----------+
| 1 | "小王" |
| 7 | ‘小李" |
+----+----------++----+------------+
| 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表，将别名命名为L只连接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 "间隔天数"第一步：
先将表格连接起来，并找出LL2表中每个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" );
文章为作者独立观点，不代表观点
相关文章
股民评论