LeetCode-SQL（二）_employee 表保存了一年内的薪水信息,请你编写 sql 语句,对于每个员工,查询他除最

以下题目都来自LeetCode

21、574.当选者

-- 表: Candidate
+-----+---------+
| id  | Name    |
+-----+---------+
| 1   | A       |
| 2   | B       |
| 3   | C       |
| 4   | D       |
| 5   | E       |
+-----+---------+ 

-- 表: Vote
+-----+--------------+
| id  | CandidateId  |
+-----+--------------+
| 1   |     2        |
| 2   |     4        |
| 3   |     3        |
| 4   |     2        |
| 5   |     5        |
+-----+--------------+
id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

请编写 sql 语句来找到当选者的名字，上面的例子将返回当选者 B.

+------+
| Name |
+------+
| B    |
+------+
1
2
3
4
5

解答：

-- 1、Vote表中分组CandidateId排序找出第一名
select CandidateId from Vote group by CandidateId order by count(CandidateId) desc limit 1

-- 2、Candidate表中查询第二步得到的结果
select Name from Candidate where id=(select CandidateId from Vote group by CandidateId order by count(CandidateId) desc limit 1)
1
2
3
4
5

---

---

22、577.员工奖金

选出所有 bonus < 1000 的员工的 name 及其 bonus。

-- Employee 表单
+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId 是这张表单的主关键字

-- Bonus 表单
+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId 是这张表单的主关键字
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

输出示例：

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+
1
2
3
4
5
6
7

解答：

-- 两表连接
select name,bonus from Employee e left join Bonus b on e.empId=b.empId where b.bonus<1000 or b.bonus is null;
1
2

---

---

23、578.查询回答率最高的问题

从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。

id 表示用户 id；action 有以下几种值：“show”，“answer”，“skip”；当 action 值为 “answer” 时 answer_id 非空，而 action 值为 “show” 或者 “skip” 时 answer_id 为空；q_num 表示当前会话中问题的编号。

请编写 SQL 查询来找到具有最高回答率的问题。

输入：
+------+-----------+--------------+------------+-----------+------------+
| id   | action    | question_id  | answer_id  | q_num     | timestamp  |
+------+-----------+--------------+------------+-----------+------------+
| 5    | show      | 285          | null       | 1         | 123        |
| 5    | answer    | 285          | 124124     | 1         | 124        |
| 5    | show      | 369          | null       | 2         | 125        |
| 5    | skip      | 369          | null       | 2         | 126        |
+------+-----------+--------------+------------+-----------+------------+
输出：
+-------------+
| survey_log  |
+-------------+
|    285      |
+-------------+
解释：
问题 285 的回答率为 1/1，而问题 369 回答率为 0/1，因此输出 285 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

解答：

-- 根据题目分析，也是分组求第一
select question_id as `survey_log` from survey_log group by question_id order by count(answer_id) desc limit 1
1
2

扩展：分组求第一

此题和574这道题基本一个思路，都是分组求第一，用到的就是分组以后倒序取第一个

---

---

24、578.查询员工的累计薪水

Employee 表保存了一年内的薪水信息。

请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

结果请按 Id 升序，然后按 Month 降序显示。

-- 示例：
-- 输入：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |
输出：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

解释：

员工 ‘1’ 除去最近一个月（月份 ‘4’），有三个月的薪水记录：月份 ‘3’ 薪水为 40，月份 ‘2’ 薪水为 30，月份 ‘1’ 薪水为 20。

所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
1
2
3
4
5

员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。

| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |
1
2
3

员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘3’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下

| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |
1
2
3
4

解答：

