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 |      注:这个题目给的是charge_date,但它定义表检查答案的时候定义的是trans_date,所以代码中chargebacks表中这一列也定义成了trans_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
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。

 

个人思路:

select distinct  a.month, a.country,ifnull(b.approved_count,0) as approved_count,    # 加了distinct,是因为最终结果中可能会有重复值
ifnull(b.approved_amount,0) as approved_amount, ifnull(c.chargeback_count,0) as chargeback_count,
 ifnull(c.chargeback_amount,0) as chargeback_amount

from
(select date_format(T.trans_date,'%Y-%m')as month, T.country as country from  Transactions as T   # 如这张表中有两个相同的月份和国家,结果中就会产生重复值,在这里加distinct没有太大用途,因为和下面一张表Union时,还会产生重复值,所以是在最终结果中加入distinct关键字
group by month(T.trans_date),T.country
union all
select date_format(C.trans_date,'%Y-%m')as month , T.country as country 
from Chargebacks as C join Transactions as T on C.trans_id=T.id
group by month(C.trans_date),T.country) a


left join

(select date_format(T.trans_date,'%Y-%m') as month ,T.country, count(T.id) as approved_count, sum(T.amount)  as approved_amount
from  Transactions as T
where T.state='approved'
group by month(T.trans_date) ,T.country) b on  a.month=b.month and a.country=b.country

left join 

(select date_format(C.trans_date,'%Y-%m') as month,T.country, count(T.id) as chargeback_count ,sum(T.amount) as chargeback_amount
from Transactions as T join Chargebacks as C on T.id=C.trans_id
group by month(C.trans_date),T.country) c on a.month=c.month  and a.country=c.country

where isnull(b.approved_count)+isnull(c.chargeback_count)<2;  # 判断非零行,当count为0时,对应的amount一定也是0,所以当approved_count和chargeback_count都为0时,使用isnull()函数判断后,这两者相加结果为2,小于2说明,其中一个count有值

 

代码分析:

a表:这张表中的月份和country是最全的,是在这张表的基础上进行left join的

PS:group by 字段后面有2个字段,select语句查询2个字段,不要忘记查询country;  因为b表和c表中的country可能完全都不相同,此时使用left join不能得到正确的结果,所以select语句查询时包含country字段,因此,group by字段后面也要添加country分组

select date_format(T.trans_date,'%Y-%m')as month, T.country as country from  Transactions as T  
group by month(T.trans_date),T.country
union all
select date_format(C.trans_date,'%Y-%m')as month , T.country as country 
from Chargebacks as C join Transactions as T on C.trans_id=T.id
group by month(C.trans_date),T.country

运行结果:

leetcode 数据库练习 -  1205 每月交易II

 

 

 

b表:

select date_format(T.trans_date,'%Y-%m') as month ,T.country, count(T.id) as approved_count, sum(T.amount)  as approved_amount
from  Transactions as T
where T.state='approved'
group by month(T.trans_date) ,T.country

运行结果:

leetcode 数据库练习 -  1205 每月交易II

 

 

 

c表:

select date_format(C.trans_date,'%Y-%m') as month,T.country, count(T.id) as chargeback_count ,sum(T.amount) as chargeback_amount
from Transactions as T join Chargebacks as C on T.id=C.trans_id
group by month(C.trans_date),T.country

运行结果:

leetcode 数据库练习 -  1205 每月交易II

 

 

 

 

 

 

 

 

 

 

测试用表:

第一张:

"Transactions":

[[100,"CB","declined",4000,"2019-02-04"]

,[101,"BB","approved",7000,"2019-02-17"]

,[102,"CA","declined",6000,"2019-02-26"]

,[103,"AA","declined",7000,"2019-04-01"]]

 

"Chargebacks":

[[100,"2019-03-29"],

[102,"2019-02-28"]

,[103,"2019-05-09"]]}}

 

 

第二张:这一张是说明最后结果会有重复值,所以在最终结果处加distinct关键字

"Transactions"

:[[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"

:[[102,"2019-05-29"],

[101,"2019-06-30"],

[105,"2019-09-18"]]}}

 

 

第三张表: b表和c表中,使用group by子句时,不要忘了country字段也要分组

"Transactions":

[[100,"BB","declined",2000,"2019-02-25"],

[101,"CA","declined",4000,"2019-02-22"],

[102,"BA","declined",2000,"2019-03-17"]

,[103,"AC","declined",6000,"2019-02-12"]

,[104,"AA","approved",3000,"2019-02-20"]

[105,"AB","declined",7000,"2019-03-22"]],

 

"Chargebacks

:[[100,"2019-04-26"]

[101,"2019-03-09"],

[103,"2019-03-07"],

[105,"2019-06-12"]]}}

 

 

 

第四张表:

"Transactions"

:[[100,"CB","approved",3000,"2019-04-11"]

,[101,"CC","declined",8000,"2019-04-05"],

[102,"BC","approved",2000,"2019-04-16"],

[103,"BC","approved",6000,"2019-02-18"],

[104,"AA","declined",7000,"2019-04-23"]

,[105,"CA","declined",1000,"2019-03-12"],

[106,"AA","declined",3000,"2019-03-24"],

[107,"CB","declined",2000,"2019-03-24"]],"

 

Chargebacks"

:[[100,"2019-07-05"]

,[101,"2019-04-15"],

[102,"2019-07-09"],

[103,"2019-03-06"]

,[104,"2019-05-08"]

,[105,"2019-05-12"]

,[106,"2019-04-10"]

,[107,"2019-07-01"]

 

上一篇:Mysql索引


下一篇:task 1:完成省城市的三级联动(包括湖南省),附代码和效果图。