20240106 SQL基础50题打卡

2024-01-07 23:07:13

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 的最后一个人。让我逐步解释这个查询:

  1. SELECT person_name: 查询语句要返回的结果是 person_name,即人员的名字。

  2. 这是一个嵌套查询。内部的子查询是一个使用变量 @pre 的查询,其中 @pre := @pre + weight 这个表达式在每一行中将权重值进行累加,并将结果作为 weight 列。Queue 表是主查询中的一个表,可能代表着排队队列,它的结构应该包括 person_name(人员名字)、weight(权重值)和 turn(顺序)。另外,(SELECT @pre := 0) tmp 用来初始化 @pre 变量,确保在开始查询时 @pre 的初始值为 0。

  3. 在 MySQL 中,SELECT @pre := 0 这样的语句通常用于在查询开始时初始化一个变量。这个语句在 SQL 中是作为一个子查询(Subquery)来执行的,而且在这种情况下,它将只执行一次。

    当你在一个查询中执行 (SELECT @pre := 0) tmp 时,它不会返回任何行,因为它的作用是初始化 @pre 变量的值为 0,并且不会生成实际的结果集。这样做是为了确保在你主要的查询语句执行之前,@pre 变量已经被赋了初始值 0。

    这个子查询中的别名 tmp 本质上是一个占位符。它没有实际的作用,只是为了使查询语法合法。当你需要在查询开始时初始化变量时,使用这种方法是相对常见的做法。

  4. ORDER BY turn: 这里的 ORDER BY 子句按照 turn 列的值对数据进行排序。turn 可能是一个列,决定了人员排队的顺序。

  5. 外部的主查询 a 使用内部子查询的结果,它选择了所有权重不超过 1000 的人员。这是通过 WHERE a.weight <= 1000 条件实现的,它筛选出总权重不超过 1000 的行。

  6. ORDER BY a.weight DESC: 外部主查询中的 ORDER BY 子句再次对结果进行排序,这次是按照 weight 列的降序排列。

  7. 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

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