-- 第一步开窗
select Id,`Month`,Salary,row_number() over(partition by Id order by `Month` desc) as rk
from Employee
-- 第二步，去掉当前月份，即最大月份
select Id,`Month`,Salary from (select Id,`Month`,Salary,row_number() over(partition by Id order by `Month` desc) as rk from Employee) t1 where rk>1;
-- 示例结果如下
Id	Month	Salary
1	7		90
1	4		60
1	3		40
1	2		30
1	1		20
2	1		20
3	3		60
3	2		40
-- 第三部，根据第二步的结果左连接一个月的，二个月的
select t2.Id Id,t2.`Month` `Month`,(ifnull(t2.Salary,0)+ifnull(t3.Salary,0)+ifnull(t4.Salary,0)) Salary
from 
(select Id,`Month`,Salary from (select Id,`Month`,Salary,row_number() over(partition by Id order by `Month` desc) as rk from Employee) t1 where rk>1)
t2
left join
(select Id,`Month`,Salary from (select Id,`Month`,Salary,row_number() over(partition by Id order by `Month` desc) as rk from Employee) t1 where rk>1)
t3 
on
(t2.Id=t3.Id and t2.`Month`-1=t3.`Month`)
left join 
(select Id,`Month`,Salary from (select Id,`Month`,Salary,row_number() over(partition by Id order by `Month` desc) as rk from Employee) t1 where rk>1)
t4 
on 
(t2.Id=t4.Id and t2.`Month`-2=t4.`Month`)
-- 连接如下
Id	Month	Salary	Id	Month	Salary	Id	Month	Salary
1	7			90	1	null	null	1	null	null
1	4			60	1	3		40		1	2		30
1	3			40	1	2		30		1	1		20
1	2			30	1	1		20		1	null	null
1	1			20	1	null	null	1	null	null
2	1			20	2	null	null	2	null	null
3	3			60	3	2		40		3	null	null
3	2			40	3	null	null	3	null	null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

---

---

25、580.统计各专业学生人数

一所大学有 2 个数据表，分别是 student 和 department ，这两个表保存着每个专业的学生数据和院系数据。

写一个查询语句，查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。

将你的查询结果按照学生人数降序排列。 如果有两个或两个以上专业有相同的学生数目，将这些部门按照部门名字的字典序从小到大排列。

student 表格如下：

| Column Name  | Type      |
|--------------|-----------|
| student_id   | Integer   |
| student_name | String    |
| gender       | Character |
| dept_id      | Integer   |
1
2
3
4
5
6

其中， student_id 是学生的学号， student_name 是学生的姓名， gender 是学生的性别， dept_id 是学生所属专业的专业编号。

department 表格如下：

| Column Name | Type    |
|-------------|---------|
| dept_id     | Integer |
| dept_name   | String  |
1
2
3
4

dept_id 是专业编号， dept_name 是专业名字。

这里是一个示例输入：

-- student 表格：
| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |

-- department表格：
| dept_id | dept_name   |
|---------|-------------|
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |

-- 示例输出为：
| dept_name   | student_number |
|-------------|----------------|
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

解答：

-- 第一步：学生表按照专业id分组求个数
select 
	dept_id,
	count(dept_id) student_number 
from 
	student 
group by 
	dept_id;

-- 第二步，专业表和第一步得到的结果进行左连接，然后按照要求进行排序
select 
	d.dept_name dept_name,
	ifnull(s.student_number,0) student_number
from 
	department d 
left join 
	(select dept_id,count(dept_id) student_number from student group by dept_id) s 
on 
	d.dept_id=s.dept_id 
order by 
	s.student_number desc,d.dept_name asc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- 用department表和student表进行左连接，然后加条件筛选
select 
	d.dept_name dept_name,
	count(s.dept_id) student_number
from
	department d
left join
	student s
on
	d.dept_id=s.dept_id
group by 
	d.dept_id,d.dept_name
order by
	student_number desc,d.dept_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

---

26、584.寻找用户推荐人

-- 给定表 customer ，里面保存了所有客户信息和他们的推荐人。
+------+------+-----------+
| id   | name | referee_id|
+------+------+-----------+
|    1 | Will |      NULL |
|    2 | Jane |      NULL |
|    3 | Alex |         2 |
|    4 | Bill |      NULL |
|    5 | Zack |         1 |
|    6 | Mark |         2 |
+------+------+-----------+
1
2
3
4
5
6
7
8
9
10
11

