MySQL联接表,其中表名是另一个表的字段 [英] MySQL join tables where table name is a field of another table
问题描述
我有5张桌子.一个主数据库和另外四个主数据库(它们具有不同的列).
I have 5 tables. One primary and 4 additional (they have different columns).
- 对象
- obj_mobiles
- obj_tablets
- obj_computers
这是我的主表(对象)的结构.
Here is the structure of my main table (objects).
ID |类型名称|等等...
ID | type | name | etc...
所以我想做的就是根据类型字段将对象与其他(obj_mobiles,obj_tablets,...)表联接. 我知道我应该使用动态SQL.但我无法制定程序.我认为它应该看起来像这样.
So what I want to do is to join objects with other (obj_mobiles,obj_tablets,...) tables, depending on type field. I know that I should use dynamic SQL. But I can't make procedure. I think it should look like something like this.
SELECT objects.type into @tbl FROM objects;
PREPARE stmnt FROM "SELECT * FROM objects AS object LEFT JOIN @tbl AS info ON object.id = info.obj_id";
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
Aslo伪代码
SELECT * FROM objects LEFT JOIN [objects.type] ON ...
任何人都可以发布程序吗?我也想让所有的行不只是1行. 谢谢.
Can anyone post procedure? Also I want to have all rows not just 1 row. Thanks.
推荐答案
如果您希望所有行(批量输出)而不是一次仅输出一行,则下面的内容应该很快,并且所有行的输出将包含所有列
If you want all rows (bulk output) and not one row at a time, the below should be fast and also the output for all rows will contain all columns.
让我们在下面将其视为表的字段. obj_mobiles-ID | M1 | M2 obj_tablets-ID | T1 | T2 obj_computers-ID | C1 | C2 对象-ID |类型名称|等等,
Lets consider below to be the fields of the tables. obj_mobiles - ID | M1 | M2 obj_tablets - ID | T1 | T2 obj_computers - ID | C1 | C2 objects - ID | type | name | etc.,
Select objects.*, typestable.*
from (
select ID as oID, "mobile" as otype, M1, M2, NULL T1, NULL T2, NULL C1, NULL C2 from obj_mobiles
union all
select ID as oID, "tablet" as otype, NULL, NULL, T1, T2, NULL, NULL from obj_tablets
union all
select ID as oID, "computer" as otype, NULL, NULL, NULL, NULL, C1, C2 from obj_computers) as typestable
left join objects on typestable.oID = objects.ID and typestable.otype = objects.type;
+------+--------------------+----------+------+----------+------+------+------+------+------+------+
| ID | name | type | ID | type | M1 | M2 | T1 | T2 | C1 | C2 |
+------+--------------------+----------+------+----------+------+------+------+------+------+------+
| 1 | Samsung Galaxy s2 | mobile | 1 | mobile | 1 | Thin | NULL | NULL | NULL | NULL |
| 2 | Samsung Galaxy Tab | tablet | 2 | tablet | NULL | NULL | 0.98 | 10 | NULL | NULL |
| 3 | Dell Inspiron | computer | 3 | computer | NULL | NULL | NULL | NULL | 4.98 | 1000 |
+------+--------------------+----------+------+----------+------+------+------+------+------+------+
该表如下创建.
mysql> create table objects (ID int, name varchar(50), type varchar (15));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into objects values (1, "Samsung Galaxy s2", "mobile"), (2, "Samsung Galaxy Tab", "tablet"), (3, "Dell Inspiron", "computer");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table obj_mobiles (ID int, M1 int, M2 varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into obj_mobiles values (1, 0.98, "Thin");
Query OK, 1 row affected (0.00 sec)
mysql> create table obj_tablets (ID int, T1 float, T2 int(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into obj_tablets values (2, 0.98, 10);
Query OK, 1 row affected (0.00 sec)
mysql> create table obj_computers (ID int, C1 float, C2 int(10));
Query OK, 0 rows affected (0.03 sec)
insert into obj_computers values (3, 4.98, 1000);
还要确认列的数据类型与原始列相同,将结果保存到表中并在下面检查数据类型.
also to confirm the datatypes of the columns are same as the original columns, the result is saved into a table and datatypes are checked below.
create table temp_result as
Select objects.*, typestable.*
from (
select ID as oID, "mobile" as otype, M1, M2, NULL T1, NULL T2, NULL C1, NULL C2 from obj_mobiles
union all
select ID as oID, "tablet" as otype, NULL, NULL, T1, T2, NULL, NULL from obj_tablets
union all
select ID as oID, "computer" as otype, NULL, NULL, NULL, NULL, C1, C2 from obj_computers) as typestable
left join objects on typestable.oID = objects.ID and typestable.otype = objects.type;
mysql> desc temp_result;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| type | varchar(15) | YES | | NULL | |
| oID | int(11) | YES | | NULL | |
| otype | varchar(8) | NO | | | |
| M1 | int(11) | YES | | NULL | |
| M2 | varchar(10) | YES | | NULL | |
| T1 | float | YES | | NULL | |
| T2 | int(11) | YES | | NULL | |
| C1 | float | YES | | NULL | |
| C2 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
这篇关于MySQL联接表,其中表名是另一个表的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!