日期:2014-05-18  浏览次数:20478 次

sql分页如何用exists替换not in???
SQL code
SELECT TOP 5 *
FROM table
WHERE (id NOT IN
          (SELECT TOP 10 id
         FROM table))

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

------解决方案--------------------
SQL code

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
*/