我有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)