LeetCode数据库SQL练习（五）

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

平均工资：部分与公司比较

给如下两个表，写一个查询句子，求出在每一个工资发放日，每个部分的平均工资与公司的平均工资的比较成果 （高 / 低 / 相同）。
表：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 。

• DATE_FORMAT()

  • 用于以不同的格局显现日期/时刻数据。
  • DATE_FORMAT(date,format)
  • date 参数是合法的日期。format 规定日期/时刻的输出格局。

• SQL1：

  • Join函数

  • select
    a.pay_month pay_month, a.department_id department_id,
    if(a.d_salary > b.c_salary, 'higher', if(a.d_salary = b.c_salary, 'same', 'lower')) comparison
    from
    (select
    avg(t.amount) d_salary, t.department_id, date_format(t.pay_date, '%Y-%m') pay_month
    from
    (select
    s.employee_id, s.amount, s.pay_date, e.department_id
    from
    salary s join employee e
    on s.employee_id = e.employee_id) t
    group by date_format(t.pay_date, '%Y-%m'), t.department_id) a
    join
    (select
    avg(amount) c_salary, date_format(pay_date, '%Y-%m') pay_month
    from
    salary
    group by date_format(pay_date, '%Y-%m')) b
    on a.pay_month = b.pay_month
    order by a.pay_month DESC, a.department_id
    

• SQL2：

  • 窗口函数

  • select
    distinct date_format(t.pay_date, '%Y-%m') pay_month,
    t.department_id department_id,
    case
    when t.d_salary > t.c_salary then 'higher'
    when t.d_salary = t.c_salary then 'same'
    else 'lower'
    end comparison
    from
    (select
    s.amount, s.pay_date, e.department_id,
    avg(s.amount) over(partition by s.pay_date) c_salary,
    avg(s.amount) over(partition by e.department_id, s.pay_date) d_salary
    from
    salary s join employee e
    on s.employee_id = e.employee_id) t
    order by t.pay_date DESC, t.department_id
    

学生地理信息陈述

一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下student表中。

+--------+-----------+
| name   | continent |
|--------|-----------|
| Jack   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jane   | America   |
+--------+-----------+

写一个查询句子实现对大洲（continent）列的透视表操作，使得每个学生依照姓名的字母次序顺次摆放在对应的大洲下面。输出的标题应顺次为美洲（America）、亚洲（Asia）和欧洲（Europe）。

关于样例输入，它的对应输出是：

+---------+------+--------+
| America | Asia | Europe |
|---------|------|--------|
| Jack    | Xi   | Pascal |
| Jane    |      |        |
+---------+------+--------+

进阶： 假设不能确认哪个大洲的学生数最多，你可以写出一个查询去生成上述学生陈述吗？

case when
1.枚举函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
2.搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END

• 窗口函数：

• SQL1：

  • 需求提前知道单个类别的最大值，作为t1

  • select
    America, Asia, Europe
    from
        (select
        name America,
        ROW_NUMBER() over(order by name) rn
        from
        student
        where continent = 'America') t1
        left join
        (select
        name Asia,
        ROW_NUMBER() over(order by name) rn
        from
        student
        where continent = 'Asia') t2
        on t1.rn = t2.rn
        left join
        (select
        name Europe,
        ROW_NUMBER() over(order by name) rn
        from
        student
        where continent = 'Europe') t3
        on t1.rn = t3.rn
    

• SQL2：

  • 知道各个类别即可

  • select
    max(case continent when 'America' then name else NULL end) America,
    max(case continent when 'Asia' then name else NULL end) Asia,
    max(case continent when 'Europe' then name else NULL end) Europe
    from
    (select
    *,
    ROW_NUMBER() over(partition by continent order by name) rk
    from
    student) t
    group by rk
    

只呈现一次的最大数字

MyNumbers 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+
这张表没有主键。或许包含重复数字。
这张表的每一行都含有一个整数。

单一数字 是在 MyNumbers 表中只呈现一次的数字。
请你编写一个 SQL 查询来陈述最大的 单一数字 。假设不存在 单一数字 ，查询需陈述 null 。

查询成果如下例所示。
示例 1：

