当前位置: 数据库>sqlserver
获取指定范围行的sql语句和存储过程
来源: 互联网 发布时间：2014-08-29
本文导语: 获取指定范围行的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;...
获取指定范围行的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 );
(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
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
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。