LeetCode-1205. 每月交易II（中等）

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      | approved | 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      | 3              | 12000           | 1                 | 1000               |
| 2019-09  | US      | 0              | 0               | 1                 | 5000               |
+----------+---------+----------------+-----------------+-------------------+--------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/monthly-transactions-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

审题：编写一个 SQL 查询，以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。

每个月，每个国家，批准的数量及总额，退单的数量及总额。

思考：批准的数量查询状态，如果为批准就是。分组求和问题。退单的数量需要根据退单表查询，属于分组并且在退单表的数量。

解题：

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

这道题的的思路就是
先查出 每个月和每个国家/地区的已批准交易的数量及其总金额

select country,state,amount,date_format(t.trans_date,'%Y-%m') as month,0  as tag
from Transactions t  where state!='declined'

-- 练习
select county,state,amount,date_format(t.trans_date,'%Y-%m') as month,0 as tag
from Transactions t where state! = 'declined';

再查出 每个月和每个国家/地区的退单的数量及其总金额 

select country,state,amount,date_format(c.trans_date,'%Y-%m') as month,1 as tag
from Transactions t   
right join Chargebacks c on t.id=c.trans_id

-- 退单的数量及总额

on t.id = c.trans_id

进行合并，只靠左右关联是完成不了的， 因为它要求Chargebacks里面的trans_date也要计入进去，
注意合并的时候 不要用Union 这个会将重复行覆盖掉，判断是否重复行就是 国家 年月 金额 均一致 这是很容易发生的

最后count统计，sum求和，分组排序

这里用了tag去区分 是交易还是退单 ，方便合并之后统计

select month,
country,
count(case when state='approved' and tag=0 then 1 else null end ) as approved_count,
sum(case when state='approved' and tag=0 then amount else 0 end ) as approved_amount,
count(case when tag=1 then 1 else null end  ) as chargeback_count,
sum(case when  tag=1 then amount else 0 end ) as chargeback_amount
from(
select country,state,amount,date_format(c.trans_date,'%Y-%m') as month,1 as tag
from Transactions t   
right join Chargebacks c on t.id=c.trans_id
union all
select country,state,amount,date_format(t.trans_date,'%Y-%m') as month,0  as tag
from Transactions t  where state!='declined'
) a group by country,month order by month,country

方法二：

 

-- Write your MySQL query statement below

SELECT date_format(a.trans_date, '%Y-%m') AS month, a.country
	, COUNT(CASE 
		WHEN state = 'approved' THEN 1
		ELSE NULL
	END) AS approved_count, SUM(CASE 
		WHEN state = 'approved' THEN amount
		ELSE 0
	END) AS approved_amount
	, COUNT(CASE 
		WHEN state IS NULL THEN 1
		ELSE NULL
	END) AS chargeback_count, SUM(CASE 
		WHEN state IS NULL THEN amount
		ELSE 0
	END) AS chargeback_amount
FROM (
	SELECT *
	FROM Transactions
	UNION ALL
	SELECT a.trans_id AS id, b.country, NULL, b.amount, a.trans_date
	FROM Chargebacks a
		JOIN Transactions b ON a.trans_id = b.id
) a
WHERE a.state != 'declined' or a.state is null
GROUP BY a.country, date_format(a.trans_date, '%Y-%m')

 知识点：