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

posted @ 2024-03-14 16:50  陈娟  阅读(63)  评论(0)    收藏  举报