写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。

对于上面的示例数据，结果为：

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+
1
2
3
4
5
6
7
8

解答：

-- is null 和 ！=
select 
	name 
from 
	customer 
where 
	referee_id is null 
or 
	referee_id != 2;
1
2
3
4
5
6
7
8
9

扩展：null值

<> 和!=

直接使用 <> 或者!=得到的结果不会包含为null的

=null：这种是错误的，应该使用is null | is not null

---

---

27、585.2016年的投资

写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

对于一个投保人，他在 2016 年成功投资的条件是：

他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。
输入格式:
表 insurance 格式如下：

| Column Name | Type          |
|-------------|---------------|
| PID         | INTEGER(11)   |
| TIV_2015    | NUMERIC(15,2) |
| TIV_2016    | NUMERIC(15,2) |
| LAT         | NUMERIC(5,2)  |
| LON         | NUMERIC(5,2)  |
1
2
3
4
5
6
7

PID 字段是投保人的投保编号， TIV_2015 是该投保人在2015年的总投保金额， TIV_2016 是该投保人在2016年的投保金额， LAT 是投保人所在城市的维度， LON 是投保人所在城市的经度。

样例输入

| PID | TIV_2015 | TIV_2016 | LAT | LON |
|-----|----------|----------|-----|-----|
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
1
2
3
4
5
6

样例输出

| TIV_2016 |
|----------|
| 45.00    |
1
2
3

解释

就如最后一个投保人，第一个投保人同时满足两个条件：
1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。

第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。

所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和，结果是 45 。
1
2
3
4
5
6
7
8

解答：

-- 分析成功投资的说明
-- 需要TIV_2015不是唯一，可以用group by 加上 count来判断
-- 地理位置是唯一，也用group by 加上count来判断
-- 第一步：找出TIV_2015不是唯一TIV_2015
select
	TIV_2015
from 
	insurance 
group by 
	TIV_2015
having
	count(*)>1
;
-- 第二步：找出地理位置是唯一的，可以拼接在一块
select
	concat(LAT,LON)
from
	insurance
group by 
	LAT,LON
having
	count(*)=1
;
-- 第三步：利用子查询进行查询
select
	sum(insurance.TIV_2016) as TIV_2016
from 
	insurance
where
	insurance.TIV_2015
in
	(select
	TIV_2015
from 
	insurance 
group by 
	TIV_2015
having
	count(*)>1)
and
	concat(LAT,LON)
in
	(select
	concat(LAT,LON)
from
	insurance
group by 
	LAT,LON
having
	count(*)=1)
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

---

---

28、586.订单最多的客户

在表 orders 中找到订单数最多客户对应的 customer_number 。

数据保证订单数最多的顾客恰好只有一位。

表 orders 定义如下：

| Column            | Type      |
|-------------------|-----------|
| order_number (PK) | int       |
| customer_number   | int       |
| order_date        | date      |
| required_date     | date      |
| shipped_date      | date      |
| status            | char(15)  |
| comment           | char(200) |
1
2
3
4
5
6
7
8
9

样例输入

| order_number | customer_number | order_date | required_date | shipped_date | status | comment |
|--------------|-----------------|------------|---------------|--------------|--------|---------|
| 1            | 1               | 2017-04-09 | 2017-04-13    | 2017-04-12   | Closed |         |
| 2            | 2               | 2017-04-15 | 2017-04-20    | 2017-04-18   | Closed |         |
| 3            | 3               | 2017-04-16 | 2017-04-25    | 2017-04-20   | Closed |         |
| 4            | 3               | 2017-04-18 | 2017-04-28    | 2017-04-25   | Closed |   
1
2
3
4
5
6

样例输出

| customer_number |
|-----------------|
| 3               |
1
2
3

解释

customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单
所以结果是该顾客的 customer_number ，也就是 3 。
1
2

进阶： 如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

解答：

-- 分组求第一
select
	customer_number
from 
	orders
group by
	customer_number
