1393-capital-gainloss
https://leetcode.com/problems/capital-gainloss/description/
IDEA报红但是能执行！
-- 用全部卖出的减去全部买入的
with b as (
select stock_name, sum(price) AS total_buy_price
from Stocks where operation = 'Buy'group by stock_name
),
s as (
select stock_name, sum(price) AS total_sell_price
from Stocks where operation = 'Sell' group by stock_name
)
SELECT s.stock_name, s.total_sell_price - b.total_buy_price as capital_gain_loss
FROM b
JOIN s ON b.stock_name = s.stock_name;
1407-top-travellers
https://leetcode.com/problems/top-travellers/description/
就是左连接，我搞错了一开始
left join要的是left全部，right没有的话就用null表示
select name, IFNULL(sum(distance), 0) as travelled_distance
from Users
left join rides r on Users.id = r.user_id
group by Users.id
order by travelled_distance desc, name asc
1484-group-sold-products-by-the-date
https://leetcode.com/problems/group-sold-products-by-the-date/description/
group_concat
select sell_date,count(distinct (product)) as num_sold,group_concat(distinct product order by product separator ',') as products
from Activities
group by sell_date
order by sell_date
GROUP_CONCAT语法
GROUP_CONCAT(DISTINCT expression1ORDER BY expression2SEPARATOR sep
);作者：力扣官方题解
链接：https://leetcode.cn/problems/group-sold-products-by-the-date/solutions/2366313/an-ri-qi-fen-zu-xiao-shou-chan-pin-by-le-wsi4/
1517-find-users-with-valid-e-mails
https://leetcode.com/problems/find-users-with-valid-e-mails/description/
正则表达式
-- 力扣官网给的还不对，com必须小写！不管了
select user_id, name, mail
from Users
where mail regexp '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$'
1527-patients-with-a-condition
https://leetcode.com/problems/patients-with-a-condition/description/
还是正则，空格分割的
select patient_id, patient_name, conditions
from Patients
where conditions regexp '(^| )DIAB1'
1581-customer-who-visited-but-did-not-make-any-transactions
https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/
select
# *customer_id, count(*) as count_no_trans
from visits
left join Transactions T on Visits.visit_id = T.visit_id
where transaction_id is null
group by customer_id
1587-bank-account-summary-ii
https://leetcode.com/problems/bank-account-summary-ii/description/
group by
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
select name, sum(amount) as balance
from Users
join Transactions T on Users.account = T.account
group by name
having sum(amount) > 10000
)
)
】)
