两个多对多关系的表:employer与employee 还有一个中间连接表:employmentmysql> desc employer;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| employerid | int(11) | | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+mysql> desc employment;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| employmentid | int(11) | | PRI | NULL | auto_increment |
| startDate | datetime | YES | | NULL | |
| endDate | datetime | YES | | NULL | |
| employerid | int(11) | | MUL | 0 | |
| employeeid | int(11) | | MUL | 0 | |
+--------------+----------+------+-----+---------+----------------+mysql> desc employee;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| employeeid | int(11) | | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| taxfileNumber | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+为何执行以下两句查询之后的查询结果却大相径庭?望大家详细指点下!谢谢!
select * from employer er left join employment et on er.employerid = et.employerid right join employee ee on et.employeeid = ee.employeeid;select * from (select * from employer er left join employment et on er.employerid = et.employerid) ss right join employee ee on ss.employeeid = ee.employeeid;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| employerid | int(11) | | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+mysql> desc employment;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| employmentid | int(11) | | PRI | NULL | auto_increment |
| startDate | datetime | YES | | NULL | |
| endDate | datetime | YES | | NULL | |
| employerid | int(11) | | MUL | 0 | |
| employeeid | int(11) | | MUL | 0 | |
+--------------+----------+------+-----+---------+----------------+mysql> desc employee;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| employeeid | int(11) | | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| taxfileNumber | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+为何执行以下两句查询之后的查询结果却大相径庭?望大家详细指点下!谢谢!
select * from employer er left join employment et on er.employerid = et.employerid right join employee ee on et.employeeid = ee.employeeid;select * from (select * from employer er left join employment et on er.employerid = et.employerid) ss right join employee ee on ss.employeeid = ee.employeeid;
http://bbs.v007.net/UploadFile/2007-3/200731320464858115.txt
oyer er left join employment et on er.employerid = et.employerid) ss right join
employee ee on ss.employeeid = ee.employeeid;
+-------------+------+--------------+------------+------------+------------+----
--+---------------+
| employer_id | name | employmentid | employerid | employeeid | employeeid | nam
e | taxfilenumber |
+-------------+------+--------------+------------+------------+------------+----
--+---------------+
| NULL | NULL | NULL | NULL | NULL | 1 | eea
| eeafff |
| 1 | era | 5 | 1 | 2 | 2 | eeb
| eebfff |
| 1 | era | 2 | 1 | 3 | 3 | eec
| eecfff |
| 2 | erb | 4 | 2 | 3 | 3 | eec
| eecfff |
| 3 | erc | 3 | 3 | 3 | 3 | eec
| eecfff |
| 1 | era | 1 | 1 | 4 | 4 | eed
| eedfff |
| NULL | NULL | NULL | NULL | NULL | 5 | eef
| eeffff |
+-------------+------+--------------+------------+------------+------------+----
--+---------------+
7 rows in set (0.00 sec)mysql> select * from employer er left join employment et on er.employerid = et.e
mployerid right join employee ee on et.employeeid = ee.employeeid;
+------------+------+--------------+------------+------------+------------+-----
-+---------------+
| employerid | name | employmentid | employerid | employeeid | employeeid | name
| taxfilenumber |
+------------+------+--------------+------------+------------+------------+-----
-+---------------+
| NULL | NULL | NULL | NULL | NULL | 1 | eea
| eeafff |
| 1 | era | 5 | 1 | 2 | 2 | eeb
| eebfff |
| 1 | era | 2 | 1 | 3 | 3 | eec
| eecfff |
| 3 | erc | 3 | 3 | 3 | 3 | eec
| eecfff |
| 2 | erb | 4 | 2 | 3 | 3 | eec
| eecfff |
| 1 | era | 1 | 1 | 4 | 4 | eed
| eedfff |
| NULL | NULL | NULL | NULL | NULL | 5 | eef
| eeffff |
+------------+------+--------------+------------+------------+------------+-----
-+---------------+
7 rows in set (0.00 sec)
这里,我把时间省掉了。
朋友用的是MySQL几点零?我的是MySQL4.1
这下晕了,怎么回事啊,难道是我机器出了问题,我测试过好多次了,我可以担保我表结构和语句都和你一样的啊,晕了@_@