LeetCode数据库SQL练习（三）

• 练习题目来自：leetcode-cn.com/

当选者

表: Candidate

+-----+---------+
| id  | Name    |
+-----+---------+
| 1   | A       |
| 2   | B       |
| 3   | C       |
| 4   | D       |
| 5   | E       |
+-----+---------+  
Id是该表的主键列。
该表的每一行都包含关于候选对象的id和名称的信息。

表: Vote

+-----+--------------+
| id  | CandidateId  |
+-----+--------------+
| 1   |     2        |
| 2   |     4        |
| 3   |     3        |
| 4   |     2        |
| 5   |     5        |
+-----+--------------+
id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.
该表的每一行决定了在选举中获得第i张选票的候选人。

请编写 sql 语句来找到当选者的名字(即获得最多选票的候选人)，上面的例子将返回当选者 B.

候选人B有2票。候选人C、D、E各有1票。获胜者是候选人B。

+------+
| Name |
+------+
| B    |
+------+

注意: 你可以假设没有平局，换言之，最多只有一位当选者。

• SQL1：

  • 子查询

  • select
    Name
    from
    Candidate
    where id = (select
                CandidateId
                from
                Vote
                group by CandidateId
                order by count(CandidateId) DESC
                limit 1)
    

• SQL2：

  • Join方法

  • select
    Name
    from
    Candidate c join Vote v
    on c.id = v.CandidateId
    group by v.CandidateId
    order by count(v.CandidateId)
    limit 1
    

• SQL3：

  • 优化Join

  • select
    Name
    from
        (select
        CandidateId id
        from
        Vote
        group by CandidateId
        order by count(id) DESC
        limit 1) t join Candidate
    on t.id = Candidate.id
    

• SQL4：

  • 窗口函数

  • select
    Name
    from
    (select
        distinct CandidateId id,
        count(id) over(partition by CandidateId) cnt
        from
        Vote
        order by cnt DESC
        limit 1) t join Candidate
    on t.id = Candidate.id
    

员工奖金

选出所有 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 是这张表单的主关键字

输出示例：

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

• SQL1：

  • select
    e.name name, b.bonus bonus
    from
    Employee e left join Bonus b
    on e.empId = b.empId
    where bonus < 1000 or bonus is null
    

• SQL2：

  • select
    e.name name, b.bonus bonus
    from
    Employee e left join Bonus b
    on e.empId = b.empId
    where ifnull(b.bonus, 0) < 1000
    

查询回答率最高的问题

从 SurveyLog 表中获得回答率最高的问题，SurveyLog 表包含这些列 ：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 查询来找到具有最高回答率的问题。如果有多个问题具有相同的最大 回答率 ，返回 question_id 最小的那个。

示例：

输入：
+------+-----------+--------------+------------+-----------+------------+
| 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 。

提示： 回答率最高的含义是：同一问题编号中回答数占显示数的比例最高。

• SQL1：

  • 两个子查询Join

  • select
    qs.question_id survey_log
    from
        (select
        question_id, count(*) a
        from
        SurveyLog
        where action = 'answer'
        group by question_id) qa
    join
        (select
        question_id, count(*) s
        from
        SurveyLog
        where action = 'show'
        group by question_id) qs
    on qa.question_id = qs.question_id
    order by (qa.a / qs.s) DESC
    limit 1
    

• SQL2：

  • 一个子查询判断

  • t.question_id survey_log
    from
        (select
        question_id,
        sum(if(action = 'answer', 1, 0)) sum_a,
        sum(if(action = 'show', 1, 0)) sum_s
        from
        SurveyLog
        group by question_id) t
    order by (sum_a / sum_s) DESC
    limit 1
    

• SQL3：

  • 直接计算

  • select
    question_id survey_log
    from
    SurveyLog
    group by question_id
    order by (sum(if(action = 'answer', 1, 0)) / sum(if(action = 'show', 1, 0))) DESC
    limit 1
    

• SQL4：

  • 根据题目机制，统计回答数

  • select
    question_id survey_log
    from
    surveyLog
    group by question_id
    order by count(answer_id) DESC
    limit 1
    

查询员工的累计薪水

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     |

• SQL1：

  • select
    e1.Id,
    e1.Month,
    (ifnull(e1.Salary, 0) + ifnull(e2.Salary, 0) + ifnull(e3.Salary, 0)) Salary
    from
        (select
        Id, max(month) month
        from
        Employee
        group by Id
        having count(*) > 1
        ) t left join Employee e1
        on (t.Id = e1.Id and t.month > e1.Month)
        left join Employee e2
        on (e2.Id = e1.Id and e2.Month = e1.Month - 1)
        left join Employee e3
        on (e3.Id = e1.Id and e3.Month = e1.Month - 2)
        order by e1.Id, e1.Month DESC
    

