select top 1 id,name,pp,qq from test where id not in (select top 2 id from test where id>100 order by id)上面这个语句,在MSSQL2000下是肯定能成功的,select top 2 id from test where id>100 order by id结果是表中id号大于100的数据中的头两行。再结合前面select top 1 id,name,pp,qq from test where id not in 能查到第三行的数据,但是在mssql2005中,始终只返回表中id号为1的那一行。是不是在mssql2005中不能用not in作为排除条件来查询??
测试结果如下:
2000中:
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-08 06:33:32
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT,value VARCHAR(3))
INSERT INTO [tb]
SELECT 1,'www' UNION ALL
SELECT 2,'dd' UNION ALL
SELECT 101,'ddd' UNION ALL
SELECT 102,'dfd' UNION ALL
SELECT 103,'dfd' union all
select 104,'dsf'select * from [tb]select top 1 * from tb where id not in (select top 2 id from tb where id>100 order by id)
id value
1 www
2005中:---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-08 06:33:32
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT,value VARCHAR(3))
INSERT INTO [tb]
SELECT 1,'www' UNION ALL
SELECT 2,'dd' UNION ALL
SELECT 101,'ddd' UNION ALL
SELECT 102,'dfd' UNION ALL
SELECT 103,'dfd'select * from [tb]select top 1 * from tb where id not in (select top 2 id from tb where id>100 order by id) id value
1 www
select top 1 id,name,pp,qq
from test
where id not in (select top 2 id from test where id>100 order by id)
and id>100
"的理解有误,有可能是受分页存储过程的影响
把 order by id desc 放到外面一层的select 试试.
from test
where id not in (select top 2 id from test where id>100 order by id)
--以上条件只是排除了id为101 和102 ,剩下的记录是id 1-100 103-。
要想取103.。。
加条件
and id>100select top 1 id,name,pp,qq
from test
where id not in (select top 2 id from test where id>100 order by id)
and id>100