输入：
MyNumbers 表：
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
输出：
+-----+
| num |
+-----+
| 6   |
+-----+
解说：单一数字有 1、4、5 和 6 。
6 是最大的单一数字，回来 6 。

示例 2：

输入：
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
输出：
+------+
| num  |
+------+
| null |
+------+
解说：输入的表中不存在单一数字，所以回来 null 。

留意： 假设没有只呈现一次的数字，输出null。

• SQL1：

  • 运用聚合函数对空值进行处理时，SUM/AVG/MAX/MIN都会回来null值

  • select
    max(t.num) num
    from
    (select
    num, count(num) cnt
    from
    MyNumbers
    group by num
    having cnt < 2
    order by num DESC
    limit 1
    ) t
    

• SQL2：

  • select句子中写入空值，直接运转select句子，我们将会得到null值

  • select(
      select num
      from MyNumbers
      group by num
      having count(num) = 1
      ORDER BY num DESC
      LIMIT 1
    ) num
    

风趣的电影

某城市开了一家新的电影院，招引了很多人过来看电影。该电影院特别留意用户体会，专门有个 LED显现板做电影引荐，上面发布着影评和相关电影描绘。
作为该电影院的信息部主管，您需求编写一个 SQL查询，找出一切影片描绘为非boring(不无聊)的并且 id 为奇数 的影片，成果请按等级rating摆放。

例如，下表cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

关于上面的例子，则正确的输出是为：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

• mod(N,M)：

  • 回来N除以M后的余数
    

• MySQL 中判别奇数的 6 种方法：

  • mod(x, 2) = 1，假设余数是 1 便是奇数。
  • power(-1, x) = -1， 假设成果是 -1 便是奇数
  • x % 2 = 1，假设余数是 1 便是奇数。
  • x & 1 = 1，假设是 1 便是奇数
  • x regexp '[1, 3, 5, 7, 9]$' = 1 假设为 1 便是奇数
  • x>>1<<1 != x 假设右移一位在左移一位不等于原值，便是奇数

• SQL：

  • select
    id, movie, description, rating
    from
    cinema
    where description != 'boring' and id%2 = 1
    order by rating DESC
    

换座位

小美是一所中学的信息科技老师，她有一张seat座位表，平时用来贮存学生名字和与他们相对应的座位 id。
其间纵列的id 是连续递加的，小美想改动相邻俩学生的座位。

你能不能帮她写一个 SQL query来输出小美想要的成果呢？

示例：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

假设数据输入的是上表，则输出成果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

留意： 假设学生人数是奇数，则不需求改动最终一个同学的座位。

• SQL1：

  • select
    (case
    when mod(id, 2) = 1 and cnt != id then id + 1
    when mod(id, 2) = 1 and cnt = id then id
    else id - 1
    end) id,
    student
    from
    seat,
    (select
    count(*) cnt
    from
    seat) t
    order by id
    

• SQL2：

  • 窗口函数

  • select
    if(mod(t.id, 2) = 1, t.lag, t.lead) student
    from
    (select
    id, student,
    LAG(student, 1, student) lag
    LEAD(student, 1, student) lead
    from
    seat) t
    

• coalesce()：

  • 回来参数中的第一个非空表达式（从左向右顺次类推）

• SQL3：

  • 运用 (id+1)^1-1 核算交换后每个学生的座位id

  • select
    s1.id, coalesce(s2.student, s1.student) student
    from
    seat s1 left join seat s2
    on ((s1.id + 1)  1) - 1 = s2.id
    order by s1.id
    

改变性别

给定一个salary表，如下所示，有 m = 男性 和 f = 女性 的值。交换一切的 f 和 m 值（例如，将一切 f 值更改为 m，反之亦然）。要求只运用一个更新（Update）句子，并且没有中心的暂时表。

留意，您必只能写一个 Update 句子，请不要编写任何 Select 句子。
例如：

+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
+----+------+-----+--------+

运转你所编写的更新句子之后，将会得到以下表:

+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |
+----+------+-----+--------+
解说：
(1, A) 和 (3, C) 从 'm' 变为 'f' 。
(2, B) 和 (4, D) 从 'f' 变为 'm' 。

