3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
...全文
请发表友善的回复…
发表回复
JL99000 2007-12-25
实现方法很多
cxmcxm 2007-12-24
select top 4 * from (select top 20 * from 表 order by 要排序的字段) a order by 要排序的字段 desc
hui_hui_2007 2007-12-24
如果有主键,可以使用8楼的方法,如果没有主键,就要生成一个自增列了.
hui_hui_2007 2007-12-24
取第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 行）
*/
msi586 2007-12-24
select top 20 * from T_User where id not in (select top 15 id from T_User)
areswang 2007-12-24
比方说？例如？
-狙击手- 2007-12-24
select identity(int,1,1) as id ,* into # from table
select * from # where id between 16 and 20
drop table #
select * from # where id between 16 and 20
drop table #
十一月猪 2007-12-24
你的 16~20 是按照什么标准来的 ， 这个肯定要确认下来 ，不然怎么做呢？
dawugui 2007-12-24
有一表T_User 我要选第16-20条的数据 不是id哦 是选index 怎么选取呢 谢谢大家。
select px = identity(int,1,1) , * into tmp from tb
select * from tmp where px between 16 and 20
select px = identity(int,1,1) , * into tmp from tb
select * from tmp where px between 16 and 20
zghua851004 2007-12-24
楼主列出来一些数据吧
dawugui 2007-12-24
取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 desc
3.
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
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 desc
3.
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