怎样得到SQL返回结果的第N行?
比如SELECT name FROM person
WHERE name IS NOT NULL结果是:zhao
qian
sun
li
我想返回这个结果的第2行和第3行,即:qian
sun
比如SELECT name FROM person
WHERE name IS NOT NULL结果是:zhao
qian
sun
li
我想返回这个结果的第2行和第3行,即:qian
sun
WHERE name IS NOT NULLselect top 2 * from #t where id>1
WHERE name IS NOT NULLselect * from #t where [id] between 2 and 3
----------------------------
id>1的头两条
SELECT identity(int) id,name into #t FROM person
WHERE name IS NOT NULLselect top 2 * from #t where id>1要加一个自增字段上去~
select name into #t from person
WHERE name IS NOT NULL
--给临时表添加id
alter table #t add id identity(1,1)
declare @i int,@k int
--@i 返回的开始行,@k返回的结束行
declare @sql varchar(8000)set @i=20
set @k=30
SET @sql=isnull(@sql,'')+'select top '+cast(@k-@i as varchar(20))+' name from #t where id not in(select top '+cast(@i as varchar(20)) +' id from #t)'
print @sql
exec(@sql)
where name not in (select top 1 name from person)
SELECT top 3 name FROM person
WHERE name IS NOT NULL
order by name desc)
identity不支持?
declare @t table(A1 varchar(10),A2 int)
insert @t select 'a',1
union all select 'b',2
union all select 'c',3
union all select 'd',4
union all select 'f',5 select * from @t a where (select count(*) from @t where a2<=a.a2)>=2
and (select count(*) from @t where a2<=a.a2)<=3
select top 2 name from person where id in(select id from person
WHERE name IS NOT NULL) order by id desc
zhujian not in select top(n) from b
返回从n行开始的m条记录
select top m 列名
from 表
where 列名' not in
(select top n 列名'
from 表
order by 列名' )
列名和列名'可以相同,看你用需要返回的列排序还是其他列排序了
一、直接新建个临时表
二、采用top,然后用not in去出重复的方法
不经常使用的话,用select top 上面举的方法挺好.
临时表也不错(数据量不大的话).