Tag Archives: SQL

create procedure proc_page
@maxPage int=5, –最多显示页数，默认5页
@rowsNum int=10, –每页行数，默认10行
@rowsSum int=50 –总行数，默认为50行，随机生成
as
if (not @rowsNum between 1 and @rowsSum) or (@maxPage<= 0 ) or(@rowsSum<= 0 )
begin
raiserror (‘错误！请正确指定各项参数！’,16,1)
return
end
IF EXISTS(SELECT * FROM sysobjects WHERE name=’A')
DROP table A
create table A
(
ID int identity(1,1) not null,
Name varchar(10) not null
)
declare @n int
set @n=1
while (@n<=@rowsSum)
begin
insert into A values(substring(replace(newid(),’-',”),1,5))
set @n=@n+1
end

declare @pages int,@pageNo int,@lastRows int –定义变量总页数，页码，最后一页行数
–select @rowsSum=count(*) from A
select @pages=ceiling(@rowsSum*1.0/@rowsNum)
select @lastRows=@rowsSum%@rowsNum
–select pages=30%4

if (@maxPage<@pages)
begin
select @rowsSum=@rowsNum*@maxPage
set @pageNo = 1
print ‘分页显示:一共’+convert(varchar(10),@rowsSum)+’行，每页行数’+convert(varchar(10),@rowsNum)+’，总页数’+convert(varchar(10),@maxPage)
while (@pageNo<=@maxPage)
begin
print ‘——————-第’+convert(varchar(10),@pageNo)+’页——————’
if exists (select * from sysobjects where name=’newA’)
drop table newA

select top (@rowsNum) a.* from (select top (@rowsSum) * from A order by ID) a
where (ID not in (select top (@rowsNum*(@maxPage-@pageNo)) a.ID from a order by a.ID desc))
and
(ID not in (select top (@rowsNum*(@pageNo-1)) a.ID from a order by a.ID))
order by a.ID

set @pageNo=@pageNo+1
end
end

else
begin
set @pageNo = 1
print ‘分页显示:一共’+convert(varchar(10),@rowsSum)+’行，每页行数’+convert(varchar(10),@rowsNum)+’，总页数’+convert(varchar(10),@pages)
if (@lastRows=0)
begin
while (@pageNo<=@pages)
begin
print ‘——————-第’+convert(varchar(10),@pageNo)+’页——————’

select top (@rowsNum) * from A
where (ID not in (select top (@rowsNum*(@pages-@pageNo)) ID from A order by ID desc))
and
(ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
order by ID
set @pageNo=@pageNo+1
end
end
else
begin
while (@pageNo<=@pages)
begin
print ‘——————-第’+convert(varchar(10),@pageNo)+’页——————’
if(@pageNo<>@pages)
begin
select top (@rowsNum) * from A
where (ID not in (select top (@rowsNum*(@pages-@pageNo-1)+@lastRows) ID from A order by ID desc))
and
(ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
order by ID
end
else
select top (@lastRows) * from A
where (ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
order by ID
set @pageNo=@pageNo+1
end
end
end
GO Continue reading →

select stuNo=jHS.stuNo,java成绩=jHS.java,HTML成绩=jHS.HTML,SQL成绩=jHS.SQL,C#成绩=C#.Score
from
(select stuNo=jH.stuNo,java=jH.java,HTML=jH.HTML,SQL=SQL.Score
from
(select stuNo=java.stuNo,java=java.Score,HTML=HTML.Score
from
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’Java’)) java,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’HTML’)) HTML
where java.stuNo=HTML.stuNo) jH,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’SQL’)) SQL
where jH.stuNo=SQL.stuNo) jHS,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’C#’)) C#
where jHS.stuNo=C#.stuNo Continue reading →