请教高手，为什么两句语句执行后结果是一样的？
1:select num from a where number<>'123456' and num not in("selcect num from b where number='123456'")
2:select num from a where number<>'123456'
3:select num from a where num not in("selcect num from b where number='123456'")
为什么这三句执行后是一样的结果，但是b里确实有和a一样结果存在．
比如：a的结果：106,107,108
b的结果; 106,108
那么是不是返回值应该是：107
问题点数：20、回复次数：9Top
1 楼shuixin13（犬犬（心帆））回复于 2003-05-03 10:36:34 得分 0
A,B 表的数据呢??Top
2 楼zjxscj（zjxscj）回复于 2003-05-03 11:13:48 得分 0
不是写着了吗？
a的结果：123456 106,1234567 107,123458 108
b的结果; 123456 106,123456 108
Top
3 楼swotcoder（苦 丁 www.help2u.org）回复于 2003-05-03 11:30:40 得分 0
问你里边放了些什么Top
4 楼zjxscj（zjxscj）回复于 2003-05-03 11:48:18 得分 0
A表内的内容：123456 106,1234567 107,123458 108
B表内的内容： 123456 106,123456 108
是这个啊！！
Top
5 楼shuixin13（犬犬（心帆））回复于 2003-05-03 12:07:34 得分 0
+----------+------+
| number | num |
+----------+------+
| 123456 | 106 |
| 1234567 | 107 |
| 123458 | 108 |
+----------+------+
+----------+------+
| number | num |
+----------+------+
| 123456 | 106 |
| 123456 | 108 |
+----------+------+
selcect num from b where number='123456'
+------+
| num |
+------+
| 106 |
| 108 |
+------+
tA.num not in("selcect num from b where number='123456'")
+------+
| num |
+------+
| 107 |
+------+Top
6 楼zjxscj（zjxscj）回复于 2003-05-03 13:30:29 得分 0
select num from a where a.num not in("select num from b where number='123456'")
我是这样写的，问题是结果是;106,107,108
这是为什么？Top
7 楼shuixin13（犬犬（心帆））回复于 2003-05-03 15:03:25 得分 0
呵呵,
问题比较奇怪,
你将你的数据导出来,
帖出来,我在我这给你试一下吧Top
8 楼zjxscj（zjxscj）回复于 2003-05-03 15:15:00 得分 0
table a
14,"guest","13357155121","108","18","女",2003-5-2 0:00:00
13,"小白","13357155120","107","18","女",2003-5-2 0:00:00
12,"啊骏","13357155122","106","28","男",2003-5-2 0:00:00
字段为：id name(text) number(text) num(text) age(text) sex(text) date
table b
1,"13357155122","106"
2,"13357155120","107"
3,"13357155121","108"
4,"13357155122","108"
自段为：id number(text) num(text)
谢谢了！Top
9 楼shuixin13（犬犬（心帆））回复于 2003-05-03 16:52:21 得分 20
CREATE TABLE `ta` (
`id` tinyint(3) unsigned NOT NULL default '0',
`name` varchar(10) default NULL,
`number` varchar(20) default NULL,
`num` char(3) default '0',
`age` char(3) default NULL,
`sex` char(2) default NULL,
`date` datetime default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `number` (`number`),
KEY `num` (`num`)
) TYPE=MyISAM;
INSERT INTO `ta` (`id`, `name`, `number`, `num`, `age`, `sex`, `date`)
VALUES
("12", "啊骏", "13357155122", "106", "28", "男", "2003-05-02 00:00:00"),
("13", "小白", "13357155120", "107", "18", "女", "2003-05-02 00:00:00"),
("14", "guest", "13357155121", "108", "18", "女", "2003-05-02 00:00:00");
CREATE TABLE `tb` (
`id` tinyint(3) unsigned NOT NULL default '0',
`number` varchar(20) NOT NULL default '',
`num` char(3) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `num` (`num`),
KEY `number` (`number`)
) TYPE=MyISAM CHARSET=latin1;
INSERT INTO `tb` (`id`, `number`, `num`)
VALUES
("1", "13357155122", "106"),
("2", "13357155120", "107"),
("3", "13357155121", "108"),
("4", "13357155122", "108");
mysql> SELECT `num` FROM `tB` WHERE `number` ='13357155122';
+-----+
| num |
+-----+
| 106 |
| 108 |
+-----+
2 rows in set (0.00 sec)
mysql> SELECT * FROM `tA` WHERE `num` NOT IN (SELECT `num` FROM `tB` WHERE `numb
er` ='13357155122');
+----+------+-------------+------+------+------+---------------------+
| id | name | number | num | age | sex | date |
+----+------+-------------+------+------+------+---------------------+
| 13 | 小白 | 13357155120 | 107 | 18 | 女 | 2003-05-02 00:00:00 |
+----+------+-------------+------+------+------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `tA` WHERE `number` <>'13357155122' AND `num` NOT IN (SELEC
T `num` FROM `tB` WHERE `number` ='13357155122');
+----+------+-------------+------+------+------+---------------------+
| id | name | number | num | age | sex | date |
+----+------+-------------+------+------+------+---------------------+
| 13 | 小白 | 13357155120 | 107 | 18 | 女 | 2003-05-02 00:00:00 |
+----+------+-------------+------+------+------+---------------------+
1 row in set (0.00 sec)
不知你要的是什么结果????Top
相关问题
- 这两个SQL语句执行的结果为什么不一样？
- SQL语句的执行结果问题
- 两段sql语句分别执行后的结果集和通过union结合后执行产生的记录数不一样，请问是什么原因？
- 怎样做一个像sql server的查询分析器一样的程序，就是输入sql语句，然后执行返回结果？
- 怎样做一个像sql server的查询分析器一样的程序，就是输入sql语句，然后执行返回结果？
- 奇怪！相同数据库，相同语句，不同机器执行结果不一样？人品问题？帮我看看。
- 怎样把sql语句的执行结果放到变量里?
- 如果得到语句执行是否成功的结果？
- 使用union时，语句1和语句2中两句执行时对数据库的影响一样吗？
- 执行SELECT语句后能否获得其返回结果的行数
