INSERT into pubs..ls
SELECT TOP 6 MB001,MB002,MB003,MC007,MB004,MC014 AS 实际数量
FROM INVMB LEFT JOIN INVMC
ON MB001=MC001 WHERE ( MB001 LIKE '303%' OR MB001 LIKE '311%' OR MB001 LIKE '30910%'
OR MB001 LIKE '30913%' OR MB001 LIKE '30914%')
AND MB002 NOT LIKE '%J%' AND MC007 <>0 AND MC007 IS NOT NULL AND NOT EXISTS
(SELECT * FROM pubs..ls where MB001=pubs..ls.MB001) ORDER BY NEWID() 不明白问题如下:语句中pubs..ls为临时表
(1)当ls表中无数据时,我用not exists 的时候返回结构为6行数据,而用exists的时候可以返回0行
(2)当ls表中有数据时,我用not exists 的时候返回结构为0行数据,而用exists的时候可以返回6行
不是exists是存在的意思吗?我写的原意是当ls表中有的数据,就不查询出来
谁能帮我解释意思啊?
麻烦各位大侠了!!
SELECT TOP 6 MB001,MB002,MB003,MC007,MB004,MC014 AS 实际数量
FROM INVMB LEFT JOIN INVMC
ON MB001=MC001 WHERE ( MB001 LIKE '303%' OR MB001 LIKE '311%' OR MB001 LIKE '30910%'
OR MB001 LIKE '30913%' OR MB001 LIKE '30914%')
AND MB002 NOT LIKE '%J%' AND MC007 <>0 AND MC007 IS NOT NULL AND NOT EXISTS
(SELECT * FROM pubs..ls where MB001=pubs..ls.MB001) ORDER BY NEWID() 不明白问题如下:语句中pubs..ls为临时表
(1)当ls表中无数据时,我用not exists 的时候返回结构为6行数据,而用exists的时候可以返回0行
(2)当ls表中有数据时,我用not exists 的时候返回结构为0行数据,而用exists的时候可以返回6行
不是exists是存在的意思吗?我写的原意是当ls表中有的数据,就不查询出来
谁能帮我解释意思啊?
麻烦各位大侠了!!
not exists (sql 不返回结果集为真) 如下:
表A
ID NAME
1 A1
2 A2
3 A3 表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3 表A和表B是一对多的关系 A.ID --> B.AID SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 1)
-->SELECT * FROM B WHERE B.AID = 1有值返回真所以有数据 SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 2)
-->SELECT * FROM B WHERE B.AID = 2有值返回真所以有数据 SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 3)
-->SELECT * FROM B WHERE B.AID = 3无值返回真所以没有数据 NOT EXISTS 就是反过来
SELECT ID , NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
3 A3
not exists (sql 不返回结果集为真)
这里用not in