select top 50 * from ( select top 150 * from tb order by a desc ) as t order by a asc
select top 50 * from T where a not In ( select top 100 a from t order by a desc )
select top 50 * from T where a not In ( select top 100 a from t order by a desc ) order by a desc
在数据库查询的时候,我们有时有这样的需求,就是要找出数据表里指定范围行内的数据记录,比如说要找出数据表里第10行到第20行的这10条数据,那么我们怎么来实现呢? 按照通常的方法是实现不了的,我们得借助于临时表以及一个函数来实现代码如下:Select no=Identity(int,1,1),* Into #temptable From dbo.teacher_info order by teacher_name--利用Identity函数生成记录序号Select * From #temptable Where no>=10 And no < 20Drop Table #temptable--用完后删除临时表 取n到m条记录的语句1.select top m * from tablename where id not in ( select top n * from tablename) 2.select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入set rowcount nselect * from 表变量order by columnname desc 3.select top n * from( select top m * from tablename order by columnname) aorder 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 top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)
如果表中无主键,可以用临时表,加标识字段解决.这里的x,y可以用变量.
select id=identity(int,1,1),* into #tb from 表 select * from #tb where id between (x-1)*y and x*y-1
select top 50 * from (select top 150 * from tb order by a) aa order by a desc
SELECT TOP 50 * FROM table1 WHERE id NOT IN ( SELECT TOP 100 id FROM table1 ORDER BY id DESC) ORDER BY table1 id DESC 只知道有两种方法。这是我知道的其中一种
SQL SERVER 没有Limit语法
select top(@x+50) * from tb where a not in(select top(x) a from tb )
select top(@x+50) * from tb where a not in(select top(@x) a from tb )
sql 2005,sql 2008 USE AdventureWorks; GODECLARE @number INT SET @number = 10; SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID DESC) AS ID ,* FROM HumanResources.Employee)X WHERE ID BETWEEN @number AND @number + 50;
select top 150 * from tb order by a desc
) as t
order by a asc
from T
where a not In ( select top 100 a from t order by a desc )
from T
where a not In ( select top 100 a from t order by a desc )
order by a desc
按照通常的方法是实现不了的,我们得借助于临时表以及一个函数来实现代码如下:Select no=Identity(int,1,1),* Into #temptable From dbo.teacher_info order by teacher_name--利用Identity函数生成记录序号Select * From #temptable Where no>=10 And no < 20Drop Table #temptable--用完后删除临时表
取n到m条记录的语句1.select top m * from tablename where id not in ( select top n * from tablename)
2.select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入set rowcount nselect * from 表变量order by columnname desc
3.select top n * from( select top m * from tablename order by columnname) aorder 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最基本的处理方法:
如果表中有主键(记录不重复的字段也可以),可以用类似下面的方法,当然x,y要换成具体的数字,不能用变量:
select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)
如果表中无主键,可以用临时表,加标识字段解决.这里的x,y可以用变量.
select id=identity(int,1,1),* into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1
select top(@x+50) *
from tb
where a not in(select top(x) a from tb )
select top(@x+50) *
from tb
where a not in(select top(@x) a from tb )
USE AdventureWorks;
GODECLARE @number INT
SET @number = 10;
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID DESC) AS ID
,*
FROM HumanResources.Employee)X
WHERE ID BETWEEN @number AND @number + 50;