order by
	count(customer_number) desc
limit
	1
;
1
2
3
4
5
6
7
8
9
10
11
12

---

29、595.大的国家

-- 这里有张 World 表
+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

-- 如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。
-- 编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。
-- 例如，根据上表，我们应该输出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

解答：

-- 方法一：使用where求取符合条件的，or
select 
	name,
	population,
	area 
from 
	world 
where
	area>3000000 
or 
	population>25000000
;

-- 方法二：分别求出两个条件，然后用union
-- 先求出人口大于300万
select
	name,
	population,
	area
from
	world
where
	area>3000000
;
-- 再求出人口大于2500万
select
	name,
	population,
	area
from
	world
where
	population>25000000
;
-- 最后使用union进行连接
select
	name,
	population,
	area
from
	world
where
	area>3000000
union
select
	name,
	population,
	area
from
	world
where
	population>25000000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

---

30、596.超过5名学生的课

-- 有一个courses 表 ，有: student (学生) 和 class (课程)。
-- 请列出所有超过或等于5名学生的课。
-- 例如，表：
+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+
-- 应该输出:
+---------+
| class   |
+---------+
| Math    |
+---------+
-- 提示：
-- 学生在每个课中不应被重复计算。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

解答：

-- 根据提议，分组求大于5的，当然要出去，相同学生选相同的课程，这是这道题的考察
-- 方法一：group by   having    distinct
select
	class
from 
	courses
group by
	class
having
	count(distinct student)>=5
;
-- 方法二：
-- 先求出各科的人数
select
	class
	count(distinct student) count
from 
	courses
group by
	class
;
-- 再从中选取大于等于5的
select
	class
from
	(select
		class
		count(distinct student) as num
	from 
		courses
	group by
		class) s
where
	s.count>=5
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

扩展：

去重distinct

---

31、597.好友申请I：总体通过率

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。
表：FriendRequest
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| sender_id      | int     |
| send_to_id     | int     |
| request_date   | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。
-- 表：RequestAccepted
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求通过的日期。
写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。
提示：
通过的好友申请不一定都在表 friend_request 中。你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。
查询结果应该如下例所示：
FriendRequest 表：
+-----------+------------+--------------+
| sender_id | send_to_id | request_date |
+-----------+------------+--------------+
| 1         | 2          | 2016/06/01   |
| 1         | 3          | 2016/06/01   |
| 1         | 4          | 2016/06/01   |
| 2         | 3          | 2016/06/02   |
| 3         | 4          | 2016/06/09   |
+-----------+------------+--------------+

RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
| 3            | 4           | 2016/06/10  |
+--------------+-------------+-------------+

Result 表：
+-------------+
| accept_rate |
+-------------+
| 0.8         |
+-------------+
总共有 5 个请求，有 4 个不同的通过请求，所以通过率是 0.80

进阶:
	你能写一个查询语句得到每个月的通过率吗？
	你能求出每一天的累计通过率吗？
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

解答：

先求出通过人数，因为通过可能重复，所以得去重，如果用group by，可能会出现null值，会报错，所以用distinct
select 
	count(*) 
from 
	(select  distinct requester_id,accepter_id from RequestAccepted) A
;
再求请求人数
select 
	count(*) 
from 
	(select distinct sender_id,send_to_id from FriendRequest) B
;
计算通过率
select round(ifnull((select count(*) from (select distinct requester_id,accepter_id from RequestAccepted) A)/(select count(*) from (select distinct sender_id,send_to_id from FriendRequest) B),0),2) as accept_rate
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

---

32、601.体育馆的人流量

表：Stadium
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

Result table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

解答：

第一步:因为id自增，先需要连续三条，这可以自连接，连接条件为id+1，可以参考下面的表格，则一目了然。
select 	
	*
from 
	Stadium s1
join 
	Stadium s2
on 
	s1.id+1=s2.id
join 
	Stadium s3
on 
	s2.id+1=s3.id
;
第二步：在第一步的基础上，加条件，为每个people都大于等于100,求得id
select 
	s1.id id1,
	s2.id id2,
	s3.id id3
