请教两个SQL语句～～～

两个多对多关系的表：employer与employee  还有一个中间连接表：employment

mysql> 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;

15 个解决方案

#1

UP

#2

UP

#3

UP

#4

UP

#5

UP

#6

UP

#7

UP

#8

汗～～终于正常了，我的帖子终于能显示出来了

#9

运行结果：
http://bbs.v007.net/UploadFile/2007-3/200731320464858115.txt

#10

up

#11

UP

#12

呵呵，我制造了一个一摸一样的，但是我的显示的结果是一样的呀。

mysql> select * from (select er.employerid as employer_id,er.name,et.* from empl
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)


这里，我把时间省掉了。

#13

哇，神奇了～～～！！！！！！！！
朋友用的是MySQL几点零？

我的是MySQL4.1
这下晕了，怎么回事啊，难道是我机器出了问题，我测试过好多次了，我可以担保我表结构和语句都和你一样的啊，晕了@_@

#14

不过怎样也谢谢朋友的回贴哦，再等几下。如果没有回复我结贴了，因为那个怪异的结果集我也可以看出一定的规律来了，心结基本解开

#15

whalefish2001用的是几点零版本的MySQL？