MySQL毗连表,个中表名是另一个表的字段

我有5张桌子.一个首要和其它四个(他们有差异的列).

>工具
> obj_mobiles
> obj_tablets
> obj_computers

这是我的主表(工具)的布局.

ID | type | name | etc…

以是我想要做的是将工具与其他(obj_mobiles,obj_tablets,…)表毗连,详细取决于范例字段.
我知道我应该行使动态SQL.但我无法建造措施.我以为应该看起来像这样.

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行.
感谢. 最佳谜底 假如您想要全部行(批量输出)而不是一次一行,则下面应该很快,而且全部行的输出都将包括全部列.

让我们在下面思量表格的字段.
obj_mobiles – ID | M1 | M2
obj_tablets – ID | T1 | T2
obj_computers – ID | C1 | C2
工具 – ID |范例|名字|等等.,

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,T1,T2,NULL from obj_tablets 
    union all
    select ID as oID,"computer" as otype,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,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);

还要确认列的数据范例与原始列沟通,功效将生涯到表中,并在下面搜查数据范例.

create table temp_result as
Select objects.*,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)

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!