likes
comments
collection
share

LeetCode数据库SQL练习（五）

作者站长头像
站长
· 阅读数 57

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

给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）。 表： 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 查询，按产品 id product_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
转载自:https://juejin.cn/post/7076842728239136782
评论
请登录