回忆是一座桥
却是通往寂寞的牢

SQL案例 - 获取指定客户每月的消费额

描述

某金融公司某项目下有如下 2 张表:

交易表 trade(t_id:交易流水号,t_time:交易时间,t_cus:交易客户,t_type:交易类型【1表示消费,0表示转账】,t_amount:交易金额):

t_id t_time t_cus t_type t_amount
1 2022-01-19 03:14:08 101 1 45.0
2 2023-02-15 11:22:11 101 1 23.6
3 2023-03-19 05:33:22 102 0 350.0
4 2023-03-21 06:44:09 103 1 16.9
5 2023-02-21 08:44:09 101 1 26.9
6 2023-07-07 07:11:45 101 1 1200.0
7 2023-07-19 06:04:32 102 1 132.5
8 2023-09-19 11:23:11 101 1 130.6
9 2023-10-19 04:32:30 103 1 110.0

客户表 customer(c_id:客户号,c_name:客户名称):

c_id c_name
101 Tom
102 Ross
103 Julie
104 Niki

现需要查询 Tom 这个客户在 2023 年每月的消费金额(按月份正序显示),示例如下:

time total
2023-02 50.5
2023-07 1200.0
2023-09 130.6

请编写 SQL 语句实现上述需求。

示例1

输入:

drop table if exists  `trade` ; 
CREATE TABLE `trade` (
`t_id` int(11) NOT NULL,
`t_time` TIMESTAMP NOT NULL,
`t_cus` int(16) NOT NULL,
`t_type` int(2) NOT NULL,
`t_amount` double NOT NULL,
PRIMARY KEY (`t_id`));
INSERT INTO trade VALUES(1,'2022-01-19 03:14:08',101,1,45);
INSERT INTO trade VALUES(2,'2023-02-15 11:22:11',101,1,23.6);
INSERT INTO trade VALUES(3,'2023-03-19 05:33:22',102,0,350);
INSERT INTO trade VALUES(4,'2023-03-21 06:44:09',103,1,16.9);
INSERT INTO trade VALUES(5,'2023-02-21 08:44:09',101,1,26.9);
INSERT INTO trade VALUES(6,'2023-07-07 07:11:45',101,1,1200);
INSERT INTO trade VALUES(7,'2023-07-19 06:04:32',102,1,132.5);
INSERT INTO trade VALUES(8,'2023-09-19 11:23:11',101,1,130.6);
INSERT INTO trade VALUES(9,'2023-10-19 04:32:30',103,1,110);

drop table if exists  `customer` ;   
CREATE TABLE `customer` (
`c_id` int(11) NOT NULL,
`c_name` varchar(20) NOT NULL,
PRIMARY KEY (`c_id`));
INSERT INTO customer VALUES(101,'Tom');
INSERT INTO customer VALUES(102,'Ross');
INSERT INTO customer VALUES(103,'Juile');
INSERT INTO customer VALUES(104,'Niki');

输出:

time|total
2023-02|50.5
2023-07|1200.0
2023-09|130.6

答案

解法1:

select
    date_format(t.t_time,'%Y-%m') time,
    sum(t.t_amount) total
from
    trade t
    left join customer c on t.t_cus=c.c_id
where
    t.t_type=1
    and year(t.t_time)=2023
    and c.c_name='Tom'
group by
    date_format(t.t_time,'%Y-%m')
order by time;

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 获取指定客户每月的消费额
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论