从所有表中选择-MySQL [英] Select From all tables - MySQL

问题描述

我有一个名为listDB的mySQL数据库，其中包含几个列名称为Product等的表.我想从所有产品(如％XYZ％)中的SELECT并在单独的表中显示搜索结果.

I have a mySQL database called listDB that contain several tables with column name Product etc. I want to SELECT from all tables where Product Like %XYZ%, and display the search result in a separate table.

我尝试过:

SELECT * FROM * WHERE Product LIKE %XYZ%

但是它不起作用.为此目的正确的查询是什么?

But it is not working. What is the right query for this purpose?

推荐答案

使用此语句，您将获得包含列产品的所有表:

You get all tables containing the column product using this statment:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('Product')
        AND TABLE_SCHEMA='YourDatabase';

然后，您必须在这些计算机上运行光标表格，以便您每次都选择:

Then you have to run a cursor on these tables so you select eachtime:

Select * from OneTable where product like '%XYZ%'

结果应输入到第三张表或视图中，看看此处.

The results should be entered into a 3rd table or view, take a look here.

注意:仅当所有表的结构相似时，此方法才有效，否则，您将必须查看所有这些表的哪些列是统一的，并创建结果表/视图以仅包含这些列.

Notice: This can work only if the structure of all table is similar, otherwise aou will have to see which columns are united for all these tables and create your result table / View to contain only these columns.

这篇关于从所有表中选择-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！