--用一句SQL取出第 m 条到第 n 条记录的方法 1 --从Table 表中取出第 m 条到第 n 条的记录:(Not In 版本) select * from Item select top n-m+1 * from TABLE where (id NOT IN (select top m-1 id FROM TABLE ))2--从TABLE表中取出第m到n条记录 (Exists版本)SELECT TOP n-m+1 * FROM TABLE AS a WHERE Not Exists (Select * From (Select Top m-1 * From TABLE order by id) b Where b.id=a.id ) Order by id3--m为上标,n为下标,例如取出第8到12条记录,m=8,n=12,Table为表名 这是最好用的一条! Select Top n-m+1 * From Table Where Id>(Select Max(Id) From (Select Top m-1 Id From Table Order By Id Asc) Temp) Order By Id Asc
sql server的随机函数newID()和RAND()
SELECT * FROM Northwind..Orders ORDER BY NEWID()
--随机排序
SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID()
--从Orders表中随机取出10条记录
MsSql随机取数据 select top 10 * from c_cy_admin order by newid() Access 随机取数据 select SELECT top 10 id FROM 表1 order by rand(id) mySql 随机取数据 SELECT id FROM gueze order by rand() limit 10
用潜逃子查询,使用ROW_NUMBER() OVER()吧
但是这在sql 2000里面就没法用了!
如果是2005使用 ROW_NUMBER() OVER()的嵌套子查询,如果是2000就是用top
C# 面试 130题中就有这种类型的题目。支持mxc1225
declare @a int set @a=cast(rand()*5+3 as int) exec('select top '+@a+' * from table order by newid()')3-7条,随机
select * from Item select top n-m+1 * from TABLE where (id NOT IN (select top m-1 id FROM TABLE ))SELECT TOP n-m+1 * FROM TABLE AS a WHERE Not Exists (Select * From (Select Top m-1 * From TABLE order by id) b Where b.id=a.id ) Order by idSelect Top n-m+1 * From Table Where Id>(Select Max(Id) From (Select Top m-1 Id From Table Order By Id Asc) Temp) Order By Id Asc 这三条语句在我这里怎么都报错? 都报的FROM关键字附近有语法错误?
declare @a int set @a=cast(rand()*5+3 as int) exec('select top '+@a+' * from table order by newid()')这条语句取的数据不是我要的。我要的是通过ID的 我刚说的是3到7条的语句,就是说ID为3到7的语句,同时取出来的数据,ID不能是3,4,5,6,7连续的,要是乱序的。
原表数据 m,n 1,af 13,dfa 3,43ad 4,43d1 6,ds 7,dfd 8,33ssdcreate table #temp ( id int identity(1,1), m int, n varchar(20) )insert into #temp select * from a select m,n from #temp where id>=3 and id<=7
select top 5 * from A where id not in (select top 5 id from A) 或 select top 5 * from A where id > (select max(id) from (select top 5 id from A )as A)
你是什么数据库?select top 5 * from A where id not in (select top 2 id from A)
1 --从Table 表中取出第 m 条到第 n 条的记录:(Not In 版本)
select * from Item
select top n-m+1 * from TABLE where (id NOT IN (select top m-1 id FROM TABLE ))2--从TABLE表中取出第m到n条记录 (Exists版本)SELECT TOP n-m+1 * FROM TABLE AS a WHERE Not Exists
(Select * From (Select Top m-1 * From TABLE order by id) b Where b.id=a.id )
Order by id3--m为上标,n为下标,例如取出第8到12条记录,m=8,n=12,Table为表名
这是最好用的一条!
Select Top n-m+1 * From Table Where Id>(Select Max(Id) From
(Select Top m-1 Id From Table Order By Id Asc) Temp) Order By Id Asc
SELECT * FROM Northwind..Orders ORDER BY NEWID()
--随机排序
SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID()
--从Orders表中随机取出10条记录
MsSql随机取数据 select top 10 * from c_cy_admin order by newid()
Access 随机取数据 select SELECT top 10 id FROM 表1 order by rand(id)
mySql 随机取数据 SELECT id FROM gueze order by rand() limit 10
set @a=cast(rand()*5+3 as int)
exec('select top '+@a+' * from table order by newid()')3-7条,随机
select top n-m+1 * from TABLE where (id NOT IN (select top m-1 id FROM TABLE ))SELECT TOP n-m+1 * FROM TABLE AS a WHERE Not Exists
(Select * From (Select Top m-1 * From TABLE order by id) b Where b.id=a.id )
Order by idSelect Top n-m+1 * From Table Where Id>(Select Max(Id) From
(Select Top m-1 Id From Table Order By Id Asc) Temp) Order By Id Asc 这三条语句在我这里怎么都报错? 都报的FROM关键字附近有语法错误?
set @a=cast(rand()*5+3 as int)
exec('select top '+@a+' * from table order by newid()')这条语句取的数据不是我要的。我要的是通过ID的
我刚说的是3到7条的语句,就是说ID为3到7的语句,同时取出来的数据,ID不能是3,4,5,6,7连续的,要是乱序的。
m,n
1,af
13,dfa
3,43ad
4,43d1
6,ds
7,dfd
8,33ssdcreate table #temp
(
id int identity(1,1),
m int,
n varchar(20)
)insert into #temp select * from a
select m,n from #temp where id>=3 and id<=7
或
select top 5 * from A where id > (select max(id) from (select top 5 id from A )as A)