20231228 SQL基础50题打卡

原创已于 2023-12-28 22:11:55 修改 · 554 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#sql #linux #数据库

于 2023-12-28 22:11:35 首次发布

SQL基础50题专栏收录该内容

15 篇文章

订阅专栏

20231228 SQL基础50题打卡

1068. 产品销售分析 I

销售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) 是销售表 Sales 的主键（具有唯一值的列的组合）。
product_id 是关联到产品表 Product 的外键（reference 列）。
该表的每一行显示 product_id 在某一年的销售情况。
注意: price 表示每单位价格。

产品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是表的主键（具有唯一值的列）。
该表的每一行表示每种产品的产品名称。

编写解决方案，以获取 Sales 表中所有 sale_id 对应的 product_name 以及该产品的所有 year 和 price 。

返回结果表 无顺序要求 。

结果格式示例如下。

示例 1：

输入：
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      |
+------------+--------------+
输出：
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

# Write your MySQL query statement below
select Product.product_name ,Sales.year , Sales.price from Sales, Product where Product.product_id = Sales.product_id;

1581. 进店却未进行过交易的顾客

表：Visits

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id 是该表中具有唯一值的列。
该表包含有关光临过购物中心的顾客的信息。

表：Transactions

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id 是该表中具有唯一值的列。
此表包含 visit_id 期间进行的交易的信息。

有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。

返回以 任何顺序 排序的结果表。

返回结果格式如下例所示。

示例 1：

输入:
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1        | 23          |
| 2        | 9           |
| 4        | 30          |
| 5        | 54          |
| 6        | 96          |
| 7        | 54          |
| 8        | 54          |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2              | 5        | 310    |
| 3              | 5        | 300    |
| 9              | 5        | 200    |
| 12             | 1        | 910    |
| 13             | 2        | 970    |
+----------------+----------+--------+
输出:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+
解释:
ID = 23 的顾客曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。
ID = 9 的顾客曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。
ID = 30 的顾客曾经去过购物中心，并且没有进行任何交易。
ID = 54 的顾客三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。
ID = 96 的顾客曾经去过购物中心，并且没有进行任何交易。
如我们所见，ID 为 30 和 96 的顾客一次没有进行任何交易就去了购物中心。顾客 54 也两次访问了购物中心并且没有进行任何交易。

# Write your MySQL query statement below
select Visits.customer_id ,count(*) as count_no_trans  from Visits where Visits.visit_id not  in (select Transactions.visit_id from Transactions) group by Visits.customer_id;

# 解法2
SELECT customer_id, count(customer_id) count_no_trans
FROM Visits v
LEFT JOIN transactions t 
ON v.visit_id = t.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;

197. 上升的温度

表： Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是该表具有唯一值的列。
该表包含特定日期的温度信息

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 无顺序要求 。

结果格式如下例子所示。

示例 1：

输入：
Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
输出：
+----+
| id |
+----+
| 2  |
| 4  |
+----+
解释：
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）

# Write your MySQL query statement below
# 解法1
select d1.id from Weather as d1, Weather as d2 where timestampdiff(day,d1.recordDate,d2.recordDate) = -1 and 
d1.temperature > d2.temperature ;
# 解法2
select today.id as Id
from Weather yesterday , Weather today
where yesterday.recordDate = date_sub(today.recordDate,INTERVAL  1 day)
and today.Temperature > yesterday.Temperature

这一题主要是时间函数的用法：

明白了，下面是每个函数的详细示例，涵盖了年、月、日、小时等不同时间单位的例子：

TIMESTAMPDIFF()：计算两个日期或时间的差异，并以指定的时间单位返回结果。

计算两个日期之间的年份差：

SELECT TIMESTAMPDIFF(YEAR, '2000-01-01', '2023-12-31');

计算两个日期之间的月份差：

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2023-12-31');

计算两个日期之间的天数差：

SELECT TIMESTAMPDIFF(DAY, '2023-01-01', '2023-12-31');

计算两个日期之间的小时差：

SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 12:00:00', '2023-01-02 10:30:00');

DATEDIFF()：计算两个日期之间的天数差。
- 计算两个日期之间的天数差：
```
SELECT DATEDIFF('2023-12-31', '2023-01-01');
```
TIMEDIFF()：计算两个时间之间的差异。
- 计算两个时间之间的时间差：
```
SELECT TIMEDIFF('10:30:00', '08:15:00');
```