力扣高频SQL 50题（基础版）第二十七题之1193.每月交易

力扣高频SQL 50题（基础版）第二十七题

1193.每月交易

题目说明

表：Transactions

±--------------±--------+

| Column Name | Type |

±--------------±--------+

| id | int |

| country | varchar |

| state | enum |

| amount | int |

| trans_date | date |

±--------------±--------+

id 是这个表的主键。

该表包含有关传入事务的信息。

state 列类型为 [“approved”, “declined”] 之一。

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

实现过程

准备数据

Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
Truncate table Transactions
insert into Transactions (id, country, state, amount, trans_date) values ('121', 'US', 'approved', '1000', '2018-12-18')
insert into Transactions (id, country, state, amount, trans_date) values ('122', 'US', 'declined', '2000', '2018-12-19')
insert into Transactions (id, country, state, amount, trans_date) values ('123', 'US', 'approved', '2000', '2019-01-01')
insert into Transactions (id, country, state, amount, trans_date) values ('124', 'DE', 'approved', '2000', '2019-01-07')

实现方式

select
    date_format(trans_date,'%Y-%m') month,
    country,
    count(trans_date) trans_count,
    sum(if(state='approved',1,0)) approved_count,
    sum(amount) trans_total_amount,
    sum(if(state='approved',amount,0)) approved_total_amount
from Transactions group by month,country;

结果截图

总结

取日期中的年月字段方法：

方法一：DATE_FORMAT() 函数

select trans_date,date_format(trans_date,'%Y-%m') month from Transactions;

方法二：

left(str, length)函数 #从左开始截取

select trans_date,left(trans_date, 7) month from Transactions;

方法三：substring(str, pos**,[length]) 函数**

str:要截取字符串的原始字符串 pos:从第几位开始截取 length:截取长度（如未设置，则为从截取位到末尾）

select trans_date,substring(trans_date, 1,7) month from Transactions;

方法四：substring_index(str, delim, count)

str:要截取字符串的原始字符串 delim:分隔符，确定字符串的位置 count:指定要返回字符串的数量。如果为正数，则从字符串的起始位置开始计数；如果为负数，从字符串的末尾开始计数。

select trans_date,substring_index(trans_date,'-',2) month from Transactions;