from 
	Stadium s1
join 
	Stadium s2
on 
	s1.id+1=s2.id
join 
	Stadium s3
on 
	s2.id+1=s3.id
where 
	s1.people>=100 
and 
	s2.people>=100 
and 
	s3.people>=100
;  t1
结果为
id1	id2	id3
5	6	7
6	7	8
第三步：因为第二步得到的结果id1,id2,id3都是Stadium中的id，而我们也需要根据这些id求出对应的数据,所以可以连接并加上条件
select s1.id id,s1.visit_date visit_date,s1.people people
from
	Stadium s1
join 
	t1
on
	s1.id=id1 or s1.id=id2 or s1.id=id3
; 
第四步：在第三步的基础上，去重，排序，得出答案
select distinct s1.id id,s1.visit_date visit_date,s1.people people
from 	
	Stadium s1
join 
	t1
on
	s1.id=id1 or s1.id=id2 or s1.id=id3
order by 
	s1.visit_date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

最终sql为
连续三条，可以内连接两次，方便理解，先求
select distinct s1.id id,s1.visit_date visit_date,s1.people
from Stadium s1
join(select s1.id id1,s2.id id2,s3.id id3
from Stadium s1
join Stadium s2
on s1.id+1=s2.id
join Stadium s3
on s2.id+1=s3.id
where s1.people>=100 and s2.people>=100 and s3.people>=100) s2
on s1.id=id1 or s1.id=id2 or s1.id=id3
order by s1.visit_date
；
1
2
3
4
5
6
7
8
9
10
11
12
13
14

扩展：

求连续，可以用自连接加条件

---

33、602.好友申请II：谁有最多的好友

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。

表 request_accepted 存储了所有好友申请通过的数据记录，其中， requester_id 和 accepter_id 都是用户的编号。

| requester_id | accepter_id | accept_date|
|--------------|-------------|------------|
| 1            | 2           | 2016_06-03 |
| 1            | 3           | 2016-06-08 |
| 2            | 3           | 2016-06-08 |
| 3            | 4           | 2016-06-09 |
1
2
3
4
5
6

写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据，结果为：

| id | num |
|----|-----|
| 3  | 3   |
1
2
3

注意：

保证拥有最多好友数目的只有 1 个人。
好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。

解释：

编号为 ‘3’ 的人是编号为 ‘1’，‘2’ 和 ‘4’ 的好友，所以他总共有 3 个好友，比其他人都多。

进阶：

在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？

解答：

因为计算为双向选择，所以先算requester_id字段的人数
select requester_id,count(requester_id) c1 from request_accepted group by requester_id
union all
再算accepter_id字段的人数
select accepter_id,count(accepter_id) c2 from request_accepted group by accepter_id
然后两个union all
select requester_id,count(requester_id) c1 from request_accepted group by requester_id
union all
select accepter_id,count(accepter_id) c2 from request_accepted group by accepter_id
把上述结果作为一个表，进行分组，求和，求第一名
select requester_id,sum(c1) from 
(select requester_id,count(requester_id) c1 from request_accepted group by requester_id
union all
select accepter_id,count(accepter_id) c2 from request_accepted group by accepter_id) t
group by requester_id order by sum(c1) desc limit 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

扩展：

union all 需要两个表格字段数相同，字段名取第一个表的名

求最高、最大、最多、第一等都可以用这种思路：倒序加取一，即 order by 字段 desc limit 1

---

34、603.连续空余坐

几个朋友来到电影院的售票处，准备预约连续空余座位。

你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |
1
2
3
4
5
6
7

对于如上样例，你的查询语句应该返回如下结果。

| seat_id |
|---------|
| 3       |
| 4       |
| 5       |
1
2
3
4
5

注意：

seat_id 字段是一个自增的整数，free 字段是布尔类型（‘1’ 表示空余， ‘0’ 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。

解答：

