有没有办法在查询中使表名动态化? [英] Is there a way to make a table name dynamic in a query?

问题描述

我正在尝试为产品创建类继承设计.

I am trying to create a Class-Inheritance design for products.

有包含所有公共字段的基表.然后对于每种产品类型，都有一个单独的表格，其中包含仅适用于该产品类型的字段

There is the base table that contains all the common fields. Then for each product type there is a separate table containing the fields that are for that product type only

因此，为了获取产品的所有数据，我需要将基表与与基表中列出的 product_type 相关联的任何表JOIN.有没有办法让这个查询动态连接到表上?

So in order to get all the data for a product I need to JOIN the base table with whatever table that correlates to the product_type listed in the base table. Is there a way to make this query join on the table dynamically?

这是一个查询，试图说明我正在尝试做的事情:

Here is a query to try to illustrate what I am trying to do:

SELECT * FROM product_base b
INNER JOIN <value of b.product_type> t
    ON b.product_base_id = t.product_base_id 
WHERE b.product_base_id = :base_id

有没有办法做到这一点?

Is there a way to do this?

推荐答案

不，没有办法做到这一点.在解析查询时必须知道表名，以便解析器可以判断该表是否存在，以及它是否包含您引用的列.优化器还需要了解表及其索引，以便制定使用哪些索引的计划.

No, there's no way to do this. The table name must be known at the time of parsing the query, so the parser can tell if the table exists, and that it contains the columns you reference. Also the optimizer needs to know the table and its indexes, so it can come up with a plan of what indexes to use.

您要求的是在执行期间根据逐行找到的数据确定表.RDBMS 无法在解析时知道所有数据值对应于真实表.

What you're asking for is for the table to be determined during execution, based on data found row-by-row. There's no way for the RDBMS to know at parse-time that all the data values correspond to real tables.

您没有理由这样做来实现类表继承.CTI 支持表之间的真实引用.

There's no reason you would do this to implement Class Table Inheritance. CTI supports true references between tables.

您正在描述多态关联的反模式.

You're instead describing the antipattern of Polymorphic Associations.

这篇关于有没有办法在查询中使表名动态化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！