取n到m条记录的语句1. select top m * from tablename where id not in (select top n id from tablename)2. select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入 set rowcount n select * from 表变量 order by columnname desc3. select top n * from (select top m * from tablename order by columnname) a order by columnname desc 4.如果tablename里没有其他identity列,那么: select identity(int) id0,* into #temp from tablename取n到m条的语句为: select * from #temp where id0 >=n and id0 <= m如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行: exec sp_dboption 你的DB名字,'select into/bulkcopy',true 5.如果表里有identity属性,那么简单: select * from tablename where identitycol between n and m
有一表T_User 我要选第16-20条的数据 不是id哦 是选index 怎么选取呢 谢谢大家。
select px = identity(int,1,1) , * into tmp from tbselect * from tmp where px between 16 and 20
select identity(int,1,1) as id ,* into # from tableselect * from # where id between 16 and 20drop table #
select top 20 * from T_User where id not in (select top 15 id from T_User)
取第7到第12条记录的方法--注:必须要有惟一字段,这里假定col1是惟一字段。 declare @tb table ( col1 varchar(3), col2 varchar(10) )insert into @tb select 'b1','aaa' union all select 'm2','bbbb' union all select 'y3','cccc' union all select 'c4','dddd' union all select 'r5','eeee' union all select 'u6','ffff' union all select 'h7','gggg' union all select 'n8','hhhh' union all select 'v9','iiii' union all select 'y10','jjjj' union all select 'c11','kkkk' union all select 'a12','llll' union all select 'z13','mmmm' union all select 'p14','nnnn' union all select 't15','oooo' union all select 'm16','pppp'select * from @tb order by col1--7-12 --(12-7)+1为6,所以为: top 6 * select top 6 * from @tb where col1 not in (select top 6 col1 from @tb) /* col1 col2 ---- ---------- h7 gggg n8 hhhh v9 iiii y10 jjjj c11 kkkk a12 llll(所影响的行数为 6 行) */
select top 4 * from (select top 20 * from 表 order by 要排序的字段) a order by 要排序的字段 desc
select top m * from tablename where id not in (select top n id from tablename)2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列,那么:
select identity(int) id0,* into #temp from tablename取n到m条的语句为:
select * from #temp where id0 >=n and id0 <= m如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字,'select into/bulkcopy',true
5.如果表里有identity属性,那么简单:
select * from tablename where identitycol between n and m
select px = identity(int,1,1) , * into tmp from tbselect * from tmp where px between 16 and 20
取第7到第12条记录的方法--注:必须要有惟一字段,这里假定col1是惟一字段。
declare @tb table (
col1 varchar(3),
col2 varchar(10)
)insert into @tb
select 'b1','aaa' union all
select 'm2','bbbb' union all
select 'y3','cccc' union all
select 'c4','dddd' union all
select 'r5','eeee' union all
select 'u6','ffff' union all
select 'h7','gggg' union all
select 'n8','hhhh' union all
select 'v9','iiii' union all
select 'y10','jjjj' union all
select 'c11','kkkk' union all
select 'a12','llll' union all
select 'z13','mmmm' union all
select 'p14','nnnn' union all
select 't15','oooo' union all
select 'm16','pppp'select *
from @tb
order by col1--7-12
--(12-7)+1为6,所以为: top 6 *
select top 6 *
from @tb
where col1 not in (select top 6 col1 from @tb)
/*
col1 col2
---- ----------
h7 gggg
n8 hhhh
v9 iiii
y10 jjjj
c11 kkkk
a12 llll(所影响的行数为 6 行)
*/