一简单的SQL语句查询问题
S表表示供应商
P表是零件表
J表是工程表
SPJ表是他们之间的相互关系表
S表如下
+-----+--------+--------+------+
| SNO | SNAME | STATUS | CITY |
+-----+--------+--------+------+
| S1 | 精益 | 20 | 天津 |
| S2 | 盛锡 | 10 | 北京 |
| S3 | 东方红 | 30 | 北京 |
| S4 | 丰泰盛 | 20 | 天津 |
| S5 | 为民 | 30 | 上海 |
+-----+--------+--------+------+
SPJ表如下
+------+------+------+------+
| SNO | PNO | JNO | QTY |
+------+------+------+------+
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | J4 | 700 |
| S1 | P2 | J2 | 100 |
| S2 | P3 | J1 | 400 |
| S2 | P3 | J2 | 200 |
| S2 | P3 | J4 | 500 |
| S2 | P3 | J5 | 400 |
| S2 | P5 | J1 | 400 |
| S2 | P5 | J2 | 100 |
| S3 | P1 | J1 | 200 |
| S3 | P3 | J1 | 200 |
| S4 | P5 | J1 | 100 |
| S4 | P6 | J3 | 300 |
| S4 | P6 | J4 | 200 |
| S5 | P2 | J4 | 100 |
| S5 | P3 | J1 | 200 |
| S5 | P6 | J2 | 200 |
| S5 | P6 | J4 | 500 |
+------+------+------+------+
现在要求没有使用天津生产的零件的工程号码!
用这个语句,
select distinct jno from spj,s where spj.sno=s.sno and s.city = '天津 ';
可以求出使用了天津产的零件的工程号码:
+------+
| jno |
+------+
| J1 |
| J3 |
| J4 |
| J2 |
+------+
那么为什么
select distinct jno from spj,s where spj.sno=s.sno and s.city != '天津 ';
的结果为
+------+
| jno |
+------+
| J1 |
| J2 |
| J4 |
| J5 |
+------+
为什么这条语句求出来的不是 没有使用天津零件的工程号码呢?
[解决办法]
select distinct jno SPJ.SNO from spj,s where spj.sno=s.sno and s.city != '天津 ';
你把语句改成这样你就看出区别了