• 窗口函数：

  • 默认从起点到当前行
  • 当前行到前n行：
    • n PRECEDING
  • 当前行到后n行：
    • n FOLLOWING
  • 当前行：
    • CURRENT ROW
  • 窗口第一行：
    • UNBOUNDED PRECEDING
  • 窗口的最后一行：
    • UNBOUNDED FOLLOWING
  • ROWS和RANGE：
    • 用来限制窗口的范围（行数）
    • ROWS：固定行号间隔
    • RANGE：包含窗口里的所有行
  • 举例：
    • 从起点到当前行：
    • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
    • 从当前行+前3行：
    • ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    • 从当前行+前3行+往后1行：
    • ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
    • 从当前行往后所有行：
    • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

• SQL2：

  • 窗口函数

  • select
    t.Id Id, t.Month Month, t.Salary Salary
    from
        (select
        Id, Month,
        sum(Salary) over(partition by Id order by Month RANGE 2 PRECEDING) Salary,
        RANK() over(partition by Id order by Month DESC) rk
        from
        Employee) t
    where t.rk > 1
    order by Id, Month DESC
    

• SQL3：

  • 窗口函数

  • select
    Id, Month,
    sum(Salary) over(partition by Id order by Month RANGE 2 PRECEDING) Salary
    from
    Employee
    where (Id, Month) not in (select Id, max(Month) from Employee group by Id)
    order by Id, Month DESC
    

统计各专业学生人数

一所大学有 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    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
Student_id是该表的主键。
dept_id是Department表中dept_id的外键。
该表的每一行都表示学生的姓名、性别和所属系的id。

这里是一个示例输入：
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              |
+-------------+----------------+

• COUNT(expression)语句，因为如果 expression is null，那么这条记录不会被计数。

• SQL：

  • select
    t.dept_name dept_name, count(t.student_id) student_number
    from
        (select
        s.student_id, d.dept_name
        from
        Student s right join Department d
        on s.dept_id = d.dept_id) t
    group by t.dept_name
    order by student_number DESC, dept_name
    

寻找用户推荐人

给定表customer，里面保存了所有客户信息和他们的推荐人。

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

• SQL：

  • select
    name
    from
    customer
    where referee_id != 2 or referee_id is null
    

2016年的投资

写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。 对于一个投保人，他在 2016 年成功投资的条件是：

1. 他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
2. 他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。 输入格式:
   表 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 。

• SQL1：

  • 两个条件Join

  • select
    round(cast(sum(a.TIV_2016) as float), 2) TIV_2016
    from
    (select
    PID, TIV_2016
    from
    insurance
    where TIV_2015 in (select
                        TIV_2015
                        from
                        insurance
                        group by TIV_2015
                        having count(TIV_2015) > 1)) a
    join
    (select
    t.PID, t.TIV_2016
    from
        (select
        PID, TIV_2016,
        concat(LAT, LON) loc
        from
        insurance) t
    group by loc
    having count(loc) <= 1) b
    on a.PID = b.PID
    

• SQL1：

  • 合并子查询

  • select
    round(cast(sum(TIV_2016) as float), 2) TIV_2016
    from
    insurance
    where 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)
    

• SQL3：

  • 窗口函数

  • select
    round(sum(tiv_2016), 2) TIV_2016
    from
        (select
        TIV_2016,
        count(*) over(partition by tiv_2015) c1,
        count(*) over(partition by lat, lon) c2
        from
        insurance) t
    where c1 > 1 and c2 = 1
    

订单最多的客户

在表 orders 中找到订单数最多客户对应的 customer_number 。
数据保证订单数最多的顾客恰好只有一位。
表 orders 定义如下：

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
Order_number是该表的主键。
此表包含关于订单ID和客户ID的信息。

样例输入

+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+

样例输出

+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+

解释

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

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

• SQL1：

  • select
    customer_number
    from
    orders
    group by customer_number
    order by count(customer_number) DESC
    limit 1
    

• 并列查询

• SQL2：

  • 与最大值相等的所有订单数

  • select
    customer_number
    from
    orders
    group by customer_number
    having count(*) = (select
                        count(*) cnt
                        from
                        orders
                        group by customer_number
                        order by cnt DESC
                        limit 1)
    

• SQL3：

  • 窗口函数

  • select
    t.customer_number customer_number
    from
        (select
        customer_number,
        DENSE_RANK() over(order by count(customer_number) DESC) rk
        from
        orders
        group by customer_number) t
    where rk = 1
    

大的国家

这里有张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     |
+-----------------+------------+------------+--------------+---------------+

name 是这张表的主键。
这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。

如果一个国家满足下述两个条件之一，则认为该国是 大国 ：

• 面积至少为 300 平方公里（即，3000000 km2），或者
• 人口至少为 2500 万（即 25000000） 编写一个 SQL 查询，输出表中所有大国的名称、人口和面积。按 任意顺序 返回结果表。
  例如，根据上表，我们应该输出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

• SQL：

  • select
    name, population, area
    from
    World
    where population >= 25000000 or area >= 3000000
    

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

解释:

-数学课有6个学生，所以我们包括它。
-英语课有1名学生，所以我们不包括它。
-生物课有1名学生，所以我们不包括它。
-计算机课有1个学生，所以我们不包括它。

提示： 学生在每个课中不应被重复计算。

• SQL：

  • select
    class
    from
    courses
    group by class
    having count(*) >= 5