20240106 SQL基础50题打卡
20240106 SQL基础50题打卡
1164. 指定日期的产品价格
产品数据表: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| change_date | date |
+---------------+---------+
(product_id, change_date) 是此表的主键(具有唯一值的列组合)。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
编写一个解决方案,找出在 2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是 10
。
以 任意顺序 返回结果表。
结果格式如下例所示。
示例 1:
输入:
Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1 | 20 | 2019-08-14 |
| 2 | 50 | 2019-08-14 |
| 1 | 30 | 2019-08-15 |
| 1 | 35 | 2019-08-16 |
| 2 | 65 | 2019-08-17 |
| 3 | 20 | 2019-08-18 |
+------------+-----------+-------------+
输出:
+------------+-------+
| product_id | price |
+------------+-------+
| 2 | 50 |
| 1 | 35 |
| 3 | 10 |
+------------+-------+
题解:
select p1.product_id, ifnull(p2.new_price, 10) as price
from (
select distinct product_id
from products
) as p1 -- 所有的产品
left join (
select product_id, new_price
from products
where (product_id, change_date) in (
select product_id, max(change_date)
from products
where change_date <= '2019-08-16'
group by product_id
)
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id
这里我没想到用左连接来这样子连接,只是查出了那些在 16 号之前有修改的信息,害!
1204. 最后一个能进入巴士的人
表: Queue
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| person_id | int |
| person_name | varchar |
| weight | int |
| turn | int |
+-------------+---------+
person_id 是这个表具有唯一值的列。
该表展示了所有候车乘客的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字,其中 n 是表中的行数。
turn 决定了候车乘客上巴士的顺序,其中 turn=1 表示第一个上巴士,turn=n 表示最后一个上巴士。
weight 表示候车乘客的体重,以千克为单位。
有一队乘客在等着上巴士。然而,巴士有1000
千克 的重量限制,所以其中一部分乘客可能无法上巴士。
编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客,并报告 person_name
。题目测试用例确保顺位第一的人可以上巴士且不会超重。
返回结果格式如下所示。
示例 1:
输入:
Queue 表
+-----------+-------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------+--------+------+
| 5 | Alice | 250 | 1 |
| 4 | Bob | 175 | 5 |
| 3 | Alex | 350 | 2 |
| 6 | John Cena | 400 | 3 |
| 1 | Winston | 500 | 6 |
| 2 | Marie | 200 | 4 |
+-----------+-------------+--------+------+
输出:
+-------------+
| person_name |
+-------------+
| John Cena |
+-------------+
解释:
为了简化,Queue 表按 turn 列由小到大排序。
+------+----+-----------+--------+--------------+
| Turn | ID | Name | Weight | Total Weight |
+------+----+-----------+--------+--------------+
| 1 | 5 | Alice | 250 | 250 |
| 2 | 3 | Alex | 350 | 600 |
| 3 | 6 | John Cena | 400 | 1000 | (最后一个上巴士)
| 4 | 2 | Marie | 200 | 1200 | (无法上巴士)
| 5 | 4 | Bob | 175 | ___ |
| 6 | 1 | Winston | 500 | ___ |
+------+----+-----------+--------+--------------+
题解:
# SELECT a.person_name
# FROM Queue a, Queue b
# WHERE a.turn >= b.turn
# GROUP BY a.person_id HAVING SUM(b.weight) <= 1000
# ORDER BY a.turn DESC
# LIMIT 1
SELECT a.person_name
FROM (
SELECT person_name, @pre := @pre + weight AS weight
FROM Queue, (SELECT @pre := 0) tmp
ORDER BY turn
) a
WHERE a.weight <= 1000
ORDER BY a.weight DESC
LIMIT 1
解释:
这个 SQL 查询旨在选择排队的人员,他们按照顺序依次加权,并且找出权重总和不超过 1000 的最后一个人。让我逐步解释这个查询:
-
SELECT person_name
: 查询语句要返回的结果是person_name
,即人员的名字。 -
这是一个嵌套查询。内部的子查询是一个使用变量
@pre
的查询,其中@pre := @pre + weight
这个表达式在每一行中将权重值进行累加,并将结果作为weight
列。Queue
表是主查询中的一个表,可能代表着排队队列,它的结构应该包括person_name
(人员名字)、weight
(权重值)和turn
(顺序)。另外,(SELECT @pre := 0) tmp
用来初始化@pre
变量,确保在开始查询时@pre
的初始值为 0。 -
在 MySQL 中,
SELECT @pre := 0
这样的语句通常用于在查询开始时初始化一个变量。这个语句在 SQL 中是作为一个子查询(Subquery)来执行的,而且在这种情况下,它将只执行一次。当你在一个查询中执行
(SELECT @pre := 0) tmp
时,它不会返回任何行,因为它的作用是初始化@pre
变量的值为 0,并且不会生成实际的结果集。这样做是为了确保在你主要的查询语句执行之前,@pre
变量已经被赋了初始值 0。这个子查询中的别名
tmp
本质上是一个占位符。它没有实际的作用,只是为了使查询语法合法。当你需要在查询开始时初始化变量时,使用这种方法是相对常见的做法。 -
ORDER BY turn
: 这里的ORDER BY
子句按照turn
列的值对数据进行排序。turn
可能是一个列,决定了人员排队的顺序。 -
外部的主查询
a
使用内部子查询的结果,它选择了所有权重不超过 1000 的人员。这是通过WHERE a.weight <= 1000
条件实现的,它筛选出总权重不超过 1000 的行。 -
ORDER BY a.weight DESC
: 外部主查询中的ORDER BY
子句再次对结果进行排序,这次是按照weight
列的降序排列。 -
LIMIT 1
: 最终结果被限制为仅返回第一个结果行,因为按照降序排列,第一个结果就是权重总和不超过 1000 的最后一个人员。
这个查询的目的是找出排队人员中总权重不超过 1000 的最后一个人,并返回其姓名。
关于变量定义
在 MySQL 中,你可以使用用户自定义变量来存储和处理数据,这些变量的作用域仅限于当前会话,并在会话结束时被清除。以下是 MySQL 中自定义变量的几种常见方法和用法:
1. 使用 SET 命令声明变量:
SET @variable_name = value;
这里 @variable_name
是你自定义的变量名,value
是你想要给变量赋的值。例如:
SET @my_variable = 10;
2. 在 SELECT 语句中设置变量:
SELECT column_name, @variable_name := value AS new_variable_name
FROM your_table;
这个方法允许你在查询中直接给变量赋值。column_name
是你选择的列名,value
是你想要赋给变量的值,AS new_variable_name
则是为变量设置别名(非必须)。例如:
SELECT column_name, @my_variable := column_name * 2 AS doubled_value
FROM your_table;
3. 使用 SELECT INTO 语句设置变量:
SELECT column_name INTO @variable_name
FROM your_table
WHERE condition;
这个语法用于从查询结果中将值赋给变量。例如:
SELECT column_name INTO @my_variable
FROM your_table
WHERE id = 1;
4. 使用用户自定义函数(User-defined Functions):
你还可以通过编写自定义函数来操作变量,并在函数中使用这些变量。
DELIMITER //
CREATE FUNCTION my_function()
RETURNS INT
BEGIN
DECLARE my_var INT;
SET my_var = 10;
RETURN my_var;
END //
DELIMITER ;
注意事项:
- 在 MySQL 中,变量名以
@
符号作为前缀。 - 这些用户自定义变量的作用范围是当前会话。它们在会话结束时会被清除。
- 变量赋值和使用可以在同一查询中进行,但是 MySQL 中变量的使用必须在赋值之后。
- 变量可以存储数字、字符串和日期等不同类型的值。
- 在复杂查询中,特别是涉及到事务或存储过程的场景中,变量的使用可以非常有用。
这些是 MySQL 中使用自定义变量的基本方法和一些常见用法。通过灵活地使用这些变量,你可以进行更多复杂的数据处理和操作。
1907. 按分类统计薪水
表: Accounts
+-------------+------+
| 列名 | 类型 |
+-------------+------+
| account_id | int |
| income | int |
+-------------+------+
在 SQL 中,account_id 是这个表的主键。
每一行都包含一个银行帐户的月收入的信息。
查询每个工资类别的银行账户数量。 工资类别如下:
"Low Salary"
:所有工资 严格低于20000
美元。"Average Salary"
: 包含 范围内的所有工资[$20000, $50000]
。"High Salary"
:所有工资 严格大于50000
美元。
结果表 必须 包含所有三个类别。 如果某个类别中没有帐户,则报告 0
。
按 任意顺序 返回结果表。
查询结果格式如下示例。
示例 1:
输入:
Accounts 表:
+------------+--------+
| account_id | income |
+------------+--------+
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
+------------+--------+
输出:
+----------------+----------------+
| category | accounts_count |
+----------------+----------------+
| Low Salary | 1 |
| Average Salary | 0 |
| High Salary | 3 |
+----------------+----------------+
解释:
低薪: 有一个账户 2.
中等薪水: 没有.
高薪: 有三个账户,他们是 3, 6和 8.
题解:
# Write your MySQL query statement below
SELECT
'Low Salary' AS category,
SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS accounts_count
FROM
Accounts
UNION
SELECT
'Average Salary' category,
SUM(CASE WHEN income >= 20000 AND income <= 50000 THEN 1 ELSE 0 END)
AS accounts_count
FROM
Accounts
UNION
SELECT
'High Salary' category,
SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS accounts_count
FROM
Accounts
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!