描述
某金融公司某项目下有如下 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;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*