Mysql 100个表嵌套查询 存储过程

背景

　　1. 业务销售订单会随机落在1～100表中，查询一个订单时需要1到100表依次去查询，增加手工重复操作和浪费时间。

　　2. 查询未解冻数据时，需要过滤部分解冻的数据，此时需要用到嵌套查询。

 

一、根据订单号，循环1~100个表，查询出订单数据

CREATE DEFINER=`{数据库连接账号}`@`%` PROCEDURE `{数据库库名}`.`{存储过程名}`(

)

　　SQL SECURITY INVOKER

begin

//定义变量

declare i int;

set i=1;

set @selects = "SELECT * FROM (";

while i<99 do

set @selects = concat(@selects,

"SELECT *,",i," FROM {表名}", i,     -- " FROM {表名}", i ：此处是循环获取表名

" WHERE 字段名 = '订单号' UNION ALL ");

set i=i+1;

end while;

-- 查99个表

 

-- 查第100个表

set @selects = concat(@selects,

"SELECT *,",i," FROM  {表名}", i,

" WHERE 字段名 = '订单号' ");

 

-- 一共查100个表并排序

set @selects = concat(@selects,") a ");

PREPARE selects FROM @selects;

EXECUTE selects;

end

 

二、查询未解冻数据时，需要过滤部分解冻的数据

CREATE DEFINER=`{数据库连接账号}`@`%` PROCEDURE `{数据库库名}`.`{存储过程名}`(

)

　　SQL SECURITY INVOKER

begin

//定义变量

declare i int;

set i=1;

set @selects = "SELECT * FROM (";

while i<99 do

set @selects = concat(@selects,

"SELECT *,",i," FROM {表名}", i,     -- " FROM {表名}", i ：此处是循环获取表名

" WHERE 字段名 = '订单号'

and 字段名 not in (" "select 字段名 "," from {表名}", i, " where 字段名 = 数据 ")

UNION ALL ");

set i=i+1;

end while;

-- 查99个表

 

-- 查第100个表

set @selects = concat(@selects,

"SELECT *,",i," FROM  {表名}", i,

" WHERE 字段名 = '订单号'

and 字段名 not in (" "select 字段名 "," from {表名}", i, " where 字段名 = 数据 ")

");

 

-- 一共查100个表并排序

set @selects = concat(@selects,") a ");

PREPARE selects FROM @selects;

EXECUTE selects;

end