我有一个表
如下
SubTable
(
SubTableID PK
TYPEVALUE int --//指出了对应主表(非PK)
TYPEID int --//指出了对应主键编号(非PK)
)//------------
MainTable1
(
AppleID INT PK
AppleNAME char
)
//------------------
MainTable2
(
OrangeID int PK
OrangeName char
)
===========================
当SubTable 的TYPEVALUE =111则说明这条记录对应的是MainTable1,TYPEID 是对应MainTable1的AppleID
当SubTable 的TYPEVALUE =222则说明这条记录对应的是MainTable2,TYPEID 是对应MainTable1的OrangeID
//=========================
现在要查询SubTable 并且显示出对应的名称 ,就是该记录所关联的AppleNAME 或OrangeName
要求用一个SQL语句查询 且对性能要求比较高 请高手帮我看看
对性能最优的答案给分 谢谢
如下
SubTable
(
SubTableID PK
TYPEVALUE int --//指出了对应主表(非PK)
TYPEID int --//指出了对应主键编号(非PK)
)//------------
MainTable1
(
AppleID INT PK
AppleNAME char
)
//------------------
MainTable2
(
OrangeID int PK
OrangeName char
)
===========================
当SubTable 的TYPEVALUE =111则说明这条记录对应的是MainTable1,TYPEID 是对应MainTable1的AppleID
当SubTable 的TYPEVALUE =222则说明这条记录对应的是MainTable2,TYPEID 是对应MainTable1的OrangeID
//=========================
现在要查询SubTable 并且显示出对应的名称 ,就是该记录所关联的AppleNAME 或OrangeName
要求用一个SQL语句查询 且对性能要求比较高 请高手帮我看看
对性能最优的答案给分 谢谢
from SubTable a left join MainTable1 b on a.TYPEID=b.AppleID
left join MainTable2 c on a.TYPEID=c.OrangeID
from SubTable a left join MainTable1 b on a.TYPEID=b.AppleID
left join MainTable2 c on a.TYPEID=c.OrangeID
这个可以用吗
MYSQL特有的吗
这个字段与TYPEID连接,有可能产生重复记录
OUT可以省略
+------------+-----------+--------+
| SubTableID | TYPEVALUE | TYPEID |
+------------+-----------+--------+
| 1 | 111 | 1 |
| 2 | 111 | 2 |
| 3 | 222 | 1 |
| 4 | 222 | 2 |
+------------+-----------+--------+
4 rows in set (0.00 sec)mysql> select * from MainTable1;
+---------+-----------+
| AppleID | AppleNAME |
+---------+-----------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
+---------+-----------+
3 rows in set (0.00 sec)mysql> select * from MainTable2;
+----------+------------+
| OrangeID | OrangeName |
+----------+------------+
| 1 | b1 |
| 2 | b2 |
| 3 | b3 |
+----------+------------+
3 rows in set (0.00 sec)mysql>
mysql> select s.*,t.cn
-> from SubTable s inner join (
-> select 111 as kid,AppleID as id,AppleNAME as cn
-> from MainTable1
-> union all
-> select 222 as kid,OrangeID as id,OrangeName as cn
-> from MainTable2
-> ) t on s.TYPEVALUE=t.kid and s.TYPEID=t.id;
+------------+-----------+--------+------+
| SubTableID | TYPEVALUE | TYPEID | cn |
+------------+-----------+--------+------+
| 1 | 111 | 1 | a1 |
| 2 | 111 | 2 | a2 |
| 3 | 222 | 1 | b1 |
| 4 | 222 | 2 | b2 |
+------------+-----------+--------+------+
4 rows in set (0.00 sec)mysql>
mysql> explain select s.*,t.cn
-> from SubTable s inner join (
-> select 111 as kid,AppleID as id,AppleNAME as cn
-> from MainTable1
-> union all
-> select 222 as kid,OrangeID as id,OrangeName as cn
-> from MainTable2
-> ) t on s.TYPEVALUE=t.kid and s.TYPEID=t.id;
+----+--------------+------------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+--------------------------------+
| 1 | PRIMARY | s | ALL | NULL | NULL | NULL | NULL| 4 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL| 6 | Using where; Using join buffer |
| 2 | DERIVED | MainTable1 | ALL | NULL | NULL | NULL | NULL| 3 | |
| 3 | UNION | MainTable2 | ALL | NULL | NULL | NULL | NULL| 3 | |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+------+---------------+------+---------+------+------+--------------------------------+
5 rows in set (0.06 sec)mysql>上述方法,可以支持多个MainTable1,MainTable2,...MainTablen,在程序或存储过程比较灵活。
如果同样是多表,则可以用下面的第二句语句 ELT(INTERVAL(s.TYPEVALUE, 111, 222,....), t1.AppleNAME,t2.OrangeName,....) 来适应灵活性。这种LEFT JOIN方式,一但在表多,或者记录多的时候会效率极差,但表少,并且记录少时则尚可。mysql> select s.*,if(s.TYPEVALUE=111,t1.AppleNAME,t2.OrangeName) as cn
-> from SubTable s left join MainTable1 t1 on s.TYPEID=t1.AppleID
-> left join MainTable2 t2 on s.TYPEID=t2.OrangeID;
+------------+-----------+--------+------+
| SubTableID | TYPEVALUE | TYPEID | cn |
+------------+-----------+--------+------+
| 1 | 111 | 1 | a1 |
| 2 | 111 | 2 | a2 |
| 3 | 222 | 1 | b1 |
| 4 | 222 | 2 | b2 |
+------------+-----------+--------+------+
4 rows in set (0.00 sec)mysql> select s.*,
-> ELT(INTERVAL(s.TYPEVALUE, 111, 222),
-> t1.AppleNAME,t2.OrangeName) as cn
-> from SubTable s left join MainTable1 t1 on s.TYPEID=t1.AppleID
-> left join MainTable2 t2 on s.TYPEID=t2.OrangeID;
+------------+-----------+--------+------+
| SubTableID | TYPEVALUE | TYPEID | cn |
+------------+-----------+--------+------+
| 1 | 111 | 1 | a1 |
| 2 | 111 | 2 | a2 |
| 3 | 222 | 1 | b1 |
| 4 | 222 | 2 | b2 |
+------------+-----------+--------+------+
4 rows in set (0.00 sec)mysql>mysql> explain select s.*,if(s.TYPEVALUE=111,t1.AppleNAME,t2.OrangeName) as cn
-> from SubTable s left join MainTable1 t1 on s.TYPEID=t1.AppleID
-> left join MainTable2 t2 on s.TYPEID=t2.OrangeID;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.s.TYPEID | 1 | |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.s.TYPEID | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
3 rows in set (0.00 sec)mysql>