??? 大家好，我是【IT江湖人称邦德jeames007，10年DBA工作经验 【大数据领域博主】上进心十足 中国DBA联盟(ACDU)成员，目前从事DBA及程序编程 擅长主流数据Oracle、MySQL、PG 运维开发、备份恢复、安装迁移、性能优化、故障应急处理等。 ? 如果你对数据库感兴趣，欢迎关注【IT邦德 ???感谢大家的大可爱小可爱
文章目录
- 前言
-
- 1393.股票的资本损益
- 1407.排名靠前的旅行者
- 1158.市场分析 I
前言
SQL每个人都必须使用它，但它不是用来衡量产出的SQL你需要使用这个工具来创造其他价值。
1393.股票的资本损益
?? Stocks 表： --------------- --------- | Column Name | Type | --------------- --------- | stock_name | varchar | | operation | enum | | operation_day | int | | price | int | --------------- --------- (stock_name, day) 是这张表的主键 operation 列是一种枚举类型，包括：('Sell','Buy') 本表的每一行代表名称 stock_name 某支股票在 operation_day 这一天的运营价格。 每次保证股票'Sell'操作前，有相应的'Buy'操作。 ?? 需求 编写一个SQL查询报告每只股票的资本损益。 股票的资本损益是一次或多次买卖股票后的全部收益或损失。 以任意顺序返回结果。 SQL如下例所示： Stocks 表: --------------- ----------- --------------- -------- | stock_name | operation | operation_day | price | --------------- ----------- --------------- -------- | Leetcode | Buy | 1 | 1000 | | Corona Masks | Buy | 2 | 10 | | Leetcode | Sell | 5 | 9000 | | Handbags | Buy | 17 | 30000 | | Corona Masks | Sell | 3 | 1010 |
| Corona Masks | Buy | 4 | 1000 |
| Corona Masks | Sell | 5 | 500 |
| Corona Masks | Buy | 6 | 1000 |
| Handbags | Sell | 29 | 7000 |
| Corona Masks | Sell | 10 | 10000 |
+---------------+-----------+---------------+--------+
Result 表:
+---------------+-------------------+
| stock_name | capital_gain_loss |
+---------------+-------------------+
| Corona Masks | 9500 |
| Leetcode | 8000 |
| Handbags | -23000 |
+---------------+-------------------+
Leetcode 股票在第一天以1000美元的价格买入，在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。
Handbags 股票在第17天以30000美元的价格买入，在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。
Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买，在第5天以500美元的价格出售。最后，它在第6天以1000美元的价格被买走，在第10天以10000美元的价格被卖掉。资本损益是每次（’Buy'->'Sell'）操作资本收益或损失的和=（1010-10）+（500-1000）+（10000-1000）=1000-500+9000=9500美元。 🐴🐴 答案 # Write your MySQL query statement below select stock_name, sum(case when operation='Sell' then price else 0 end) -sum(case when operation='Buy' then price else 0 end) capital_gain_loss from Stocks group by stock_name /* Write your T-SQL query statement below */ select stock_name, sum(case when operation='buy' then -price else price end ) as 'capital_gain_loss' from Stocks group by stock_name /* Write your PL/SQL query statement below */ select stock_name "stock_name", sum( case when operation = 'Sell' then price when operation = 'Buy' then -price
end
) as "capital_gain_loss"
from Stocks
group by stock_name
1407.排名靠前的旅行者
🚀表：Users
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表单主键。
name 是用户名字。
表：Rides
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+
id 是该表单主键。
user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。
🚀 需求
写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
查询结果格式如下例所示。
Users 表：
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Alex |
| 4 | Donald |
| 7 | Lee |
| 13 | Jonathan |
| 19 | Elvis |
+------+-----------+
Rides 表：
+------+----------+----------+
| id | user_id | distance |
+------+----------+----------+
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 13 | 312 |
| 6 | 19 | 50 |
| 7 | 7 | 120 |
| 8 | 19 | 400 |
| 9 | 7 | 230 |
+------+----------+----------+
Result 表：
+----------+--------------------+
| name | travelled_distance |
+----------+--------------------+
| Elvis | 450 |
| Lee | 450 |
| Bob | 317 |
| Jonathan | 312 |
| Alex | 222 |
| Alice | 120 |
| Donald | 0 |
+----------+--------------------+
Elvis 和 Lee 旅行了 450 英里，Elvis 是排名靠前的旅行者，因为他的名字在字母表上的排序比 Lee 更小。
Bob, Jonathan, Alex 和 Alice 只有一次行程，我们只按此次行程的全部距离对他们排序。
Donald 没有任何行程, 他的旅行距离为 0。
🐴🐴 答案
# Write your MySQL query statement below
select max(name) name,ifnull(sum(distance),0) travelled_distance
from Users u left join Rides r on r.user_id=u.id
group by r.user_id
order by travelled_distance desc,name
/* Write your T-SQL query statement below */
select max(name) name,isnull(sum(distance),0) travelled_distance
from Users u left join Rides r on r.user_id=u.id
group by r.user_id
order by travelled_distance desc,name
/* Write your PL/SQL query statement below */
select max(name) "name",
sum(nvl(distance,0)) "travelled_distance"
from Users u left join Rides r on r.user_id=u.id
group by r.user_id
order by 2 desc,1
1158.市场分析 I
🚀 Table: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| join_date | date |
| favorite_brand | varchar |
+----------------+---------+
此表主键是 user_id。
表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| item_id | int |
| buyer_id | int |
| seller_id | int |
+---------------+---------+
此表主键是 order_id。
外键是 item_id 和（buyer_id，seller_id）。
Table: Items
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| item_id | int |
| item_brand | varchar |
+---------------+---------+
此表主键是 item_id。
🚀 需求
请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。
以 任意顺序 返回结果表。
查询结果格式如下。
示例 1:
输入：
Users 表:
+---------+------------+----------------+
| user_id | join_date | favorite_brand |
+---------+------------+----------------+
| 1 | 2018-01-01 | Lenovo |
| 2 | 2018-02-09 | Samsung |
| 3 | 2018-01-19 | LG |
| 4 | 2018-05-21 | HP |
+---------+------------+----------------+
Orders 表:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2018-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-03 | 3 | 2 | 3 |
| 4 | 2018-08-04 | 1 | 4 | 2 |
| 5 | 2018-08-04 | 1 | 3 | 4 |
| 6 | 2019-08-05 | 2 | 2 | 4 |
+----------+------------+---------+----------+-----------+
Items 表:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
+---------+------------+
输出：
+-----------+------------+----------------+
| buyer_id | join_date | orders_in_2019 |
+-----------+------------+----------------+
| 1 | 2018-01-01 | 1 |
| 2 | 2018-02-09 | 2 |
| 3 | 2018-01-19 | 0 |
| 4 | 2018-05-21 | 0 |
+-----------+------------+----------------+
🐴🐴 答案
# Write your MySQL query statement below
SELECT user_id AS buyer_id, join_date, IFNULL(Buy.cnt, 0) AS orders_in_2019
FROM Users
LEFT JOIN (
SELECT buyer_id, COUNT(order_id) AS cnt
FROM Orders
WHERE Year(order_date)='2019'
GROUP BY buyer_id
) AS Buy
ON user_id=Buy.buyer_id
/* Write your T-SQL query statement below */
SELECT user_id AS buyer_id, join_date, isnull(Buy.cnt,0) AS orders_in_2019
FROM Users
LEFT JOIN (
SELECT buyer_id, COUNT(order_id) AS cnt
FROM Orders
WHERE Year(order_date)='2019'
GROUP BY buyer_id
) Buy
ON user_id=Buy.buyer_id
/* Write your PL/SQL query statement below */
select
buyer_id "buyer_id",
to_char(max(join_date),'yyyy-mm-dd') "join_date",
count(case when to_number(to_char(order_date,'yyyy')) = 2019 then order_id
else null end) "orders_in_2019"
from Users a,Orders b
where a.user_id = b. buyer_id
group by buyer_id
order by 1
体系化学习SQL，请关注CSDN博客 https://blog.csdn.net/weixin_41645135/category_11653817.html