系统环境
OS(操作系统): winxp
DB(数据库版本): MSSQL20052008
SP(数据库补丁):SP1如下四张表,依次名为S,P,J,SPJ
求没有使用北京供应商生产的红色零件的工程号JNO我的两种想法:1.
Select Distinct Jno From SPJ
where Jno not in
(
Select Jno From Spj,P,S where P.Color='红' and S.City='北京'
and Spj.Pno=P.Pno and Spj.Sno=S.Sno
)/*正确解法*/得出的结果是J2,J3,J4,这是正确的2.这种想法是想体验一下自身连接有什么用,什么时候该用什么不该用Select Distinct Jno From SPJ S1
where Jno not in
(
Select Jno From Spj S2,P,S where P.Color='红' and S.City='北京'
and S2.Pno=P.Pno and S2.Sno=S.Sno AND S2.Sno=S1.Sno
)\*有问题的解法*\得出的结果却是J1,J2,J3,J4将方法二改一下,去掉not
Select Distinct Jno From SPJ S1
where Jno in
(
Select Jno From Spj S2,P,S where P.Color='红' and S.City='北京'
and S2.Pno=P.Pno and S2.Sno=S.Sno AND S2.Sno=S1.Sno
)
结果是J1最后的结果是我逻辑混乱了~有人能讲一下自身连接么
OS(操作系统): winxp
DB(数据库版本): MSSQL20052008
SP(数据库补丁):SP1如下四张表,依次名为S,P,J,SPJ
求没有使用北京供应商生产的红色零件的工程号JNO我的两种想法:1.
Select Distinct Jno From SPJ
where Jno not in
(
Select Jno From Spj,P,S where P.Color='红' and S.City='北京'
and Spj.Pno=P.Pno and Spj.Sno=S.Sno
)/*正确解法*/得出的结果是J2,J3,J4,这是正确的2.这种想法是想体验一下自身连接有什么用,什么时候该用什么不该用Select Distinct Jno From SPJ S1
where Jno not in
(
Select Jno From Spj S2,P,S where P.Color='红' and S.City='北京'
and S2.Pno=P.Pno and S2.Sno=S.Sno AND S2.Sno=S1.Sno
)\*有问题的解法*\得出的结果却是J1,J2,J3,J4将方法二改一下,去掉not
Select Distinct Jno From SPJ S1
where Jno in
(
Select Jno From Spj S2,P,S where P.Color='红' and S.City='北京'
and S2.Pno=P.Pno and S2.Sno=S.Sno AND S2.Sno=S1.Sno
)
结果是J1最后的结果是我逻辑混乱了~有人能讲一下自身连接么
(
select distinct j.jno from j , spj , s , p
where j.jno = spj.jno and spj.sno = s.sno and spj.pno = p.pno and j.city = '北京' and p.color = '红'
)