案例背景:公司数据储存在N张表中,N是大数,现在需要求每个类别某类数据topN用户的数据
代码结果:创建存储过程将数据从多个相同表中查询求topN并存入,
本文主要内容
知识点
分组查询topN问题 https://editor.csdn.net/md/?articleId=126635132
创建存储过程
案例
存储过程知识点介绍
mysql存储过程文档:
https://www.runoob.com/w3cnote/mysql-stored-procedure.html
案例
建立数据集
#创建表
create table orders1 (
orderid int(11) not null primary key ,
Itemid varchar(30) not null ,
category varchar(10) not null ,
orderdate datetime not null ,
sales float not null );#创建表
create table orders2 like orders1;
create table orders3 like orders1;
create table orders4 like orders1;
create table orders5 like orders1;
#插入数据
insert into orders1 (orderid, Itemid, category, orderdate, sales)
values ('1','k1','A','2020-1-2','459.5'),
('2','k2','A','2020-2-2','345.4'),
('3','k1','A','2020-1-7','47'),
('4','k3','C','2020-1-21','678'),
('5','k4','B','2020-5-2','345'),
('6','k5','C','2020-3-12','654'),
('7','k4','B','2020-4-25','464'),
('8','k2','A','2020-5-28','632'),
('9','k3','C','2020-7-20','98'),
('10','k5','C','2020-4-28','455.6'),
('11','k1','A','2020-3-27','459.7'),
('12','k2','A','2020-2-23','776.6'),
('13','k4','B','2020-2-12','759'),
('14','k5','C','2020-8-2','999'),
('15','k2','A','2020-9-21','599'),
('16','k3','C','2020-10-21','433'),
('17','k4','B','2020-10-28','232'),
('18','k5','C','2020-10-3','124'),
('19','k3','C','2020-6-6','321.4'),
('20','k4','B','2020-9-11','788.6');
insert into orders2