[大数据]SQL第二练

1连续出现的数字

1.1题目描述

表：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 是唯一连续出现至少三次的数字。

1.2求解

我自己没做出来，看到官方解法觉得我自己是XX，强行三表连接,求解如下：

SELECT DISTINCT
    l1.Num AS ConsecutiveNums # 区别名
FROM
    Logs l1,
    Logs l2,
    Logs l3  # 三个表取别名
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1 # 证明三个id连续
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num  # 证明三个数字相等
; 

执行：

2超过经理收入的员工

2.1题目描述

表：Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。

编写一个SQL查询来查找收入比经理高的员工。

以 任意顺序 返回结果表。

查询结果格式如下所示：

输入: 
Employee 表:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
输出: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
解释: Joe 是唯一挣得比经理多的雇员。

2.2解题

sam是joe的领导，max 是henry的领导。一表多用，解法如下：

SELECT a.name  as "Employee"
FROM
Employee as a,
Employee as  b
WHERE
(a. managerId=b.Id
AND  a.salary>b.salary);

执行：

3分数排名

3.1题目描述

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

查询结果格式如下所示。

输入: 
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)  'rank'
from scores;

提交结果：

注意：rank是关键词，要加引号。降序要desc。
知识点：
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

4查找重复的电子邮箱

4.1题目描述

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

说明：所有电子邮箱都是小写字母。

4.2 求解

通过 group by 对 email 分组，在使用 having 将重复的 email 筛选出来：

select Email
from Person
group by Email
having count(Email) > 1;
# 查询该列数据不包括null的数量

执行：

本题也可以拓展为：找出重复出现n次的数据

select 列名
from 表名
group by 列名
having count(列名) > n;

注意优先顺序：where >group by>having>order by

5第二高的薪水

5.1题目描述

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                |
+---------------------+

5.2求解

ifnull(a,b)函数解释：

1. 如果value不是空，结果返回a
2. 如果value是空，结果返回b

LIMIT子句语法:

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

LIMIT子句参数：

• offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。
• count指定要返回的最大行数

本题求解：

select ifNull
(
(select distinct salary
from Employee 
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;

执行结果：

5.3获得第n个最高值

1. 首先，按照降序对结果集进行排序。
2. 第二步，使用LIMIT子句获得第n贵的产品。

通用查询如下：

SELECT 
    column1, column2,...
FROM
    table
ORDER BY column1 DESC
LIMIT n-1, 1;

获取售价第三高、第四高的产品信息为：LIMIT 2, 1 和 LIMIT 3, 1。