列出与清洁工Betty负责相同的车辆的清洁工的名字,下面两个代码第一个是错的,多出了and c2.cno=b2.cno这一段,为啥结果不同了?自身插入嵌套(self join)不是应该有 cleaner c1,cleaner c2,bus b1, bus b2四个表一个接一个的建立关系的吗,期待热心的朋友帮忙解答一下!select c2.cname
from cleaner c1,cleaner c2,bus b1, bus b2
where c1.cname='Betty'
and b1.cno=c1.cno
and b2.tno=b1.tno
and c2.cno=b2.cno
And c2.cName<> 'Betty';
=====================================================
Select distinct c2.cName
From Cleaner c1, Cleaner c2, Bus b1, Bus b2
Where c1.cName= 'Betty'
And c1.cNo=b1.cNo
And b1.tNo=b2.tNo
And c2.cName<> 'Betty';--这里c2和b2的关系都没说,不是一般都要建立关系,“U”型才能传递值得吗?
from cleaner c1,cleaner c2,bus b1, bus b2
where c1.cname='Betty'
and b1.cno=c1.cno
and b2.tno=b1.tno
and c2.cno=b2.cno
And c2.cName<> 'Betty';
=====================================================
Select distinct c2.cName
From Cleaner c1, Cleaner c2, Bus b1, Bus b2
Where c1.cName= 'Betty'
And c1.cNo=b1.cNo
And b1.tNo=b2.tNo
And c2.cName<> 'Betty';--这里c2和b2的关系都没说,不是一般都要建立关系,“U”型才能传递值得吗?
------------
Bus |
------------|
reg_no(PK)|
model |
cNo(FK) |
tNo(FK) |
------------
========================
---------
Cleaner |
-------- |
cNo(PK)|
cName |
cSalary |
dNo(FK)|
---------
---------- --------------------
1 betty
2
3 jhone
4 tomSQL> select * from bus b; TNO CNO
---------- ----------
1 1
1 2
1 3
2 4SQL>
SQL> SELECT DISTINCT C2.*
2 FROM CLEANER C2, BUS B1, CLEANER C1, BUS B2
3 WHERE C2.CNAME <> 'betty'
4 AND C2.CNO = B2.CNO
5 AND B2.TNO = B1.TNO
6 AND B1.CNO = C1.CNO
7 AND C1.CNAME = 'betty'; CNO CNAME
---------- --------------------
2
3 jhone
自身插入,就像日本人一样啊,呵呵
SQL> Select distinct c2.*
2 From Cleaner c1, Cleaner c2, Bus b1, Bus b2
3 Where c1.cName= 'betty'
4 And c1.cNo=b1.cNo
5 And b1.tNo=b2.tNo
6 And c2.cName <> 'betty'; CNO CNAME
---------- --------------------
2
3 jhone
4 tom
能执行,但查询结果不是我们想要的数据