方法一：
表进行自连接，取条件为abs(a.id-b.id)=1并且free字段为1表示空余的,最后再排序去重
select distinct c1.seat_id seat_id
from cinema c1 join cinema c2 on abs(c1.seat_id-c2.seat_id)=1 and c1.free=1 and c2.free=1 group by c1.cinema

方法二：
过滤出free为1的，并开窗
select seat_id,seat_id-row_number() over(order by seat_id) as id from cinema where free=1;
结果如下：
seat_id	id
1		0
3		1
4		1
5		1
gourp by id having count(id)>=2就可以求出seat_id，但是group by 之后不能求出每一个seat_id,所以这里我们可以再开一个窗求数量
select seat_id,count(id) over(partition by id) as cn
from (select seat_id,seat_id-row_number() over(order by seat_id) as id from cinema where free=1) t
然后查询数量大于等于2的
select seat_id from(
select seat_id,count(id) over(partition by id) as cn
from (select seat_id,seat_id-row_number() over(order by seat_id) as id from cinema where free=1) t) t where t.cn>=2 order by seat_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

扩展：SQL执行顺序

SQL中的常用查询关键词：
select、distinct、from、join、on、where、group by、having、sum、count、max、avg、order by、limit
书写顺序：
select -> distint -> from -> join -> on -> where -> group by -> having -> order by -> limit
必须字段：select、from
可选字段：distinct、join、on、where、group by、having、sum、count、max、avg、order by、limit
执行顺序：from -> on -> join -> where -> group by(开始使用select中的别名，后面的语句中都可以使用别名) -> sum、count、max、avg -> having -> select -> distinct -> order by -> limit
关键词含义：
from：需要从哪个数据表检索数据
join：对需要关联查询的表进行关联
on：关联条件
where：过滤表中数据的条件
group by：如何将上面过滤出的数据分组
having：对上面已经分组的数据进行过滤的条件
select：查看结果集中的哪个列或列的计算结果
distinct：对结果集重复值去重
order by：按照什么样的顺序来查看返回的数据
limit：截取出目标页数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

---

35、607.销售员

描述

给定 3 个表： salesperson， company， orders。
输出所有表 salesperson 中，没有向公司 'RED' 销售任何东西的销售员。

示例：
输入

表： salesperson

+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+
1
2
3
4
5
6
7
8
9

表 salesperson 存储了所有销售员的信息。每个销售员都有一个销售员编号 sales_id 和他的名字 name 。

表： company

+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+
1
2
3
4
5
6
7
8

表 company 存储了所有公司的信息。每个公司都有一个公司编号 com_id 和它的名字 name 。

表： orders

+----------+------------+---------+----------+--------+
| order_id | order_date | com_id  | sales_id | amount |
+----------+------------+---------+----------+--------+
| 1        |   1/1/2014 |    3    |    4     | 100000 |
| 2        |   2/1/2014 |    4    |    5     | 5000   |
| 3        |   3/1/2014 |    1    |    1     | 50000  |
| 4        |   4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+
1
2
3
4
5
6
7
8

表 orders 存储了所有的销售数据，包括销售员编号 sales_id 和公司编号 com_id 。

输出

+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+
1
2
3
4
5
6
7

解释

根据表 orders 中的订单 ‘3’ 和 ‘4’ ，容易看出只有 ‘John’ 和 ‘Pam’ 两个销售员曾经向公司 ‘RED’ 销售过。

所以我们需要输出表 salesperson 中所有其他人的名字。

解答：

找出公司为RED的id
select id from company where name='RED'
通过orders表找出为RED公司的订单编号
select sales_id from orders where com_id=(select com_id from company where name='RED')
在salesperson表中找出不是RED公司订单编号的其他人员
select name from salesperson where sales_id not in(select sales_id from orders where com_id=(select com_id from company where name='RED'))
1
2
3
4
5
6

---

36、608.树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
1
2
3
4
5
6
7
8
9

树中每个节点属于以下三种类型之一：

叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+
1
2
3
4
5
6
7
8
9

解释

