sql分页如何用exists替换not in？？？

[askquestion_004]

SELECT TOP 5 *
FROM table
WHERE (id NOT IN
          (SELECT TOP 10 id
         FROM table))

上面是段分页用的sql，如何用exists替换not in

[IEEE_China]

你这SQL语句什么也查不到

--

SELECT TOP 5 *
FROM table
WHERE  NOT exists
          (SELECT TOP 10 id
         FROM table)

[开着拖拉机泡妞]

go
if OBJECT_ID('test')is not null
drop table test
go
create table test(
id int identity(1,1),
value int
)
go
declare @b int
set @b=1
while @b<100
begin
insert test
select @b
set @b=@b+1
end


SELECT TOP 5 *
FROM test
WHERE (id NOT IN
          (SELECT TOP 15 id
         FROM test))
/*
id	value
16	16
17	17
18	18
19	19
20	20
         */
         
SELECT TOP 5 *
FROM test b
WHERE not exists(select 1 from(select top 15 id from test)a where b.id<=a.id)
/*
id	value
16	16
17	17
18	18
19	19
20	20
*/

[开着拖拉机泡妞]

[Quote=引用 1 楼 的回复:]

你这SQL语句什么也查不到

SQL code


--

SELECT TOP 5 *
FROM table
WHERE NOT exists
(SELECT TOP 10 id
FROM table)
[/Quote]

go
if OBJECT_ID('test')is not null
drop table test
go
create table test(
id int identity(1,1),
value int
)
go
declare @b int
set @b=1
while @b<100
begin
insert test
select @b
set @b=@b+1
end


SELECT TOP 5 *
FROM test
WHERE (id NOT IN
          (SELECT TOP 10 id
         FROM test))
/*
id	value
16	16
17	17
18	18
19	19
20	20
*/

--他的语句能实现，你是不是没测试数据啊

[IEEE_China]

回复 T，
哦，没注意，看错了。呵呵
没测试，上来就直接提交代码了

[askquestion_004]

SELECT TOP 5 *
FROM table
WHERE (id NOT IN
          (SELECT TOP 10 id
         FROM table))

SELECT TOP 5 *
FROM test b
WHERE not exists(select 1 from(select top 15 id from test)a where b.id<=a.id)

哪位能分析下效率不？？？

[开着拖拉机泡妞]

就你这两个语句差不多差不多。

[yukai08621]

select top * from table where id not in (select id from table )