MySQL基础练习题8-每月交易1

题目： 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

准备数据

分析数据

第一步：用substr()函数来截取到月份，用group by为每个国家分组来查找每个国家

第二步：用count()和sum()来求事务数和总金额

​第三步：加入已批准的事务数，及其总金额。

 总结

---

题目： 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

准备数据

## 创建库
create database db;
use db;

## 创建交易库（transactions）
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');

 输入：

交易表

分析数据

第一步：用substr()函数来截取到月份，用group by为每个国家分组来查找每个国家

## 第一步：使用substr()函数来截取到月份，用group by为每个国家分组来查找每个国家
select substr(trans_date,1,7) as month,
       country
from transactions
group by month,country;

 第二步：用count()和sum()来求事务数和总金额

## 第二步：用count()和sum()来求事务数和总金额
select substr(trans_date,1,7) as month,
       country,
       count(id) as trans_count,
       sum(amount) as trans_total_amout
from transactions
group by month,country;

 第三步：加入已批准的事务数，及其总金额。

## 第三步：加入已批准的事务数，及其总金额。
select substr(trans_date,1,7) as month,
       country,
       count(id) as trans_count,
       sum(state='approved') as approved_count,
       sum(amount) as trans_total_amout,
       sum(if(state='approved',amount,0)) as approved_total_amount  ## 排除不是批准的事务，把它设置为0
from transactions
group by month,country;

 总结

1. MySQL中截取子字符串使用的是substr()函数，substr(str,start,len)函数:从指定位置截取指定个数的字符串。
2. 可以用if()函数，将不符合条件的设置为其他。if(expr,v1,v2)函数 : 判断数据给出返回值。