获取指定范围行的sql语句
复制代码 代码如下:
//获取31-40行
(1)select top 10 * from A where Id not in( select top 30 Id from A order by Id asc ) order by id asc;
(2)select top 10 * from A where Id > ( select max( id ) from ( select top 30 id from A order by id asc ) temp ) order by id asc;
(3)select top 10 * from A tt where not exists( select * from ( select top 30 * from A order by ID asc ) temp where temp.Id = tt.Id );
获取指定范围行的存储过程
复制代码 代码如下:
//存储过程
CREATE PROCEDURE PageRead(@pageIndex int,@statisticType int) AS
begin
if @statisticType = 1
begin
select id=identity(int,1,1),tbp.name,tbp.description,AVG(tbs.ReadTime) AS avgTime into #avgTable
from tb_statistic tbs,tb_project tbp Where tbp.id = tbs.cdId Group By tbp.name,tbp.description
select * From #avgTable Where id between @pageIndex and @pageIndex + 9
end
else if @statisticType = 2
begin
select id=identity(int,1,1),tbp.name,tbp.description,SUM(tbs.ReadTime) AS sumTime into #sumTable
from tb_statistic tbs,tb_project tbp Where tbp.id = tbs.cdId Group By tbp.name,tbp.description
select * From #sumTable Where id between @pageIndex and @pageIndex + 9
end
else
begin
select id=identity(int,1,1),tbp.name,tbp.description,Count(tbs.RemoteIP) AS userCount into #userTable
from tb_statistic tbs,tb_project tbp Where tbp.id = tbs.cdId Group By tbp.name,tbp.description
select * From #userTable Where id between @pageIndex and @pageIndex + 9
end
end
GO