以下题目都来自LeetCode
21、574.当选者
+
| id | Name |
+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+
+
| id | CandidateId |
+
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
+
id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.
请编写 sql 语句来找到当选者的名字，上面的例子将返回当选者 B.
+
| Name |
+
| B |
+
解答：
select CandidateId from Vote group by CandidateId order by count(CandidateId) desc limit 1
select Name from Candidate where id=(select CandidateId from Vote group by CandidateId order by count(CandidateId) desc limit 1)
22、577.员工奖金
选出所有 bonus < 1000 的员工的 name 及其 bonus。
+
| empId | name | supervisor| salary |
+
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 3 | Brad | null | 4000 |
| 4 | Thomas | 3 | 4000 |
+
empId 是这张表单的主关键字
+
| empId | bonus |
+
| 2 | 500 |
| 4 | 2000 |
+
empId 是这张表单的主关键字
输出示例：
+
| name | bonus |
+
| John | null |
| Dan | 500 |
| Brad | null |
+
解答：
select name,bonus from Employee e left join Bonus b on e.empId=b.empId where b.bonus<1000 or b.bonus is null;
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 。
解答：
select question_id as `survey_log` from survey_log group by question_id order by count(answer_id) desc limit 1
扩展：分组求第一
此题和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’ 除去最近一个月（月份 ‘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 |
员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。
| Id | Month | Salary |
|
| 2 | 1 | 20 |
员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘3’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下
| Id | Month | Salary |
|
| 3 | 3 | 100 |
| 3 | 2 | 40 |
解答：
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
25、580.统计各专业学生人数
一所大学有 2 个数据表，分别是 student 和 department ，这两个表保存着每个专业的学生数据和院系数据。
写一个查询语句，查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。
将你的查询结果按照学生人数降序排列。 如果有两个或两个以上专业有相同的学生数目，将这些部门按照部门名字的字典序从小到大排列。
student 表格如下：
| Column Name | Type |
|
| student_id | Integer |
| student_name | String |
| gender | Character |
| dept_id | Integer |
其中， student_id 是学生的学号， student_name 是学生的姓名， gender 是学生的性别， dept_id 是学生所属专业的专业编号。
department 表格如下：
| Column Name | Type |
|
| dept_id | Integer |
| dept_name | String |
dept_id 是专业编号， dept_name 是专业名字。
这里是一个示例输入：
| student_id | student_name | gender | dept_id |
|
| 1 | Jack | M | 1 |
| 2 | Jane | F | 1 |
| 3 | Mark | M | 2 |
| dept_id | dept_name |
|
| 1 | Engineering |
| 2 | Science |
| 3 | Law |
| dept_name | student_number |
|
| Engineering | 2 |
| Science | 1 |
| Law | 0 |
解答：
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
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
26、584.寻找用户推荐人
+
| id | name | referee_id|
+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
+
写一个查询语句，返回一个客户列表，列表中客户的推荐人的编号都 不是 2。
对于上面的示例数据，结果为：
+
| name |
+
| Will |
| Jane |
| Bill |
| Zack |
+
解答：
select
name
from
customer
where
referee_id is null
or
referee_id != 2;
扩展：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) |
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 |
样例输出
| TIV_2016 |
|
| 45.00 |
解释
就如最后一个投保人，第一个投保人同时满足两个条件：
1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。
第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。
所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和，结果是 45 。
解答：
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)
;
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) |
样例输入
| 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 |
样例输出
| customer_number |
|
| 3 |
解释
customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单
所以结果是该顾客的 customer_number ，也就是 3 。
进阶： 如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？
解答：
select
customer_number
from
orders
group by
customer_number
order by
count(customer_number) desc
limit
1
;
29、595.大的国家
+
| 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 |
+
+
| name | population | area |
+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+
解答：
select
name,
population,
area
from
world
where
area>3000000
or
population>25000000
;
select
name,
population,
area
from
world
where
area>3000000
;
select
name,
population,
area
from
world
where
population>25000000
;
select
name,
population,
area
from
world
where
area>3000000
union
select
name,
population,
area
from
world
where
population>25000000
30、596.超过5名学生的课
+
| student | class |
+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+
+
| class |
+
| Math |
+
解答：
select
class
from
courses
group by
class
having
count(distinct student)>=5
;
select
class
count(distinct student) count
from
courses
group by
class
;
select
class
from
(select
class
count(distinct student) as num
from
courses
group by
class) s
where
s.count>=5
;
扩展：
去重distinct
31、597.好友申请I：总体通过率
在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。
表：FriendRequest
+
| Column Name | Type |
+
| sender_id | int |
| send_to_id | int |
| request_date | date |
+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。
+
| 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
进阶:
你能写一个查询语句得到每个月的通过率吗？
你能求出每一天的累计通过率吗？
解答：
先求出通过人数，因为通过可能重复，所以得去重，如果用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
;
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 连续的记录。
解答：
第一步:因为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
| id | visit_date | people | id | visit_date | people | id | visit_date | people |
|---|
| 1 | 2017-01-01 | 10 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 4 | 2017-01-04 | 99 | | 3 | 2017-01-03 | 150 | 4 | 2017-01-04 | 99 | 5 | 2017-01-05 | 145 | | 4 | 2017-01-04 | 99 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
最终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
；
扩展：
求连续，可以用自连接加条件
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 |
写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据，结果为：
| id | num |
|
| 3 | 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;
扩展：
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 |
对于如上样例，你的查询语句应该返回如下结果。
| seat_id |
|
| 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
扩展：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：截取出目标页数据
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 |
+
表 salesperson 存储了所有销售员的信息。每个销售员都有一个销售员编号 sales_id 和他的名字 name 。
表： company
+
| com_id | name | city |
+
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
+
表 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 |
+
表 orders 存储了所有的销售数据，包括销售员编号 sales_id 和公司编号 com_id 。
输出
+
| name |
+
| Amy |
| Mark |
| Alex |
+
解释
根据表 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'))
36、608.树节点
给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
+
| id | p_id |
+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+
树中每个节点属于以下三种类型之一：
叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
+
| id | Type |
+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+
解释
节点 ‘1’ 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 ‘2’ 和 ‘3’ 。
节点 ‘2’ 是内部节点，因为它有父节点 ‘1’ ，也有孩子节点 ‘4’ 和 ‘5’ 。
节点 ‘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
37、612.平面上的最近距离
表 point_2d 保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。
写一个查询语句找到两点之间的最近距离，保留 2 位小数。
| x | y |
|
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
最近距离在点 (-1,-1) 和(-1,-2) 之间，距离为 1.00 。所以输出应该为：
| shortest |
|
| 1.00 |
**注意：**任意点之间的最远距离小于 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
扩展：基础函数
round(x,y): 返回参数x的四舍五入的有y位的小数的值
sqrt(x): 返回x的平方根
pow(x,y):返回x的y次方
38、613.直线上的最近距离
表 point 保存了一些点在 x 轴上的坐标，这些坐标都是整数。
写一个查询语句，找到这些点中最近两个点之间的距离。
| x |
|
| -1 |
| 0 |
| 2 |
最近距离显然是 ‘1’ ，是点 ‘-1’ 和 ‘0’ 之间的距离。所以输出应该如下：
| shortest|
|
| 1 |
注意：每个点都与其他点坐标不同，表 table 不会有重复坐标出现。
进阶：如果这些点在 x 轴上从左到右都有一个编号，输出结果时需要输出最近点对的编号呢？
解答：
自连接加条件
select min(abs(p1.x-p2.x)) as shorttest
from point p1 join point p2 on p1.x!=p2.x
39、614.二级关注者
在 facebook 中，表 follow 会有 2 个字段： followee, follower ，分别表示被关注者和关注者。
请写一个 sql 查询语句，对每一个关注者，查询关注他的关注者的数目。
比方说：
+
| followee | follower |
+
| A | B |
| B | C |
| B | D |
| D | E |
+
应该输出：
+
| follower | num |
+
| B | 2 |
| D | 1 |
+
解释：
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
扩展：分析题意
此题最主要是理解题意，关注者和被关注者，先找到被关注者在关注者里，然后对关注者进行分组，求关注者的数。
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 |
employee_id 字段是表 employee 中 employee_id 字段的外键。
| employee_id | department_id |
|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
对于如上样例数据，结果为：
| pay_month | department_id | comparison |
|
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower |
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |
解释
在三月，公司的平均工资是 (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
思路二：
计算公司每个月的平均工资
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
扩展：基本函数
date_format：date_format(datetime,fmt)
| 格式符 | 说明 | 格式符 | 说明 |
|---|
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 | | %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03。。。） | | %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） | | %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) | | %e | 数字形式表示月中的天数（1,2,3,4,5…） | | | | %H | 两位数字表示小数，24小时制（01,02…） | %h和%I | 两位数字表示小时，12小时制（01,02…） | | %k | 数字形式的小时，24小时制(1,2,3) | %l | 数字形式表示小时，12小时制（1,2,3,4…） | | %i | 两位数字表示分钟（00,01,02） | %S和%s | 两位数字表示秒(00,01,02…) | | %W | 一周中的星期名称（Sunday…） | %a | 一周中的星期缩写（Sun.，Mon.,Tues.，…） | | %w | 以数字表示周中的天数(0=Sunday,1=Monday…) | | | | %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 | | %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | | | | %T | 24小时制 | %r | 12小时制 | | %p | AM或PM | %% | 表示% |
case when then else end
CASE
? WHEN 条件1 then result1
? WHEN 条件2 THEN result2 …
? [ELSE resultn]
END
|