• SQL1：

  • update
    salary
    set
    sex = case sex
    when 'm' then 'f'
    else 'm'
    end
    

• SQL2：

  • update
    salary
    set sex = if(sex = 'm', 'f', 'm')
    

• SQL3：

  • update
    salary
    set sex = char(ascii('m') + ascii('f') - ascii(sex))
    

买下一切产品的客户

Customer表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+
product_key 是 Customer 表的外键。

Product表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_key | int     |
+-------------+---------+
product_key 是这张表的主键。

写一条 SQL 查询句子，从Customer表中查询购买了Product表中一切产品的客户的 id。
示例：

Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
Product 表：
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
Result 表：
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
购买了一切产品（5 和 6）的客户的 id 是 1 和 3 。

• 由于product table的产品数目固定，所以只要 group by customer_id 后只要他们中就相当于购买了悉数的产品 having count distinct product_key = product.product_key

• SQL：

  • select
    customer_id
    from
    Customer
    group by customer_id
    having count(distinct product_key) >= (select
                                          count(distinct product_key)
                                          from
                                          Product)
    

合作过至少三次的演员和导演

ActorDirector表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| actor_id    | int     |
| director_id | int     |
| timestamp   | int     |
+-------------+---------+
timestamp 是这张表的主键.

写一条SQL查询句子获取合作过至少三次的演员和导演的 id 对(actor_id, director_id)
示例：

ActorDirector 表：
+-------------+-------------+-------------+
| actor_id    | director_id | timestamp   |
+-------------+-------------+-------------+
| 1           | 1           | 0           |
| 1           | 1           | 1           |
| 1           | 1           | 2           |
| 1           | 2           | 3           |
| 1           | 2           | 4           |
| 2           | 1           | 5           |
| 2           | 1           | 6           |
+-------------+-------------+-------------+
Result 表：
+-------------+-------------+
| actor_id    | director_id |
+-------------+-------------+
| 1           | 1           |
+-------------+-------------+
仅有的 id 对是 (1, 1)，他们恰好合作了 3 次。

• SQL：

  • select
    actor_id, director_id
    from
    ActorDirector
    group by actor_id, director_id
    having count(*) >= 3
    

产品出售剖析系列

出售表Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) 是出售表 Sales 的主键.
product_id 是关联到产品表 Product 的外键.
留意: price 表示每单位价格

产品表Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id是表的主键.

1、写一条SQL查询句子获取Sales表中一切产品对应的产品名称 product_name以及该产品的一切售卖年份 year和价格 price。

示例：

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Result 表：
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

• SQL：

  • select
    p.product_name product_name, s.year year, s.price price
    from
    Sales s join Product p
    on s.product_id = p.product_id
    

2、编写一个 SQL 查询，按产品 idproduct_id来计算每个产品的出售总量。
查询成果格局如下面例子所示:

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Result 表：
+--------------+----------------+
| product_id   | total_quantity |
+--------------+----------------+
| 100          | 22             |
| 200          | 15             |
+--------------+----------------+

• SQL1：

  • select
    product_id, sum(quantity) total_quantity
    from
    Sales
    group by product_id
    

• SQL2：

  • 窗口函数

  • select
    distinct product_id,
    sum(quantity) over(partition by product_id) total_quantity
    from
    Sales
    

3、编写一个 SQL 查询，选出每个出售产品的第一年的产品 id、年份、数量 和价格。

查询成果格局如下：

Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Result table:
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

• SQL1：

  • 子查询

  • select
    product_id, year first_year, quantity, price
    from
    Sales
    where (product_id, year) in
                                (select
                                product_id, min(year)
                                from
                                Sales
                                group by product_id)
    

• SQL2：

  • Join方法

  • select
    s.product_id, t.first_year, s.quantity, s.price
    from
    (select
    product_id, min(year) first_year
    from
    Sales
    group by product_id) t
    left join Sales s
    on t.product_id = s.product_id and t.first_year = s.year
    

• SQL3：

  • 窗口函数

  • select
    product_id, year first_year, quantity, price
    from
    (select
    product_id, year, quantity, price,
    rank() over(partition by product_id order by year) rk
    from
    Sales) t
    where rk = 1