20231230 SQL基础50题打卡

2023-12-30 15:38:54

20231230 SQL基础50题打卡

570. 至少有5名直接下属的经理


表: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+
id 是此表的主键(具有唯一值的列)。
该表的每一行表示雇员的名字、他们的部门和他们的经理的id。
如果managerId为空,则该员工没有经理。
没有员工会成为自己的管理者。

编写一个解决方案,找出至少有五个直接下属的经理。

任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Employee 表:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | Null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
输出: 
+------+
| name |
+------+
| John |
+------+

题解:

# Write your MySQL query statement below
select e.name 
from Employee as e 
where e.id in (
    select e1.managerId from Employee as e1
    group by e1.managerId
    having count(*) >=5 
)

注意知识点:

在 MySQL 中,GROUP BY 子句用于将结果集按照指定的列进行分组,通常与聚合函数(如 SUMCOUNTAVG 等)一起使用,以便对每个分组应用这些函数并返回汇总结果。

下面是一个简单的示例,展示了如何使用 GROUP BY

假设我们有一个 students 表包含学生的姓名、所属班级和分数等信息,现在想要按照班级对学生进行分组,并计算每个班级的平均分数。

SELECT class, AVG(score) AS avg_score
FROM students
GROUP BY class;

这个查询将会返回每个班级的平均分数,其中 class 是你希望进行分组的列,AVG(score) 是对分数列进行平均值计算。

请注意以下几点:

  • GROUP BY 子句应该位于 SELECT 语句的结尾,在聚合函数之后。
  • 你可以在 SELECT 中使用聚合函数(如 SUMAVGCOUNT 等),以对每个分组进行计算。
  • 除了聚合函数的列,其他列在 SELECT 中使用时,要么在 GROUP BY 子句中,要么是聚合函数中的参数。如果不是这样,MySQL会返回错误或者随机选择一个行为。

使用 GROUP BY 可以对数据进行分组汇总,这在进行统计分析或者数据摘要时非常有用。

错误示范:

select e1.managerId from Employee as e1 where e1.managerId is not null and  count(*) >= 5 
group by e1.id;

在你的查询中,你正在尝试使用 COUNT(*) 函数来筛选满足条件的记录,但是在 WHERE 子句中的 COUNT(*) 函数并不会按照你预期的方式工作。COUNT(*) 函数用于计算符合条件的行数,并不用于过滤查询结果。

如果你想筛选出满足某个条件的行,并对这些行进行计数,可以使用 HAVING 子句,因为 HAVING 子句是用于过滤分组后的结果。

以下是一个修改后的示例,演示了如何使用 HAVING 子句来筛选出符合条件的结果:

SELECT e1.managerId 
FROM Employee AS e1 
WHERE e1.managerId IS NOT NULL
GROUP BY e1.managerId
HAVING COUNT(*) >= 5;

在这个查询中,首先对 Employee 表中 managerId 不为空的记录进行分组,并且使用 HAVING 子句来筛选出满足条件的分组,即计数大于等于 5 的 managerId

1934. 确认率


表: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
User_id是该表的主键。
每一行都包含ID为user_id的用户的注册时间信息。

表: Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+
(user_id, time_stamp)是该表的主键。
user_id是一个引用到注册表的外键。
action是类型为('confirmed', 'timeout')的ENUM
该表的每一行都表示ID为user_id的用户在time_stamp请求了一条确认消息,该确认消息要么被确认('confirmed'),要么被过期('timeout')。

用户的 确认率'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位

编写一个SQL查询来查找每个用户的 确认率 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例1:

输入:
Signups 表:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+
Confirmations 表:
+---------+---------------------+-----------+
| user_id | time_stamp          | action    |
+---------+---------------------+-----------+
| 3       | 2021-01-06 03:30:46 | timeout   |
| 3       | 2021-07-14 14:00:00 | timeout   |
| 7       | 2021-06-12 11:57:29 | confirmed |
| 7       | 2021-06-13 12:58:28 | confirmed |
| 7       | 2021-06-14 13:59:27 | confirmed |
| 2       | 2021-01-22 00:00:00 | confirmed |
| 2       | 2021-02-28 23:59:59 | timeout   |
+---------+---------------------+-----------+
输出: 
+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+
解释:
用户 6 没有请求任何确认消息。确认率为 0。
用户 3 进行了 2 次请求,都超时了。确认率为 0。
用户 7 提出了 3 个请求,所有请求都得到了确认。确认率为 1。
用户 2 做了 2 个请求,其中一个被确认,另一个超时。确认率为 1 / 2 = 0.5。

题解:

# Write your MySQL query statement below
SELECT
    s.user_id,
    ROUND(IFNULL(SUM(IF(c.action = 'confirmed', 1, 0)) / NULLIF(COUNT(c.user_id), 0), 0), 2) AS confirmation_rate
FROM
    Signups AS s
LEFT JOIN
    Confirmations AS c
ON
    s.user_id = c.user_id
GROUP BY
    s.user_id;

