如何查询特定键值对的多个SQL表？

Situation: A PHP application with multiple installable modules creates a new table in database for each, in the style of mod_A, mod_B, mod_C etc. Each has the column section_id.

情况:具有多个可安装模块的PHP应用程序在数据库中为每个创建一个新表,其格式为mod_A,mod_B,mod_C等。每个都有列section_id。

Now, I am looking for all entries for a specific section_id, and I'm hoping there's another way besides "Select * from mod_a, mod_b, mod_c ... mod_xyzzy where section_id=value"... or even worse, using a separate query for each module.

现在,我正在寻找特定section_id的所有条目,我希望除了“选择*来自mod_a,mod_b,mod_c ... mod_xyzzy where section_id = value”之外还有另一种方式......或者更糟糕的是,使用单独的查询每个模块。

8 个解决方案

#1

If the tables are changing over time, you can inline code gen your solution in an SP (pseudo code - you'll have to fill in):

如果表随着时间的推移而变化,您可以在SP中内联代码生成您的解决方案(伪代码 - 您必须填写):

SET @sql = ''

DECLARE CURSOR FOR
SELECT t.[name] AS TABLE_NAME
FROM sys.tables t
WHERE t.[name] LIKE 'SOME_PATTERN_TO_IDENTIFY_THE_TABLES'

-- or this

- 或这个

DECLARE CURSOR FOR
SELECT t.[name] AS TABLE_NAME
FROM TABLE_OF_TABLES_TO_SEACRH t

START LOOP

IF @sql <> '' SET @sql = @sql + 'UNION ALL '
SET @sql = 'SELECT * FROM [' + @TABLE_NAME + '] WHERE section_id=value '

END LOOP

EXEC(@sql)

I've used this technique occasionally, when there just isn't any obvious way to make it future-proof without dynamic SQL.

我偶尔也会使用这种技术,如果没有任何明显的方法可以在没有动态SQL的情况下使其面向未来。

Note: In your loop, you can use the COALESCE/NULL propagation trick and leave the string as NULL before the loop, but it's not as clear if you are unfamiliar with the idiom:

注意:在你的循环中,你可以使用COALESCE / NULL传播技巧并在循环之前将字符串保留为NULL,但是如果你不熟悉这个习语就不那么清楚了:

SET @sql = COALESCE(@sql + ' UNION ALL ', '')
    + 'SELECT * FROM [' + @TABLE_NAME + '] WHERE section_id=value '

#2

What about?

SELECT * FROM mod_a WHERE section_id=value
UNION ALL
SELECT * FROM mod_b WHERE section_id=value
UNION ALL
SELECT * FROM mod_c WHERE section_id=value

#3

I have two suggestions.

我有两个建议。

Perhaps you need to consolidate all your tables. If they all contain the same structure, then why not have one "master" module table, that just adds one new column identifying the module ("A", "B", "C", ....)

也许您需要整合所有表格。如果它们都包含相同的结构,那么为什么没有一个“主”模块表,只需添加一个标识模块的新列(“A”,“B”,“C”,......)

If your module tables are mostly the same, but you have a few columns that are different, you might still be able to consolidate all the common information into one table, and keep smaller module-specific tables with those differences. Then you would just need to do a join on them.

如果您的模块表大多相同,但是您有一些不同的列,您仍然可以将所有公共信息合并到一个表中,并保留较小的模块特定表以及这些差异。然后你只需要对它们进行连接。

This suggestion assumes that your query on the column section_id you mention is super-critical to look up quickly. With one query you get all the common information, and with a second you would get any specific information if you needed it. (And you might not -- for instance if you were trying to validate the existense of the section, then finding it in the common table would be enough)

这个建议假设你提到的列section_id上的查询对于快速查找是非常关键的。只需一个查询即可获得所有常用信息,如果需要,您可以获得任何特定信息。 (你可能没有 - 例如,如果你试图验证该部分的存在性,那么在公共表中找到它就足够了)
Alternatively you can add another table that maps section_id's to the modules that they are in.

或者,您可以添加另一个表,将section_id映射到它们所在的模块。
```
section_id | module
-----------+-------
      1    |  A
      2    |  B
      3    |  A
     ...   | ...
```
This does mean though that you have to run two queries, one against this mapping table, and another against the module table to pull out any useful data.

这确实意味着您必须运行两个查询,一个针对此映射表,另一个针对模块表以提取任何有用的数据。

You can extend this table with other columns and indices on those columns if you need to look up other columns that are common to all modules.

如果需要查找所有模块通用的其他列,可以使用这些列上的其他列和索引扩展此表。

This method has the definite disadvanage that the data is duplicated.

这种方法具有明确的不利之处,即数据是重复的。

#4

Perhaps some additional info would help, but it sounds like you have the solution already. You will have to select from all the tables with a section_id. You could use joins instead of a table list, joining on section_id. For example

也许一些额外的信息会有所帮助,但听起来你已经有了解决方案。您必须使用section_id从所有表中进行选择。您可以使用连接而不是表列表,加入section_id。例如

select a.some_field, b.some_field.... 
from mod_a a
inner join mod_b b on a.section_id = b.section_id
...
where a.section_id = <parameter>

You could also package this up as a view. Also notice the field list instead of *, which I would recommend if you were intending to actually use *.

您也可以将其打包为视图。另请注意字段列表而不是*,如果您打算实际使用*,我建议使用*。

#5

Well, there are only so many ways to aggregate information from multiple tables. You can join, like you mentioned in your example, or you can run multiple queries and union them together as in borjab's answer. I don't know if some idea of creating a table that intersects all the module tables would be useful to you, but if section_id was on a table like that you'd be able to get everything from a single query. Otherwise, I applaud your laziness, but am afraid to say, I don't see any way to make that job eaiser :)

好吧,只有很多方法可以聚合来自多个表的信息。您可以加入,就像您在示例中提到的那样,或者您可以运行多个查询并将它们组合在一起,就像在borjab的答案中一样。我不知道创建一个与所有模块表相交的表的想法是否对你有用,但是如果section_id在一个像这样的表上你就可以从一个查询获得所有东西。否则,我为你的懒惰喝彩,但我不敢说,我看不出任何办法让你的工作变得更好:)

#6

I was going to suggest the same think as borjab. The only problem with that is that you will have to update all of these queries if you add another table. The only other option I see is a stored procedure.

我打算建议和borjab一样的想法。唯一的问题是,如果添加另一个表,则必须更新所有这些查询。我看到的唯一其他选项是存储过程。

I did think of another option here, or at least an easier way to present this. You can also use a view to these multiple tables to make them appear as one, and then your query would look cleaner, be easier to understand and you wouldn't have to rewrite a long union query when you wanted to do other queries on these multiple tables.

我确实想到了另一种选择,或者至少是一种更简单的方式来呈现这一点。您还可以使用这些多个表的视图使它们显示为一个,然后您的查询看起来更干净,更容易理解,当您想对这些查询执行其他查询时,您不必重写长联合查询多个表格。

#7

SELECT * FROM (
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
    UNION ALL
    SELECT * FROM table3
) subQry
WHERE field=value

#8

An option from the database side would be to create a view of the UNION ALL of the various tables. When you add a table, you would need to add it to the view, but otherwise it would look like a single table.

数据库端的一个选项是创建各种表的UNION ALL视图。添加表时,需要将其添加到视图中,否则它将看起来像一个表。

CREATE VIEW modules AS (
    SELECT * FROM mod_A
    UNION ALL 
    SELECT * FROM mod_B
    UNION ALL 
    SELECT * FROM mod_C
);

select * from modules where section_id=value;

#1