mysql实战练习题（输出分组后前N项）

首先建表和插入数据

create table deal_tb
(id int(2),
daystr datetime,
amount decimal(10,2)
)engine=INNODB;
insert into deal_tb
values
(1, "2019-02-08", 6214.23),
(1, "2019-02-08", 6247.32),
(1, "2019-02-09", 85.63),
(2, "2019-02-14", 943.18),
(2, "2019-02-15", 369.76),
(2, "2019-02-18", 795.15),
(2, "2019-02-19", 715.65),
(2, "2019-02-21", 537.71),
(3, "2019-02-09", 967.36),
(3, "2019-02-10", 85.69),
(3, "2019-02-12", 769.85),
(3, "2019-02-13", 943.86);

1、统计每个用户交易金额最多的三天
第一步，先针对每一个id把每一天的amount相加

create table deal_tb1
as
select id ,daystr, sum(amount) as sa
from deal_tb
group by id, daystr
order by id asc,sa desc;

select from deal_tb1;

第二步，输出分组后每组的前三项,有两种方法

select * from deal_tb1 a where
(select count(*) from deal_tb1 b where a.id=b.id and a.sa<b.sa)<3;

或

select a.* from deal_tb1 a left join deal_tb1 b
on a.id=b.id and a.sa<b.sa
group by a.id,a.daystr,a.sa 
having count(a.id)<3
order by id asc,sa desc;

2、统计每个用户每天累计交易的总额（截至今天所有交易额）

select id, sum(sa) as s
from deal_tb1
where date(daystr)<date(now())
group by id;

本人纯新手，如有错误，希望大家指正
参考博客（分组后输出前N项）：https://blog.csdn.net/naxieren1992/article/details/100797828?utm_source=app