1205 每月交易II leetcode数据库刷题（没写出来）

最新推荐文章于 2023-12-01 16:47:52 发布

每天都要被自己菜醒

最新推荐文章于 2023-12-01 16:47:52 发布

阅读量170

点赞数

分类专栏： leetcode 数据库刷题文章标签： mysql

本文链接：https://blog.csdn.net/qq_45531594/article/details/108363137

版权

数据库刷题同时被 2 个专栏收录

38 篇文章 3 订阅

订阅专栏

leetcode

21 篇文章 0 订阅

订阅专栏

1205 每月交易II
sql 架构；

create table if not exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
create table if not exists Chargebacks (trans_id int, trans_date date)
Truncate table Transactions
insert into Transactions (id, country, state, amount, trans_date) values ('101', 'US', 'approved', '1000', '2019-05-18')
insert into Transactions (id, country, state, amount, trans_date) values ('102', 'US', 'declined', '2000', '2019-05-19')
insert into Transactions (id, country, state, amount, trans_date) values ('103', 'US', 'approved', '3000', '2019-06-10')
insert into Transactions (id, country, state, amount, trans_date) values ('104', 'US', 'declined', '4000', '2019-06-13')
insert into Transactions (id, country, state, amount, trans_date) values ('105', 'US', 'approved', '5000', '2019-06-15')
Truncate table Chargebacks
insert into Chargebacks (trans_id, trans_date) values ('102', '2019-05-29')
insert into Chargebacks (trans_id, trans_date) values ('101', '2019-06-30')
insert into Chargebacks (trans_id, trans_date) values ('105', '2019-09-18')

在这里插入图片描述
mysql 没有full join 的用法
注意这里销售表没有 09月，可是退款表却有9月，连接起来的话使用union all 注意匹配字段

select  id,country,state,amount,substr(trans_date,1,7) as 'trans_date'
from  Transactions
union all 

select trans_id,country, 
#'Chargebacks'  as state,amount, 为了匹配字段且不影响上面的状态
'Chargebacks'  as state,amount, substr(c.trans_date,1,7) as 'trans_date'
from Chargebacks c 
left join Transactions t  on c.trans_id = t.id

本题最关键还就是这里的union all

在这里插入图片描述
这里注意过滤0行的情况

# Write your MySQL query statement below


SELECT z.trans_date as 'month',z.country,
#已批准交易的数量
sum(if(z.state = 'approved',1,0)) as 'approved_count',
#已批准交易的总金额
sum(if(z.state = 'approved',z.amount,0)) as 'approved_amount',
#退单的数量
sum(if(z.state = 'Chargebacks',1,0)) as 'chargeback_count',
#退单的总金额
sum(if(z.state = 'Chargebacks',z.amount,0)) as 'chargeback_amount'

FROM
(select  id,country,state,amount,substr(trans_date,1,7) as 'trans_date'
from  Transactions
union all #因为没带主键id，不all的话可能去重去掉有效数据

select trans_id as 'id',country, 
#'Chargebacks'  as state,amount, 为了匹配字段且不影响上面的状态
'Chargebacks'  as state,amount, substr(c.trans_date,1,7) as 'trans_date'
from Chargebacks c 
left join Transactions t  on c.trans_id = t.id ) z

group by z.trans_date,z.country 
#，忽略所有为零的行
having sum(if(z.state = 'approved',1,0)) or sum(if(z.state = 'Chargebacks',1,0))