20231228 SQL基础50题打卡

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

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

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

1. 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');
   

2. DATEDIFF()：计算两个日期之间的天数差。

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

   SELECT DATEDIFF('2023-12-31', '2023-01-01');
   

3. TIMEDIFF()：计算两个时间之间的差异。

   • 计算两个时间之间的时间差：

   SELECT TIMEDIFF('10:30:00', '08:15:00');
   

这些示例演示了如何使用不同的时间单位来计算日期和时间之间的差异，包括年、月、日、小时等单位。