当在 SQL 中执行数据查询和处理时,以下是涉及的几个关键函数的解释和用法:

  1. IFNULL(expression, value_if_null)

    • IFNULL() 函数用于判断一个表达式是否为 NULL。如果表达式为 NULL,则返回指定的值;如果表达式不为 NULL,则直接返回表达式的值。
    • 例如:IFNULL(column_name, 'N/A') 会检查 column_name 是否为 NULL,如果为 NULL,则返回 'N/A',否则返回 column_name 的实际值。
  2. NULLIF(expression1, expression2)

    • NULLIF() 函数用于比较两个表达式是否相等。如果两个表达式的值相等,则返回 NULL;如果两个表达式的值不相等,则返回第一个表达式的值。
    • 例如:NULLIF(column1, column2) 会比较 column1column2 的值,如果相等则返回 NULL,否则返回 column1 的值。
  3. ROUND(number, decimals)

    • ROUND() 函数用于对一个数字进行四舍五入。
    • number 是要进行四舍五入的数字,decimals 是指定的小数位数。
    • 例如:ROUND(5.678, 2) 会将数字 5.678 四舍五入到小数点后两位,得到 5.68
  4. SUM(column)

    • SUM() 函数用于计算指定列中所有数值的总和。
    • 它对于统计某一列中的数值非常有用。
    • 例如:SUM(sales) 会计算 sales 列中所有数值的总和。
  5. COUNT(column)

    • COUNT() 函数用于计算指定列中非 NULL 值的数量。
    • 它通常用于统计行数或某一列中非 NULL 值的数量。
    • 例如:COUNT(user_id) 会统计 user_id 列中非 NULL 值的数量。

在给出的 SQL 查询中,结合了以上函数:

SELECT
    s.user_id,
    ROUND(IFNULL(SUM(IF(c.action = 'confirmed', 1, 0)) / NULLIF(COUNT(c.user_id), 0), 0), 2) AS confirmation_rate
FROM
    Signups AS s
LEFT JOIN
    Confirmations AS c
ON
    s.user_id = c.user_id
GROUP BY
    s.user_id;
  1. IFNULL() 用于处理 SUM(IF(c.action = 'confirmed', 1, 0)) / COUNT(c.user_id) 的结果,以防止除以零的错误。如果除数为零,则返回 0。
  2. SUM(IF(c.action = 'confirmed', 1, 0)) 计算了 Confirmations 表中 action 列为 'confirmed' 的数量,表示确认消息的次数。
  3. COUNT(c.user_id) 统计了 Confirmations 表中每个用户发起的请求总数。
  4. 整个表达式计算每个用户的确认率,并使用 ROUND() 对结果进行四舍五入,保留两位小数。
  5. 最后通过 GROUP BY s.user_id 对结果进行分组,以便得到每个用户的确认率。

在 MySQL 中,当除法操作中的除数是 NULL 时,结果会是 NULL 或者取决于 SQL_MODE 中的设置。MySQL 的行为可以根据不同的 SQL_MODE 设置而有所不同。

  1. 严格模式 (Strict mode)

    • 在严格模式下,如果除数是 NULL,则除法操作的结果会是 NULL。这是 MySQL 的默认行为。
  2. 非严格模式

    • 在非严格模式下,默认情况下会将除以 NULL 视为 0,即除数为 NULL 时,除法操作的结果会是 0。这种行为可以通过设置 SET sql_mode='traditional'; 实现。

例如,在非严格模式下:

SELECT 10 / NULL; -- 结果将会是 0

但是,在严格模式下,这个查询将返回 NULL:

SET sql_mode='STRICT_ALL_TABLES';
SELECT 10 / NULL; -- 结果将会是 NULL

这种行为可以通过 SQL_MODE 的设置来控制,但是一般来说,除以 NULL 会返回 NULL,除非显式地设置了非严格模式以改变默认行为。

620. 有趣的电影


表:cinema

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| id             | int      |
| movie          | varchar  |
| description    | varchar  |
| rating         | float    |
+----------------+----------+
id 是该表的主键(具有唯一值的列)。
每行包含有关电影名称、类型和评级的信息。
评级为 [0,10] 范围内的小数点后 2 位浮点数。

编写解决方案,找出所有影片描述为 boring (不无聊) 的并且 id 为奇数 的影片。

返回结果按 rating 降序排列

结果格式如下示例。

示例 1:

输入:
+---------+-----------+--------------+-----------+
|   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     |
+---------+-----------+--------------+-----------+
解释:
我们有三部电影,它们的 id 是奇数:1、3 和 5。id = 3 的电影是 boring 的,所以我们不把它包括在答案中。

题解:

# Write your MySQL query statement below
select * 
from cinema 
where description != 'boring' and Mod(id,2) = 1
order by rating desc; 

在 MySQL 中,你可以使用模运算(MOD() 函数)来判断一个数是否为奇数。奇数是指不能被 2 整除的整数。当一个数除以 2 的余数为 1 时,它就是奇数。

以下是使用 MOD() 函数来判断一个数是否为奇数的示例:

SELECT number, 
    CASE 
        WHEN MOD(number, 2) = 1 THEN '奇数'
        ELSE '偶数'
    END AS 奇偶判断
FROM your_table;

在这个示例中,number 是你要判断的数值列,your_table 是包含这个列的表。MOD(number, 2) 计算了 number 除以 2 的余数,然后使用 CASE 表达式根据余数是否为 1 来判断是奇数还是偶数。

如果 MOD(number, 2) 的结果为 1,CASE 表达式会返回 '奇数',否则返回 '偶数'

文章来源:https://blog.csdn.net/Go_ahead_forever/article/details/135305769
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。