节点 ‘1’ 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 ‘2’ 和 ‘3’ 。
节点 ‘2’ 是内部节点，因为它有父节点 ‘1’ ，也有孩子节点 ‘4’ 和 ‘5’ 。
节点 ‘3’, ‘4’ 和 ‘5’ 都是叶子节点，因为它们都有父节点同时没有孩子节点。

 	  1
	/   \
   2     3
 /   \
 4    5
1
2
3
4
5

解答：

p_id为null的为根节点
叶子节点为id有，p_id里没有的
出去根节点和叶子节点就是内部节点
sleect id,if(isNull(p_id),'Rooto',if(id in (select p_id from tree),'Inner','Leaf')) Type
from tree
1
2
3
4
5

---

37、612.平面上的最近距离

表 point_2d 保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。

写一个查询语句找到两点之间的最近距离，保留 2 位小数。

| x  | y  |
|----|----|
| -1 | -1 |
| 0  | 0  |
| -1 | -2 |
1
2
3
4
5

最近距离在点 (-1,-1) 和(-1,-2) 之间，距离为 1.00 。所以输出应该为：

| shortest |
|----------|
| 1.00     |
1
2
3

**注意：**任意点之间的最远距离小于 10000 。

解答：

两点之间距离公式为：
select round(sqrt(min((pow(p1.x-p2.x,2)+pow(p1.y-p2.y,2)),2) as shortest
from point_2d p1,point_2d p2 where p1.x!=p2.x or p1.y!=p2.y
1
2
3

扩展：基础函数

round(x,y): 返回参数x的四舍五入的有y位的小数的值

sqrt(x): 返回x的平方根

pow(x,y):返回x的y次方

---

38、613.直线上的最近距离

表 point 保存了一些点在 x 轴上的坐标，这些坐标都是整数。

写一个查询语句，找到这些点中最近两个点之间的距离。

| x   |
|-----|
| -1  |
| 0   |
| 2   |
1
2
3
4
5

最近距离显然是 ‘1’ ，是点 ‘-1’ 和 ‘0’ 之间的距离。所以输出应该如下：

| shortest|
|---------|
| 1       |
1
2
3

注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。

进阶：如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？

解答：

自连接加条件
select min(abs(p1.x-p2.x)) as shorttest
from point p1 join point p2 on p1.x!=p2.x
1
2
3

---

39、614.二级关注者

在 facebook 中，表 follow 会有 2 个字段： followee, follower ，分别表示被关注者和关注者。

请写一个 sql 查询语句，对每一个关注者，查询关注他的关注者的数目。

比方说：

+-------------+------------+
| followee    | follower   |
+-------------+------------+
|     A       |     B      |
|     B       |     C      |
|     B       |     D      |
|     D       |     E      |
+-------------+------------+
1
2
3
4
5
6
7
8

应该输出：

+-------------+------------+
| follower    | num        |
+-------------+------------+
|     B       |  2         |
|     D       |  1         |
+-------------+------------+
1
2
3
4
5
6

解释：

B 和 D 都在在 follower 字段中出现，作为被关注者，B 被 C 和 D 关注，D 被 E 关注。A 不在 follower 字段内，所以A不在输出列表中。

注意：

• 被关注者永远不会被他 / 她自己关注。
• 将结果按照字典序返回。

解答：

表自连接，条件为被关注者等于关注者
然后对关注者进行分组
select f1.follower follower,count(distinct f2.follower) num 
from follow f1 join follow f2 on f1.follower=f2.followee group by f1.follower order by f1.follower
1
2
3
4

扩展：分析题意

此题最主要是理解题意，关注者和被关注者，先找到被关注者在关注者里，然后对关注者进行分组，求关注者的数。

---

40、615.平均工资：部门与公司比较

给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）。

表： salary

| id | employee_id | amount | pay_date   |
|----|-------------|--------|------------|
| 1  | 1           | 9000   | 2017-03-31 |
| 2  | 2           | 6000   | 2017-03-31 |
| 3  | 3           | 10000  | 2017-03-31 |
| 4  | 1           | 7000   | 2017-02-28 |
| 5  | 2           | 6000   | 2017-02-28 |
| 6  | 3           | 8000   | 2017-02-28 |
1
2
3
4
5
6
7
8

employee_id 字段是表 employee 中 employee_id 字段的外键。

| employee_id | department_id |
|-------------|---------------|
| 1           | 1             |
| 2           | 2             |
| 3           | 2             |
1
2
3
4
5

对于如上样例数据，结果为：

| pay_month | department_id | comparison  |
|-----------|---------------|-------------|
| 2017-03   | 1             | higher      |
| 2017-03   | 2             | lower       |
| 2017-02   | 1             | same        |
| 2017-02   | 2             | same        |
1
2
3
4
5
6

解释

在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33…

由于部门 ‘1’ 里只有一个 employee_id 为 ‘1’ 的员工，所以部门 ‘1’ 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ，所以比较结果是 ‘higher’。

第二个部门的平均工资为 employee_id 为 ‘2’ 和 ‘3’ 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 ‘lower’ 。

在二月用同样的公式求平均工资并比较，比较结果为 ‘same’ ，因为部门 ‘1’ 和部门 ‘2’ 的平均工资与公司的平均工资相同，都是 7000 。

解答：

思路一：
salary表和employee表进行连接，顺便格式化日期
select department_id,amount,date_format(pay_date,'%Y-%m') as pay_month from salary  join employee on salary.employee_id=employee.employee_id
开窗求每个月公司公司的平均值、每个月每个部门的平均值
select pay_month,department_id,amount,avg(amount) over(partition by pay_month) as all_avg,avg(amount) over(partition by pay_month,department_id) as alone_avg from (select department_id,amount,date_format(pay_date,'%Y-%m') as pay_month from salary  join employee on salary.employee_id=employee.employee_id) t
分组并比较
select h.pay_month pay_month,h.department_id department_id,
if(h.all_avg=h.alone_avg,'same',if(h.all_avg>h.alone_avg,'lower','higher')) comparison
from (select pay_month,department_id,amount,avg(amount) over(partition by pay_month) as all_avg,avg(amount) over(partition by pay_month,department_id) as alone_avg from (select department_id,amount,date_format(pay_date,'%Y-%m') as pay_month from salary  join employee on salary.employee_id=employee.employee_id) t) h group by h.pay_month,h.department_id order by h.pay_month desc
1
2
3
4
5
6
7
8
9

思路二：
计算公司每个月的平均工资
select avg(amount) as company_avg,date_format(pay_date,'%Y-%m') as pay_month
from salary
group by date_format(pay_date,'%Y-%m')
company_avg	pay_month
7000.0000	2017-02
8333.3333	2017-03
计算每个部门每个月的平均工资
select department_id,avg(amount) as department_avg,date_format(pay_date,'%Y-%m') as pay_month from salary join employee on salary.employee_id=employee.employee_id group by
department_id,pay_month
department_id	department_avg	pay_month
	1				7000.0000	2017-02
	1				9000.0000	2017-03
	2				7000.0000	2017-02
	2				8000.0000	2017-03
将第二步中的表和之前的公司数据作比较并求出结果
select department_salary.pay_month, department_id,
case
  when department_avg>company_avg then 'higher'
  when department_avg<company_avg then 'lower'
  else 'same'
end as comparison
from
(
  select department_id, avg(amount) as department_avg, date_format(pay_date, '%Y-%m') as pay_month
  from salary join employee on salary.employee_id = employee.employee_id
  group by department_id, pay_month
) as department_salary
join
(
  select avg(amount) as company_avg,  date_format(pay_date, '%Y-%m') as pay_month from salary group by date_format(pay_date, '%Y-%m')
) as company_salary
on department_salary.pay_month = company_salary.pay_month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

扩展：基本函数

date_format：date_format(datetime,fmt)

case when then else end

CASE

​ WHEN 条件1 then result1

​ WHEN 条件2 THEN result2 …

​ [ELSE resultn]

END