有没有类似oracle中的下面这样的语法: select f1,f2,rownum from t where rownum<20 minus (select f1,f2,rownum from t where rownum<10)这样的语句在sql server 中不报错,但只执行minus前面的语句.
看看你的表可不可以用兩個列合並作為關鍵字如得到 21-30 記錄select top 10 from yourTable where a+b not in (select top a+b 20 from yourTable)
select top 10 from yourTable where a+b not in (select top 20 a+b from yourTable)
not in 操作效率很差呀,能不能不用。
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表 select * from #temp where ID_Num>10 and ID_Num<=20 --- SELECT * FROM T t1 WHERE hits IN (SELECT TOP 10 hits FROM T WHERE datediff(month,dates,t1.dates)=0 ORDER BY hits desc ) ORDER BY year(t1.dates) DESC ,month(t1.dates) desc, t1.hits DESC
SELECT Top @页大小 * FROM T WHERE SortField NOT IN (SELECT TOP @页大小 * @第几页 SortField FROM T ORDER BY SortField ) ORDER BY SortField
刚刚做了一个试验,根本不对,你可以自己在一个表中插入100 条记录试验一下:select top 90 * from table where a+b not in (select top 100 a+b from table) 结果根本不是期望的90-100之间的纪录。
pengdali(大力) 能否解释一下。
大家的方案能不能不排序,如果100万条记录的数据库执行order by 会等死人的。
使用存储过程分页,效率最高的一种写法CREATE PROCEDURE TheorySearch ( @keyword nvarchar(100), @int_pagenow int=0, @int_pagesize int=0, @int_recordcount int=0 output )AS set nocount ondeclare @int_allid int declare @int_beginid int,@int_endid int declare @int_pagebegin int, @int_pageend intselect @int_allid=count(*) from 医学理论 where (题目 like @keyword or 关键词 like @keyword) select @int_recordcount=@int_allidselect @int_beginid=(@int_pagenow-1)*@int_pagesize+1 select @int_endid = @int_beginid+@int_pagesize-1 select identity(int,1,1) as id1 ,id+0 as id into #temp from 医学理论 where 题目 like @keyword or 关键词 like @keyword order by id desc--select @int_recordcount=@@rowcount select 题目,文件名,书名,所属章节 from 医学理论 a INNER JOIN #temp b on a.id=b.id and b.id1 between @int_beginid and @int_endid drop table #temp set nocount off return GO
事实证明用三条SQL语句效率最高:1. select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表 2. select * from #temp where ID_Num >10 and ID_Num <=20 3. DROP TABLE #temp
http://expert.csdn.net/Expert/topic/1299/1299921.xml?temp=.5284235
select f1,f2,rownum from t where rownum<20 minus
(select f1,f2,rownum from t where rownum<10)这样的语句在sql server 中不报错,但只执行minus前面的语句.
where a+b not in
(select top a+b 20 from yourTable)
where a+b not in
(select top 20 a+b from yourTable)
select * from #temp where ID_Num>10 and ID_Num<=20
---
SELECT *
FROM T t1
WHERE hits IN (SELECT TOP 10 hits
FROM T
WHERE datediff(month,dates,t1.dates)=0
ORDER BY hits desc
)
ORDER BY year(t1.dates) DESC ,month(t1.dates) desc, t1.hits DESC
FROM T
WHERE SortField NOT IN (SELECT TOP @页大小 * @第几页 SortField
FROM T
ORDER BY SortField
)
ORDER BY SortField
条记录试验一下:select top 90 * from table where a+b not in
(select top 100 a+b from table)
结果根本不是期望的90-100之间的纪录。
(
@keyword nvarchar(100),
@int_pagenow int=0,
@int_pagesize int=0,
@int_recordcount int=0 output
)AS
set nocount ondeclare @int_allid int
declare @int_beginid int,@int_endid int
declare @int_pagebegin int, @int_pageend intselect @int_allid=count(*) from 医学理论 where (题目 like @keyword or 关键词 like @keyword)
select @int_recordcount=@int_allidselect @int_beginid=(@int_pagenow-1)*@int_pagesize+1
select @int_endid = @int_beginid+@int_pagesize-1 select identity(int,1,1) as id1 ,id+0 as id into #temp from 医学理论 where 题目 like @keyword or 关键词 like @keyword order by id desc--select @int_recordcount=@@rowcount
select 题目,文件名,书名,所属章节 from 医学理论 a INNER JOIN #temp b on a.id=b.id and b.id1 between @int_beginid and @int_endid
drop table #temp
set nocount off
return
GO
2. select * from #temp where ID_Num >10 and ID_Num <=20
3. DROP TABLE #temp