要求：撰寫一個 SQL 查詢，以查找每個月和每個國家/地區的資訊：已批準交易的數量及其總金額、退單的數量及其總金額，
注意：在您的查詢中，只需顯示給定月份和國家，忽略所有為零的行，
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 |
| trans_date | date |
+----------------+---------+
退單包含有關放置在事務表中的某些事務的傳入退單的基本資訊，
trans_id 是 transactions 表的 id 列的外鍵，
每項退單都對應于之前進行的交易，即使未經批準，
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 Table：
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
+----------+------------+
Result Table：
+---------+---------+----------------+-----------------+------------------+-------------------+
| 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 |
+---------+---------+----------------+-----------------+------------------+-------------------+
SQL陳述句：
with c as(
select substr(a.trans_date,1,7) as month,b.country,count(a.trans_id) as chargeback_count,sum(b.amount) as chargeback_amount
from Chargebacks a
join Transactions b
on a.trans_id=b.id
group by b.country,substr(a.trans_date,1,7))
,
d as(select substr(trans_date,1,7) as month,country,count(id) as approved_count,sum(amount) as approved_amount
from Transactions
where state="approved"
group by country,substr(trans_date,1,7)
),f as(
select distinct month,country from(
select month,country
from c
union all
select month,country
from d)e)
select f.month,f.country,ifnull(d.approved_count,0) as approved_count,ifnull(d.approved_amount,0) as approved_amount,
ifnull(c.chargeback_count,0) as chargeback_count,ifnull(c.chargeback_amount,0) as chargeback_amount
from f
left join d
on f.month=d.month and f.country=d.country
left join c
on f.month=c.month and f.country=c.country
order by f.month asc;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/286826.html
標籤：其他
上一篇：高效的mysql分頁技巧
下一篇：mysql組內排序