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 子句用于将结果集按照指定的列进行分组，通常与聚合函数（如 SUM、COUNT、AVG 等）一起使用，以便对每个分组应用这些函数并返回汇总结果。

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

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

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

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

请注意以下几点：

GROUP BY 子句应该位于 SELECT 语句的结尾，在聚合函数之后。
你可以在 SELECT 中使用聚合函数（如 SUM、AVG、COUNT 等），以对每个分组进行计算。
除了聚合函数的列，其他列在 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 中执行数据查询和处理时，以下是涉及的几个关键函数的解释和用法：

IFNULL(expression, value_if_null)
- IFNULL() 函数用于判断一个表达式是否为 NULL。如果表达式为 NULL，则返回指定的值；如果表达式不为 NULL，则直接返回表达式的值。
- 例如：IFNULL(column_name, 'N/A') 会检查 column_name 是否为 NULL，如果为 NULL，则返回 'N/A'，否则返回 column_name 的实际值。
NULLIF(expression1, expression2)
- NULLIF() 函数用于比较两个表达式是否相等。如果两个表达式的值相等，则返回 NULL；如果两个表达式的值不相等，则返回第一个表达式的值。
- 例如：NULLIF(column1, column2) 会比较 column1 和 column2 的值，如果相等则返回 NULL，否则返回 column1 的值。
ROUND(number, decimals)
- ROUND() 函数用于对一个数字进行四舍五入。
- number 是要进行四舍五入的数字，decimals 是指定的小数位数。
- 例如：ROUND(5.678, 2) 会将数字 5.678 四舍五入到小数点后两位，得到 5.68。
SUM(column)
- SUM() 函数用于计算指定列中所有数值的总和。
- 它对于统计某一列中的数值非常有用。
- 例如：SUM(sales) 会计算 sales 列中所有数值的总和。
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;

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

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

严格模式 (Strict mode)
- 在严格模式下，如果除数是 NULL，则除法操作的结果会是 NULL。这是 MySQL 的默认行为。
非严格模式
- 在非严格模式下，默认情况下会将除以 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
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！