题目
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 |
+----------+---------+----------------+-----------------+-------------------+--------------------+
解答
先两表连接
SELECT *
FROM Transactions AS T
LEFT JOIN Chargebacks AS C
ON T.`id` = C.`trans_id`;
查找每个月和每个国家/地区的已批准交易的数量及其总金额 需要对左表的月份和州进行分组
退单的数量及其总金额 则需要对右表的月份和州进行分组
连接后查询好像做不到
查看别人的方法
发现是分别查询然后用union合并
先查询已批准的订单
SELECT DATE_FORMAT(T.`trans_date`, '%Y-%m') AS MONTH, T.`country`, T.`state`, T.`amount`
FROM Transactions AS T
WHERE T.`state` = 'approved';
再查询退单的订单 字段需与上表保持一致
SELECT DATE_FORMAT(C.`charge_date`, '%Y-%m') AS MONTH, T.`country`, T.`state`, T.`amount`
FROM Transactions AS T
JOIN Chargebacks AS C
ON T.`id` = C.`trans_id`;
两表进行合并 为了区分 引入一个tag
且要用 union all避免重复的去掉
(SELECT DATE_FORMAT(T.`trans_date`, '%Y-%m') AS MONTH, T.`country`, T.`state`, T.`amount`, (SELECT 1) AS tag
FROM Transactions AS T
WHERE T.`state` = 'approved')
UNION ALL
(SELECT DATE_FORMAT(C.`charge_date`, '%Y-%m') AS MONTH, T.`country`, T.`state`, T.`amount`,(SELECT 0) AS tag
FROM Transactions AS T
JOIN Chargebacks AS C
ON T.`id` = C.`trans_id`);
之后与每月交易I 一致 按月份 国家分组 得到想要的计算结果即可
SELECT tmp.month, tmp.country,
SUM(tmp.tag) AS approved_count,
SUM(IF(tmp.tag = 1, tmp.amount, 0)) AS approved_amount,
COUNT(IF(tmp.tag = 0, 1, NULL)) AS chargeback_count,
SUM(IF(tmp.tag = 0, tmp.amount, 0)) AS chargeback_amount
FROM ((SELECT DATE_FORMAT(T.`trans_date`, '%Y-%m') AS MONTH, T.`country`, T.`state`, T.`amount`, (SELECT 1) AS tag
FROM Transactions AS T
WHERE T.`state` = 'approved')
UNION ALL
(SELECT DATE_FORMAT(C.`charge_date`, '%Y-%m') AS MONTH, T.`country`, T.`state`, T.`amount`,(SELECT 0) AS tag
FROM Transactions AS T
JOIN Chargebacks AS C
ON T.`id` = C.`trans_id`)) AS tmp
GROUP BY tmp.month, tmp.country;
网友评论