leetcode 1393 股票的资本损益

select p1.stock_name, (p2.price - p1.price) as capital_gain_loss from (
    select s1.stock_name, s1.operation, sum(s1.price) as price from Stocks s1
    group by s1.stock_name, s1.operation
) p1
left join (
    select s2.stock_name, s2.operation, sum(s2.price) as price from Stocks s2
    group by s2.stock_name, s2.operation
) p2
on p1.stock_name = p2.stock_name
and p1.operation = 'Buy'
and p2.operation = 'Sell'
where p2.stock_name is not null

select
stock_name,
sum(
    case
        when operation = 'Buy' then  0 - price
        when operation = 'Sell' then price
        end
) as capital_gain_loss
from Stocks
group by stock_name