关键词:控制流程:if ,case…when
2022年12月20日
题目
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美元。
思路
将股票按名字(stock_name
)进行分组,再根据操作(operation
)的名称给价格加上正号或者负号,然后进行累加(sum
)操作
方法一
使用if
语句
select
stock_name,
sum(
if(operation='buy',-price,price)
)as capital_gain_loss
from
Stocks
group by
stock_name
if语句的用法
if( expr1 , expr2 , expr3 ) # expr1 的值为 TRUE,则返回值为 expr2 # expr1 的值为FALSE,则返回值为 expr3
方法二
使用case.....when
语句
select
stock,
sum(
case
when operation = ' buy' then -price
else price
end
)as capital_gain_loss
from
Stocks
group by
stock_name
case的用法:
用法一:
case句法返回第一个value = 列名比较结果为真的结果。 如果没有比较结果符合,则返回else后的结果,如果没有else部分,则返回NULL:
case 列名 when condition1 then result1 when condition2 then result2 when conditionN then resultN else result end;
例:
select case score when '1' then '优秀' when '2' then '良' else ‘菜’ end from student
用法二:
第一种评估一个或多个条件,并返回第一个符合条件的结果。 如果没有条件是符合的,则返回else子句部分的结果,如果没有else部分,则返回null:
case when condition1 then result1 when condition2 then result2 when conditionN then resultN else result end;