mysql语句连接另一个表_MySQL连接表,其中表名是另一个表的字段

如果您想要所有行(批量输出)而不是一次一行,则下面应该很快,并且所有行的输出都将包含所有列.

让我们在下面考虑表格的字段.

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)