Mysql 使用存储过程合并多个表数据

女爷i 2024-03-30 15:03 56阅读 0赞

## Mysql 使用存储过程合并多个表数据 ##

drop procedure if exists mergeTable;
    CREATE PROCEDURE mergeTable()
    BEGIN
    
        #定义变量
        declare temp_table_name varchar(20);
        declare total int default 0;
        declare done int default false;
        #游标数据来源  查询出你想要合并的表名称
        declare cur cursor for SELECT table_name
                               from information_schema.TABLES
                               where table_name LIKE '%user%'
                               group by table_name;
        #定义标记结束
        declare continue HANDLER for not found set done = true;
    
        drop table if exists temp_table;
        #创建临时表
        CREATE temporary TABLE `temp_table`
        (
            `id`   int(11)    NOT NULL,
            `name` int(11)    NOT NULL,
            `type` tinyint(2) NOT NULL
        );
    
    
        set total = 0;
        open cur;
        #变量赋值
        fetch cur into temp_table_name;
        while(not done)
            do
                #复制表数据到临时表
                set @sqlStr = CONCAT('INSERT INTO temp_table (`id`, `name`, `type`)
            SELECT `id`, `name`, `type`
         FROM  ', temp_table_name);
    
                PREPARE stmt from @sqlStr;
                #执行sql
                EXECUTE stmt;
    
                fetch cur into temp_table_name;
            end while;
    
        close cur;
        #统计临时表个数
        select count(1) from temp_table;
    END;
    
    #执行临时表
    call mergeTable();

编写mysql存储过程，循环的查询到表结构相同的多张表记录并将结果表合并输出。可以查询多天也可查询单天

直接实例代码与注释

CREATE PROCEDURE tableData1(in sTime VARCHAR(255),in eTime VARCHAR(255))  
BEGIN \#创建传入两个参数的存储过程，sTime开始时间，eTime结束时间  
\#定义变量  
DECLARE date VARCHAR(255);  
declare sqlVar varchar(21845);  
declare rest int;  
declare tableName varchar(1024);  
  
\#给初始值 sqlVar为空字符串,因为下边要拼接sql语句  
set rest = 100;  
set sqlVar='';

\#当开始时间等于结束时间时，只拼接一次sql语句-实现可以只查询一天数据  
if sTime = eTime THEN  
SET date = DATE\_FORMAT(sTime,"%Y-%m-%d");  
set sqlVar=CONCAT(sqlVar," (SELECT \*  
FROM data\_detail\_",sTime," WHERE XXX  
");  
  
ELSE  
  
\#rest初始值是随便给的大于0的值。所以一定会进入循环。接下来在下边使用函数DATEDIFF用结束时间减去开始时间  
\#给rest重新赋值，当大于0时说明还没结束继续拼接sql，当等于或小于零时说明应该结束了，不需要继续往下拼接sql  
while rest > 0 do  
  
\#转换日期格式，根据您自己的表结构来改，我是需要的  
SET date = DATE\_FORMAT(sTime,"%Y-%m-%d");  
  
\#使用开始时间拼接上字符串成为表名  
set tableName=CONCAT('data\_detail\_',sTime);  
  
\#查询是否存在当前表名，赋值个数给cnt  
select count(1) from information\_schema.tables where table\_name = tableName into @cnt;

\#判断表名是否存在，当表不存在时直接跳过，不判断的话会报错  
if @cnt != 0 then

\#最最最重要的一点，当结束时间减去开始时间为1时，需要走else拼接上后边的的union all 。  
\#当结束时间减去开始时间为1时，说明已经到达结束时间的前一天，并且在最后DATE\_ADD将前一天日期加1已经到达结束时间。最后不需要再拼接上一个union all  
if rest=1 then  
set sqlVar=CONCAT(sqlVar," (SELECT \*  
FROM data\_detail\_",sTime," WHERE XXX  
)");  
ELSE  
set sqlVar=CONCAT(sqlVar," (SELECT  
FROM data\_detail\_",sTime," WHERE XXX  
)",' UNION all');  
END IF;  
END if;

\#判断到达某日期，当rest为1时说明已经到达结束日期前一天  
set rest = DATEDIFF(eTime,sTime);  
\#当前日期加1  
set sTime = DATE\_FORMAT(DATE\_ADD(sTime,INTERVAL 1 day),'%Y%m%d');

\#结束循环与判断  
END while;  
END if;  
  
\#赋值用户变量  
set @v\_s=sqlVar;  
\#预定义sql语句  
prepare stmt from @v\_s;  
\#执行预定义sql语句  
EXECUTE stmt;  
\#释放资源  
DEALLOCATE PREPARE stmt;  
  
end;

call tableData1(‘20211006’,‘20211008’);  
结果长这个样子  
![5c8bd17cdb134645bd203ac108e132c4.png][]

1.创建存储过程语法:  
CREATE PROCEDURE 存储过程名(参数列表)  
BEGIN  
存储过程体（一组合法的SQL语句）  
END  
2.变量的定义与赋值:  
局部变量  
DECLARE variable\_name datatype(size) DEFAULT default\_value;  
DECLARE相当于关键字，有点类似var，表示定义一个变量；然后variable\_name是你指定的变量名称；datatype表示数据类型而括号内的size表示对应类型的格式比如varchar(50)这样子，这里需要注意的是datatype只能是MySql支持的那些基本数据类型；DEFAULT关键字指明了变量的默认值为default\_value。  
通过SET赋值: SET variable\_name = XXX  
用户变量  
用户变量不需要声明，可以直接使用，用户变量以@var\_name的形式命名变量，变量名必须以@开头  
赋值语法； set @varName=value;

只说了我用到的两种变量，其他变量查阅其他资料哦

3.While循环语句结构:  
while(表达式) do  
…  
end while;  
与其他语言while循环大同小异了其实，不难理解当表达式结果为false时退出循环  
4.if判断语句:  
IF expression THEN  
statements;  
ELSE  
else-statements;  
END IF;  
5.union与union all 的区别  
对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录

需求： A库有多张按时间分表创建的表a\_table\_$\{date\}，我想用定时器每天凌晨在B库创建一张和A库昨天的表一样的表b\_table\_$\{date\}，然后将数据抓取过来,然后再对同步过来的表b\_table\_$\{date\}做一次统计汇总插入到B库的另一张表b\_table\_statistic中。 使用存储过程做。可以根据统计时间传参来同步不同时间的数据。

思路：

1. 如果B库不存在统计日期的表b\_table\_$\{date\}则创建一个新表b\_table\_$\{date\}；

2. 开启事务，将B库表b\_table\_$\{date\}的数据清空，方便失败了再次统计；

3. 将A库a\_table\_$\{date\}表的数据同步到B库b\_table\_$\{date\}表；

注意： 在插入的时候如果数据量比较大，可以通过分组进行批量插入来避免事务问题和mysql插入数据量限制问题；

4. 先删除B库b\_table\_statistic表中统计日期的数据；然后再向B库b\_table\_statistic表中插入统计日期的统计数据；

5. 关闭事务；  
二. sql建模  
三. 脚本

\-- statistic\_date 统计日期 接收yyyy-MM-dd类型的时间字符串  
CREATE PROCEDURE create\_current\_day\_cdr\_table (IN statistic\_date VARCHAR(64)) BEGIN  
  
\-- 同步t\_cdr表记录需要插入的次数  
DECLARE CDR\_TABLE\_INSERT\_COUNT INT;  
  
\-- 声明事务  
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK ;  
  
\-- 每次插入的记录数  
SET @EACH\_INSERT\_SIZE = 1000;  
  
\-- 循环变量  
SET @i = 1;  
  
  
\-- 根据统计日期拼接话单详情表名称  
SET @tableName = CONCAT("t\_cdr\_", from\_unixtime( unix\_timestamp(statistic\_date), '%Y%m%d') );  
  
  
\-- 如果统计日期的话单详情表不存在则创建  
SET @createTableStr = CONCAT(" CREATE TABLE IF NOT EXISTS ", @tableName , "(  
\`id\` int(11) NOT NULL AUTO\_INCREMENT,  
\`call\_type\` char(2) DEFAULT NULL COMMENT '通话类型',  
\`cb\_sessionid\` varchar(64) DEFAULT NULL,  
\`busiprefix\` varchar(16) DEFAULT NULL,  
\`caller\_brand\` varchar(64) DEFAULT NULL COMMENT '主叫品牌',  
\`caller\` varchar(64) DEFAULT NULL COMMENT '主叫号码',  
\`calleraccess\` varchar(64) DEFAULT NULL COMMENT '呼入主叫',  
\`callee\_brand\` varchar(64) DEFAULT NULL COMMENT '被叫品牌',  
\`callee\` varchar(64) DEFAULT NULL COMMENT '被叫号码',  
\`calleeaccess\` varchar(64) DEFAULT NULL COMMENT '呼入被叫',  
\`calleeoriginal\` varchar(64) DEFAULT NULL COMMENT '被叫裸号',  
\`callerip\` varchar(64) DEFAULT NULL COMMENT '主叫IP',  
\`in\_gw\_media\_ip\` varchar(64) DEFAULT NULL COMMENT '主叫媒体IP',  
\`in\_gw\_media\_isp\` int(11) DEFAULT NULL,  
\`callergatewayh323id\` varchar(512) DEFAULT NULL COMMENT '主叫经由网关',  
\`callerproductid\` varchar(64) DEFAULT NULL COMMENT '主叫设备',  
\`callertogateway\` varchar(64) DEFAULT NULL COMMENT '呼出主叫',  
\`calleeip\` varchar(64) DEFAULT NULL COMMENT '被叫信令IP',  
\`calleegatewayh323id\` varchar(64) DEFAULT NULL COMMENT '被叫经由网关',  
\`calleeproductid\` varchar(128) DEFAULT NULL COMMENT '被叫设备',  
\`calleetogateway\` varchar(64) DEFAULT NULL COMMENT '呼出被叫',  
\`rtp\_ip\` varchar(64) DEFAULT NULL COMMENT '被叫媒体IP',  
\`gd\_gw\_media\_isp\` int(11) DEFAULT NULL,  
\`billingmode\` char(1) DEFAULT NULL,  
\`calllevel\` char(1) DEFAULT NULL,  
\`agentfeetime\` int(11) DEFAULT NULL,  
\`starttime\` datetime DEFAULT NULL COMMENT '起始时间',  
\`stoptime\` datetime DEFAULT NULL COMMENT '结束时间',  
\`pdd\` int(11) DEFAULT NULL COMMENT '接通延迟(秒)',  
\`linepdd\` int(11) DEFAULT NULL,  
\`waittime\` int(11) DEFAULT NULL COMMENT '接续时长(秒)',  
\`holdtime\` int(11) DEFAULT NULL COMMENT '通话时长(秒)',  
\`feeprefix\` varchar(64) DEFAULT NULL COMMENT '计费前缀',  
\`feetime\` int(11) DEFAULT NULL COMMENT '计费时长(秒)',  
\`fee\` bigint(20) unsigned DEFAULT NULL COMMENT '通话费用(元)',  
\`package\_id\` int(11) DEFAULT NULL,  
\`package\_fee\` bigint(20) unsigned DEFAULT NULL COMMENT '套餐费用(元)',  
\`package\_feetime\` int(11) DEFAULT NULL COMMENT '套餐时长(秒)',  
\`account\` varchar(32) DEFAULT NULL COMMENT '账户号码',  
\`account\_name\` varchar(255) DEFAULT NULL COMMENT '账户名称',  
\`enddirection\` char(1) DEFAULT NULL COMMENT '挂断方',  
\`endreason\` varchar(255) DEFAULT NULL COMMENT '终止原因',  
\`agentpackagefee\` bigint(20) unsigned DEFAULT NULL,  
\`agentpackagefeetime\` int(11) DEFAULT NULL,  
\`agentpackageid\` int(11) DEFAULT NULL,  
\`agentaccount\` varchar(255) DEFAULT NULL,  
\`agentname\` varchar(255) DEFAULT NULL,  
\`agentfee\` bigint(20) unsigned DEFAULT NULL COMMENT '落地费用(元)',  
\`agentfeeprefix\` varchar(64) DEFAULT '0',  
\`gatewayrouting\_type\` int(11) DEFAULT NULL,  
\`caller\_area\` varchar(32) DEFAULT NULL COMMENT '主叫地区',  
\`area\_code\` varchar(32) DEFAULT NULL COMMENT '被叫地区',  
\`province\` varchar(64) DEFAULT NULL,  
\`city\` varchar(64) DEFAULT NULL,  
\`switch\` varchar(64) DEFAULT NULL COMMENT '主叫经由VLS-switch',  
\`operators\` int(11) unsigned DEFAULT '0',  
\`outgw\_area\` varchar(64) DEFAULT NULL,  
\`ingw\_type\` varchar(32) DEFAULT NULL,  
\`routetrace\` varchar(320) DEFAULT NULL COMMENT '路由跟踪',  
\`call\_encode\` varchar(200) DEFAULT NULL COMMENT '语音编码',  
\`fee\_type\` char(1) DEFAULT NULL COMMENT '费率类型',  
\`fee\_type\_ee\` char(1) DEFAULT NULL,  
PRIMARY KEY (\`id\`),  
KEY \`index\_account\` (\`account\`),  
KEY \`index\_account\_name\` (\`account\_name\`),  
KEY \`index\_agentaccount\` (\`agentaccount\`),  
KEY \`index\_caller\` (\`caller\`),  
KEY \`index\_callee\` (\`callee\`),  
KEY \`index\_callergatewayh323id\` (\`callergatewayh323id\`(64)),  
KEY \`index\_calleegatewayh323id\` (\`calleegatewayh323id\`),  
KEY \`index\_starttime\` (\`starttime\`),  
KEY \`index\_callerproductid\` (\`callerproductid\`),  
KEY \`index\_calleeproductid\` (\`calleeproductid\`),  
KEY \`index\_city\` (\`city\`),  
KEY \`index\_call\_type\` (\`call\_type\`),  
KEY \`index\_stoptime\` (\`stoptime\`)  
)ENGINE=InnoDB AUTO\_INCREMENT=2 DEFAULT CHARSET=utf8");  
  
PREPARE createtablestmt FROM @createTableStr;  
EXECUTE createtablestmt;  
  
  
\-- 开启事务 注意： 必须要在建表语句下面 需要事务的地方进行 如果放在上面 则事务不起作用  
START TRANSACTION ;  
  
  
\-- 如果统计日期的话单详情表存在数据则先删除  
SET @delCDRTableStr = CONCAT( " DELETE FROM ", @tableName );  
PREPARE delCDRTableStmt FROM @delCDRTableStr;  
EXECUTE delCDRTableStmt;  
  
  
\-- 同步抓取vlsdbcdr库统计日期的话单详情表数据 由于表数据量较大，采取分批插入 将EXECUTE结果赋值到@CDRTableRowCount变量中  
SET @countCDRTableStr = CONCAT( " SELECT COUNT(1) INTO @CDRTableRowCount FROM vlsdbcdr.", @tableName );  
PREPARE countCDRTableStmt FROM @countCDRTableStr;  
EXECUTE countCDRTableStmt;  
  
  
\-- 计算需要插入的次数  
SELECT CEIL (@CDRTableRowCount / @EACH\_INSERT\_SIZE) INTO CDR\_TABLE\_INSERT\_COUNT;  
  
WHILE @i <= CDR\_TABLE\_INSERT\_COUNT DO  
  
  
SET @baseColumn = " \`id\`, \`call\_type\`, \`cb\_sessionid\`, \`busiprefix\`, \`caller\_brand\`, \`caller\`, \`calleraccess\`, \`callee\_brand\`, \`callee\`, \`calleeaccess\`, \`calleeoriginal\`,  
\`callerip\`, \`in\_gw\_media\_ip\`, \`in\_gw\_media\_isp\`, \`callergatewayh323id\`, \`callerproductid\`, \`callertogateway\`, \`calleeip\`, \`calleegatewayh323id\`, \`calleeproductid\`,  
\`calleetogateway\`, \`rtp\_ip\`, \`gd\_gw\_media\_isp\`, \`billingmode\`, \`calllevel\`, \`agentfeetime\`, \`starttime\`, \`stoptime\`, \`pdd\`, \`linepdd\`, \`waittime\`, \`holdtime\`,  
\`feeprefix\`, \`feetime\`, \`fee\`, \`package\_id\`, \`package\_fee\`, \`package\_feetime\`, \`account\`, \`account\_name\`, \`enddirection\`, \`endreason\`, \`agentpackagefee\`,  
\`agentpackagefeetime\`, \`agentpackageid\`, \`agentaccount\`, \`agentname\`, \`agentfee\`, \`agentfeeprefix\`, \`gatewayrouting\_type\`, \`caller\_area\`, \`area\_code\`,  
\`province\`, \`city\`, \`switch\`, \`operators\`, \`outgw\_area\`, \`ingw\_type\`, \`routetrace\`, \`call\_encode\`, \`fee\_type\`, \`fee\_type\_ee\` ";  
  
SET @insertCDRStr = CONCAT(" INSERT INTO ", @tableName, "(", @baseColumn, ")", " SELECT ", @baseColumn, " FROM vlsdbcdr.", @tableName,  
" LIMIT ", (@i - 1) \* @EACH\_INSERT\_SIZE, ",", @EACH\_INSERT\_SIZE );  
  
PREPARE insertCDRstmt FROM @insertCDRStr;  
EXECUTE insertCDRstmt;  
  
SET @i = @i + 1;  
  
END WHILE;  
  
  
  
\-- 删除销售客户账单表已存在的统计日期数据  
DELETE FROM t\_sale\_customer\_day\_cost WHERE statistical\_time = statistic\_date;  
  
  
\-- 向销售客户日账单表插入统计日期的数据  
SET @insertSCDCStr = CONCAT( "  
  
INSERT INTO osp.t\_sale\_customer\_day\_cost  
( statistical\_time, customer\_id, account\_id, service\_type, fee\_type, call\_times, caller, caller\_area, feetime, fee, create\_time)  
SELECT '", statistic\_date,"' AS statistical\_time, (SELECT out\_id FROM t\_account AS A WHERE A.account\_id = CDR.account AND A.type = 0) AS customer\_id,  
CDR.account, '5', CDR.fee\_type, count(1) AS call\_count, CDR.caller, CDR.caller\_area, SUM(CDR.feetime / 60), SUM(CDR.fee), NOW()  
FROM ",  
@tableName, " AS CDR  
WHERE  
CDR.call\_type = '5'  
GROUP BY  
customer\_id, CDR.account, CDR.call\_type, CDR.fee\_type ");  
  
PREPARE insertSCDCstmt FROM @insertSCDCStr;  
EXECUTE insertSCDCstmt;  
  
  
\-- 提交事务  
COMMIT ;  
  
END;

四 定时器代码

package com.yzx.osp.modular.task.controlller;  
  
import com.yzx.osp.common.util.DateUtil;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.scheduling.annotation.Scheduled;  
import org.springframework.stereotype.Component;  
  
import java.util.Date;  
  
/\*\*  
\* @author yuanshushu  
\* @date 2018/9/13  
\* @description 话单详情定时任务类  
\*/  
@Component  
public class CdrDateTask \{  
  
@Autowired  
private JdbcTemplate jdbcTemplate;  
  
@Scheduled(cron = "0/10 \* \* \* \* ?")  
public void syncTCdrDateData() \{  
  
try \{  
  
// 按日期生成话单详情表  
this.jdbcTemplate.execute("call create\_current\_day\_cdr\_table()");  
  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
  
  
\}  
  
  
\}

五. 存储过程DEMO 小  
5.1 遍历分批插入

CREATE PROCEDURE test () BEGIN  
  
  
DECLARE CDR\_TABLE\_ROW\_COUNT BIGINT;  
DECLARE CDR\_TABLE\_INSERT\_COUNT INT;  
  
\-- 声明事务  
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK ;  
  
SET @EACH\_INSERT\_SIZE = 100;  
SET @i = 1;  
  
SELECT count(1) FROM t\_cdr\_20180811 INTO CDR\_TABLE\_ROW\_COUNT;  
SELECT CEIL (CDR\_TABLE\_ROW\_COUNT / @EACH\_INSERT\_SIZE) INTO CDR\_TABLE\_INSERT\_COUNT;  
  
  
START TRANSACTION ;  
  
  
WHILE @i <= CDR\_TABLE\_INSERT\_COUNT DO  
  
  
\-- 拼接插入sql 注意： mysql limit m,n m为开始行数, n为查询行数  
SET @insertStr = CONCAT( "  
INSERT INTO t\_cdr\_20180812  
(call\_type, cb\_sessionid)  
SELECT  
call\_type, cb\_sessionid  
FROM  
t\_cdr\_20180811  
limit ", (@i - 1) \* @EACH\_INSERT\_SIZE, ",", @EACH\_INSERT\_SIZE );  
  
PREPARE insertStrStmt FROM @insertStr;  
EXECUTE insertStrStmt;  
  
SET @i = @i + 1;  
  
END WHILE;  
  
COMMIT ;  
  
END;

5.2 获取EXECUTE执行结果  
\-- statistic\_date 统计日期 接收yyyy-MM-dd类型的时间字符串  
CREATE PROCEDURE create\_current\_day\_cdr\_table (IN statistic\_date VARCHAR(64)) BEGIN  
SET @tableName = 't\_cdr\_20180811';  
SET @countCDRTableStr = CONCAT( " SELECT COUNT(1) INTO @STABLE\_CDR\_ROW\_COUNT FROM vlsdbcdr.", @tableName);  
PREPARE countCDRTableStmt FROM @countCDRTableStr;  
EXECUTE countCDRTableStmt;  
  
SELECT @STABLE\_CDR\_ROW\_COUNT;  
  
END;

案例  
建立数据集

\#创建表  
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 (orderid, Itemid, category, orderdate, sales)  
values ('21','k1','A','2020-1-3','954.5'),  
('22','k2','A','2020-2-4','435.4'),  
('23','k1','A','2020-1-8','74'),  
('24','k3','C','2020-7-5','78'),  
('25','k4','B','2020-5-26','35'),  
('26','k5','C','2020-3-10','54'),  
('27','k4','B','2020-4-29','44'),  
('28','k2','A','2020-5-24','32'),  
('29','k3','C','2020-7-7','98'),  
('30','k5','C','2020-4-6','45.6'),  
('31','k1','A','2020-3-23','49.7'),  
('32','k2','A','2020-2-1','76.6'),  
('33','k4','B','2020-2-18','759'),  
('34','k5','C','2020-8-29','99'),  
('35','k2','A','2020-9-29','59'),  
('36','k3','C','2020-10-25','43'),  
('37','k4','B','2020-10-28','22'),  
('38','k5','C','2020-10-3','14'),  
('39','k3','C','2020-6-10','31.4'),  
('40','k4','B','2020-9-11','78.6');  
insert into orders3 (orderid, Itemid, category, orderdate, sales)  
values ('41','k1','A','2020-1-3','954.5'),  
('42','k2','A','2020-2-4','435.4'),  
('43','k1','A','2020-1-8','674'),  
('44','k3','C','2020-7-5','478'),  
('45','k4','B','2020-5-26','635'),  
('46','k5','C','2020-3-10','654'),  
('47','k4','B','2020-4-29','644'),  
('48','k2','A','2020-5-24','632'),  
('49','k3','C','2020-7-7','98'),  
('50','k5','C','2020-4-6','645.6'),  
('51','k1','A','2020-3-23','49.7'),  
('52','k2','A','2020-2-1','676.6'),  
('53','k4','B','2020-2-18','759'),  
('54','k5','C','2020-8-29','99'),  
('55','k2','A','2020-9-29','459'),  
('56','k3','C','2020-10-25','443'),  
('57','k4','B','2020-10-28','522'),  
('58','k5','C','2020-10-3','614'),  
('59','k3','C','2020-6-10','31.4'),  
('60','k4','B','2020-9-11','78.6');  
insert into orders4 (orderid, Itemid, category, orderdate, sales)  
values ('61','k1','A','2020-1-3','954.5'),  
('62','k2','A','2020-2-4','4435.4'),  
('63','k1','A','2020-1-8','874'),  
('64','k3','C','2020-7-5','778'),  
('65','k4','B','2020-5-26','535'),  
('66','k5','C','2020-3-10','754'),  
('67','k4','B','2020-4-29','644'),  
('68','k2','A','2020-5-24','932'),  
('69','k3','C','2020-7-7','898'),  
('70','k5','C','2020-4-6','645.6'),  
('71','k1','A','2020-3-23','449.7'),  
('72','k2','A','2020-2-1','376.6'),  
('73','k4','B','2020-2-18','759'),  
('74','k5','C','2020-8-29','399'),  
('75','k2','A','2020-9-29','159'),  
('76','k3','C','2020-10-25','343'),  
('77','k4','B','2020-10-28','222'),  
('78','k5','C','2020-10-3','114'),  
('79','k3','C','2020-6-10','231.4'),  
('80','k4','B','2020-9-11','178.6');  
insert into orders5 (orderid, Itemid, category, orderdate, sales)  
values ('81','k1','A','2020-1-3','1954.5'),  
('82','k2','A','2020-2-4','1435.4'),  
('83','k1','A','2020-1-8','174'),  
('84','k3','C','2020-7-5','178'),  
('85','k4','B','2020-5-26','35'),  
('86','k5','C','2020-3-10','154'),  
('87','k4','B','2020-4-29','144'),  
('88','k2','A','2020-5-24','132'),  
('89','k3','C','2020-7-7','198'),  
('90','k5','C','2020-4-6','145.6'),  
('91','k1','A','2020-3-23','149.7'),  
('92','k2','A','2020-2-1','176.6'),  
('93','k4','B','2020-2-18','1759'),  
('94','k5','C','2020-8-29','199'),  
('95','k2','A','2020-9-29','159'),  
('96','k3','C','2020-10-25','143'),  
('97','k4','B','2020-10-28','122'),  
('98','k5','C','2020-10-3','114'),  
('99','k3','C','2020-6-10','131.4'),  
('100','k4','B','2020-9-11','178.6');

创建存储过程  
创建一个表data来储存查询结果数据集

create table data (category varchar(10) not null ,orderid int(11) not null primary key ,  
Itemid varchar(30) not null ,  
sales float not null  
);

\#如果你需要调试存储过程一定要加这句代码  
drop procedure if exists insert\_odata;

存储过程主体

delimiter ;;  
create procedure insert\_odata (IN i int)  
begin  
DECLARE i int;  
set i=1;  
while i<=5 do  
set @sqlStr:=CONCAT('insert into data (select category, orderid,Itemid,sales from(  
select category,orderid,Itemid,sales,  
row\_number()over(partition by category order by category,sales desc ) as ranking  
from orders',i,') t1 where ranking <=2);');  
prepare stmt from @sqlStr;  
execute stmt;  
deallocate prepare stmt;  
set i=i+1;  
end while  
end  
;;  
delimiter ;

调用存储过程，并查看数据结果

call insert\_odata()  
select \* from data;

最后求所有数据每个category,sales的前两名

select category,orderid,Itemid,sales from  
(select category,orderid,Itemid,sales,  
row\_number()over(partition by category,Itemid order by category,sales desc ) as ranking  
from data) t1 where ranking <=2  
);

一点小拓展

我在第一次写的时候，因为不知道在MySQL中使用循环需要将其放入存储过程，我的思路是将表名作为变量创建存储过程，然后再通过循环调用存储过程，导致第一个方案用了两个存储过程。

create table data (category varchar(10) not null ,orderid int(11) not null primary key ,  
Itemid varchar(30) not null ,  
sales float not null  
);  
\#第一个存储过程  
\#用表作为变量  
drop procedure if exists insert\_odata;  
delimiter ;;  
create procedure insert\_odata (IN temp varchar(30))  
begin  
set @tempStr:=CONCAT('insert into data (select category,orderid,Itemid,sales from(  
select category,orderid,Itemid,sales,  
row\_number()over(partition by category order by category,sales desc ) as ranking  
from ',temp,') t1 where ranking <=2);');  
prepare stmt from @tempStr;  
execute stmt;  
deallocate prepare stmt;  
end  
;;  
delimiter ;

\#第二个 存储过程  
drop procedure if exists run;  
delimiter $$  
create procedure run()  
begin  
DECLARE i int;  
DECLARE Tname varchar(25);  
set i=1;  
while i<=5 do  
set Tname =concat('orders',i);  
call insert\_odata(Tname);  
set i=i+1;  
end while;  
end  
$$

delimiter ;  
call run();  
select \* from data;

[5c8bd17cdb134645bd203ac108e132c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8788e976b0b649a785a1bd38a2200944.png