MySQL使用子查询来获取表名? [英] MySQL Using Subquery To Get Table Name?

问题描述

我正在尝试从MySQL数据库中选择一些数据.

I'm trying to select some data from a MySQL database.

我有一张包含业务详细信息的表，还有一个单独的包含交易列表的表.因为我们有多笔交易

I have a table containing business details, and a seperate one containing a list of trades. As we have multiple trades

business_details

id | business_name | trade_id | package_id
1  | Happy News    |    12    |    1

这是主表，包含公司名称，交易ID和包裹ID

This is the main table, contains the business name, the trade ID and the package ID

shop_trades

id | trade
1  | newsagents

这包含商家的贸易类型

configuration_packages

id | name_of_trade_table
1  | shop_trades
2  | leisure_trades

其中包含要查看的交易表的名称

This contains the name of the trade table to look in

因此，基本上，如果我想查找交易类型(例如，新闻代理，快餐等)，请查看XXXX_trades表.但是我首先需要从configuration_packages表中查找XXXX的名称.

So, basically, if I want to find the trade type (e.g., newsagent, fast food, etc) I look in the XXXX_trades table. But I first need to look up the name of XXXX from the configuration_packages table.

我通常要做的是2个SQL查询:

What I would normally do is 2 SQL queries:

SELECT business_details.*, configuration_packages.name_of_trade_table
FROM business_details, configuration_packages
WHERE business_details.package_id = configuration_packages.id
AND business_details.id = '1'

这给了我数据库表的名称以寻找商品名，所以我查找了表的名称

That gives me the name of the database table to look in for the trade name, so I look up the name of the table

SELECT trade FROM XXXX WHERE id='YYYY'

其中XXXX是作为第一个查询的一部分返回的表的名称，而YYYY是包的ID，也是从第一次查询返回的包的ID.

Where XXXX is the name of the table returned as part of the first query and YYYY is the id of the package, again returned from the first query.

有没有一种方法可以将这两个查询组合在一起，使我只运行一个查询?

Is there a way to combine these two queries so that I only run one?

我以前使用过子查询，但仅在查询的SELECT端使用了-而不是FROM端.

I've used subqueries before, but only on the SELECT side of the query - not the FROM side.

推荐答案

通常，这是由单个查询中的联合来处理的.

Typically, this is handled by a union in a single query.

规范化使您进入逻辑模型.这有助于更好地理解数据.在实现模型时，通常会进行非规范化.您在此处拥有的子类型通常以两种方式实现:

Normalization gets you to a logical model. This helps better understand the data. It is common to denormalize when implementing the model. Subtypes as you have here are commonly implemented in two ways:

• 像您一样使用单独的表，这使检索变得很困难.这导致您提出有关如何检索数据的问题.
• 带有子类型指示符的所有子类型的公用表.这可能会导致某些子类型的列始终为空.它简化了数据访问，并可能更改在代码中处理子类型的方式.

如果很少访问子类型的额外列，则可以使用混合实现，其中公共列在类型表中，而某些或所有子类型列在子表中.这对代码来说更复杂.

If the extra columns for a subtype are relatively rarely accessed, then you may use a hybrid implementation where the common columns are in the type table, and some or all of the subtype columns are in a subtype table. This is more complex to code.

这篇关于MySQL使用子查询来获取表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！