用于从某些特定格式“xyz _ ****”的表名中读取公共列数据的通用查询 [英] Generic query for reading common column data from table names of some specific format "xyz_****"

问题描述

我在数据库中有多个表（让数据库名称为metadata.mdb，这是ms访问数据库），表名为



type xyz _ ****（例如xyz_6121，xyz_3438，xyz_1111，....），

abc _ ****（例如abc_1233，abc_4560，abc_1427，...），

pqr _ ** **依旧..



特定类型的每个表，例如xyz _ ****类型有一些常见的列，例如表xyz_6121，xyz_3438，xyz_1111， ....列x，y和z是常见的。我想编写一个查询，可以从特定类型的表中获取此类列（x，y和z）的所有数据，即xyz _ ****（即使可能不通过查询单个表）。我必须使用C＃应用程序将此数据写入Excel工作表，但我无法找到可以解决问题的查询。



截至目前我只需要知道，是否有任何通用的方法，以便我们可以从多个表中查询一些常见的列数据（具有表名（xyz _ ****），从一些已知单词xyz_开始，以任何随机数结尾（ ****其中每个*代表一个数值）），需要帮助。如果我不清楚请告诉我。



我尝试过：



我没有找到任何通用方法，所以我们可以查询来自多个表的一些常见列数据（具有表名（xyz _ ****），从一些已知单词xyz_开始，以任意随机数结尾（* ***其中每个*表示一个数值）），但我可以对每个表进行单独查询，问题是如果我们有50个或更多以xyz_开头的表，那么如何处理它们。

i想要所有这些类型的查询通用。

从xyz_6121中选择x，y，z

从xyz_3438选择x，y，z

I have multiple tables in a database(let database name metadata.mdb which is ms access database) with table names of

type xyz_**** (e.g xyz_6121, xyz_3438, xyz_1111,....),
abc_**** (e.g abc_1233,abc_4560,abc_1427,...),
pqr_**** and so on..

each table of a particular type for example xyz_**** type has some common columns let's say table xyz_6121, xyz_3438, xyz_1111,.... have column x,y and z as common. I want to write a query that can get all data of such columns(x,y and z) from a particular type of table i.e xyz_**** at once(i.e if possible not by querying individual table ). I have to get this data using C# application to write it into an excel sheet but I am not able to find a query that can solve the issue.

As of now i only need to know, Is there any generic way so that we can query for some common column data from multiple tables (having table name(xyz_****) starting with some known word "xyz_" and ending with any random number("****" where each * represent a numeric value)), need help on this. Let me know if i m not clear.

What I have tried:

I found nothing for any generic way so that we can query for some common column data from multiple tables (having table name(xyz_****) starting with some known word "xyz_" and ending with any random number("****" where each * represent a numeric value)), but i can indiviual query for each table, problem is if we have 50 or more tables starting with "xyz_", then how to deal with them.
i want something generic for all these type of queries.
select x, y, z from xyz_6121
select x, y, z from xyz_3438

推荐答案

我不认为你问的是可能的。无论你做什么，最终都会有50个单独的查询到达数据库。



我认为你有这些选择



1）您可以使用动态SQL并将数字后缀存储在var表中，遍历var表以将所述数据填充到临时表中并从临时表中查询。



伪代码将是

I don't think what you are asking is possible. No matter what you do your going to end up with 50 individual queries hitting the database.

I think you've got these options

1) You could use Dynamic SQL and store your number suffixes in a var table, loop over the var table to populate said data into a temp table and query from the temp table.

Pseudo code for this would be

INSERT INTO @Tables(tablename) 
SELECT 
    TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'xyz_%';

DECLARE @TablesId INT = (SELECT MIN(Id) FROM @Tables);

WHILE @TablesId IS NOT NULL
BEGIN
   DECLARE @TableName varchar(200) = (SELECT tablename FROM @Tables WHERE Id = @TablesId);
   DECLARE @Sql varchar(500) = 'INSERT INTO #aggregationtable (x,y,z) SELECT x,y,z FROM xyz ' + @TableName;
   
   EXEC sp_executesql @Sql;

END;
--Then you can query from the temp table to get all of your data

SELECT * FROM #aggregationtable

2）您可以创建一个视图，然后查询该视图。您可以使用临时表填充所有数据并查询数据。但你仍然要查询50次。



3）你的查询中都有一个联合但是UNION是一个昂贵的操作，并且在50个SELECT语句中可能不会运行有效。

2) You could create a View and then query out of that view. You could use a temp table to populate all that data and query out of the data. But your still querying 50 times.

3) Do a union all in your query but UNION is an expensive operation, and that on 50 SELECT statements will probably not run very efficiently.

SELECT x,y,z FROM xyz_0001
UNION ALL
SELECT x,y,z FROM xyz_0002
UNION ALL
SELECT x,y,z FROM xyz_0003
UNION ALL
SELECT x,y,z FROM xyz_0004

4）不要通过表名对表进行版本控制，而是添加datetime列或versio你的表的数字列，所以你没有这个问题。

4) Don't version your tables via table name, instead add a datetime column or a version number column to your tables so you don't have this problem.

唯一的方法是使用VBA！



首先，您需要使用以下查询列出所有表：

The only way to achieve that is to use VBA!

First of all, you'll need to list all tables by using below query:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects AS mso
WHERE ((mso.[Name] Like <>'xyz_*') AND (msoType In (1,4,6)) AND (mso.Flags=0));

或使用VBA代码：在Access数据库中列出表 [ ^ ] - 推荐！



然后你需要遍历表的名称来创建像这样的查询字符串：

or by using VBA code: Listing the Tables in an Access Database[^] - recommended!

Then you'll need to loop through the names of tables to create query-string like this:

If .Fields("TABLE_TYPE") <> "VIEW" AND .Fields("TABLE_NAME") Like "xyz*" Then 
    sQuery = sQuery & vbcr & "SELECT x, y, z FROM " & .Fields("TABLE_NAME") & vbCr & _
         "UNION ALL"
End If

最后，你可以创建DAO .QueryDef或从字符串变量执行查询。



详情请见：

如何从MS Access数据库中获取表名？ - 堆栈溢出 [ ^ ]

RunSQL vs Execute - 访问维基 - 访问帮助和操作方法 - UtterAccess.com的Microsoft Office [ ^ ]

Microsoft Access技巧：使用VBA代码访问数据 - 介绍DAO记录集 [ ^ ]

Microsoft Access技巧：DAO编程代码示例 [ ^ ]

如何使用MS Access中的记录集（Dao） [ ^ ]

Finally, you can create DAO.QueryDef or execute query directrly from string variable.

For further details, please see:
How can I get table names from an MS Access Database? - Stack Overflow[^]
RunSQL vs Execute - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com[^]
Microsoft Access Tips: Accessing Data with VBA Code - Introducing the DAO Recordset[^]
Microsoft Access tips: DAO Programming Code Examples[^]
How to work with recordset (Dao) in MS Access[^]

这篇关于用于从某些特定格式“xyz _ ****”的表名中读取公共列数据的通用查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！