文章目錄

• • 1. 題目
  • 2. 解題

1. 題目

Transactions 記錄表

+----------------+---------+ | Column Name | Type | +----------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +----------------+---------+ id 是這個表的主鍵。 該表包含有關傳入事務的信息。 狀態列是類型為 [approved（已批準）、declined（已拒絕）] 的枚舉。

Chargebacks 表

+----------------+---------+ | Column Name | Type | +----------------+---------+ | trans_id | int | | charge_date | date | +----------------+---------+ 退單包含有關放置在事務表中的某些事務的傳入退單的基本信息。 trans_id 是 transactions 表的 id 列的外鍵。 每項退單都對應于之前進行的交易，即使未經批準。

編寫一個 SQL 查詢，以查找每個月和每個國家/地區的已批準交易的數量及其總金額、退單的數量及其總金額。

注意：在您的查詢中，給定月份和國家，忽略所有為零的行。

查詢結果格式如下所示：

Transactions 表： +------+---------+----------+--------+------------+ | id | country | state | amount | trans_date | +------+---------+----------+--------+------------+ | 101 | US | approved | 1000 | 2019-05-18 | | 102 | US | declined | 2000 | 2019-05-19 | | 103 | US | approved | 3000 | 2019-06-10 | | 104 | US | declined | 4000 | 2019-06-13 | | 105 | US | approved | 5000 | 2019-06-15 | +------+---------+----------+--------+------------+Chargebacks 表： +------------+------------+ | trans_id | trans_date | +------------+------------+ | 102 | 2019-05-29 | | 101 | 2019-06-30 | | 105 | 2019-09-18 | +------------+------------+Result 表： +----------+---------+----------------+-----------------+-------------------+--------------------+ | month | country | approved_count | approved_amount | chargeback_count | chargeback_amount | +----------+---------+----------------+-----------------+-------------------+--------------------+ | 2019-05 | US | 1 | 1000 | 1 | 2000 | | 2019-06 | US | 2 | 8000 | 1 | 1000 | | 2019-09 | US | 0 | 0 | 1 | 5000 | +----------+---------+----------------+-----------------+-------------------+--------------------+

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/monthly-transactions-ii
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。

2. 解題

# Write your MySQL query statement below select * from (select t.month, t.country, ifnull(sum(t1.approved_count),0) approved_count, ifnull(sum(t1.approved_amount),0) approved_amount,ifnull(sum(t2.chargeback_count),0) chargeback_count, ifnull(sum(t2.chargeback_amount),0) chargeback_amountfrom (select distinct country, date_format(trans_date, '%Y-%m') monthfrom Transactionsunionselect distinct country, date_format(ch.trans_date, '%Y-%m') monthfrom Chargebacks ch left join Transactions tron ch.trans_id = tr.id) tleft join(select date_format(trans_date, '%Y-%m') month, country, count(*) approved_count,ifnull(sum(amount),0) approved_amountfrom Transactionswhere state='approved'group by month, country) t1on t.month = t1.month and t.country = t1.countryleft join (select date_format(ch.trans_date, '%Y-%m') month,country,count(*) chargeback_count,ifnull(sum(amount),0) chargeback_amountfrom Chargebacks ch left join Transactions tron ch.trans_id = tr.idgroup by month, country) t2on t.month = t2.month and t.country = t2.countrygroup by month, country ) tmp where tmp.approved_count != 0 or tmp.chargeback_count != 0

or 簡單寫法，創建一個 chargeback state

select date_format(a.trans_date,'%Y-%m') month,country,sum(state = 'approved') approved_count,sum(if(state = 'approved',amount,0)) approved_amount,sum(state = 'chargeback') chargeback_count,sum(if(state = 'chargeback',amount,0)) chargeback_amount from (select * from transactionswhere state = 'approved'union allselect id, country, 'chargeback' state, amount, c.trans_datefrom chargebacks c left join transactions t on c.trans_id = t.id ) a group by month,country

---

我的CSDN博客地址 https://michael.blog.csdn.net/

長按或掃碼關注我的公眾號（Michael阿明），一起加油、一起學習進步！

總結

以上是生活随笔為你收集整理的LeetCode MySQL 1205. 每月交易II（union all）*的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。