SQL 初级编程分页显示数据

要求在过程中实现（今天刚学的while循环，能用上最好用上）已有这么一张表student就这么两个字段stu_idstu_name（stu_id为10000条数据）要求每... 要求在过程中实现（今天刚学的while循环，能用上最好用上）
已有这么一张表 student
就这么两个字段 stu_id stu_name （stu_id 为10000条数据）
要求每页显示10条数据
就在数据库中完成即可
根据我提供的东西写一段代码初期考虑不用很全面就实现分页就行
别的因素都可不考虑展开

 我来答

3个回答

#热议# 在购买新能源车时，要注意哪些？

匿名用户
2010-04-14

展开全部

呃..."要求在过程中实现" 是指必须要用存储过程???
不管了, 随便写个楼主试试吧~

--以下@page是页码编号.如果一定要用存储过程的话, 直接放到proc里面, 然后加个参数.
①:这个主要是针对stu_id顺序编号.而且, 具有唯一性的情况.
create proc proc_Pagination_1
(
@pageIndex int =1
)
as
declare int @page
set @page = 10*(@page-1)
select top 10 * from student where stu_id not in (select top @page stu_id from student)

②:这个跟stu_id是否为int类型, 是否有顺序编号无关.
create proc proc_Pagination
(
@pageIndex int =1
)
as
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY stu_id Desc) AS Item,stuname FROM student) AS S WHERE Item BETWEEN (@pageIndex-1)*10+1 AND @pageIndex*10

修改了下下.
好了, 就提供这么两个简单的方法供LZ参考,希望有帮助吧~

本回答由提问者推荐

已赞过 已踩过<

评论收起

mwtcz
2010-04-14 · TA获得超过1322个赞

知道小有建树答主

回答量：1367

采纳率：50%

帮助的人：1033万

我也去答题访问个人页

关注

展开全部

给你个我的存储过程做参考。

分页，用不上while.呵呵。

CREATE PROCEDURE UP_PARTY_ADMIN_AUDITLIST
@FILTER TINYINT,
@USERCODE VARCHAR(60),
@CATECODE VARCHAR(60),--0代表全部
@PAGESIZE INT,
@PAGEINDEX INT
AS
--DECLARE @CATES TABLE( CATECODE VARCHAR(60))
--INSERT INTO @CATES select * from f_split(@CATECODE,'|' )

create table #groups (groupcode varchar(60))

declare @groupcode varchar(60),
@rolecode varchar(60),
@offset int,
@s_offset varchar(20),
@s_pagesize varchar(20),
@strsql varchar(4000),
@strrc varchar(2000),
@fields varchar(1000),
@cond varchar(200),
@sort varchar(200),
@tables varchar(200),
@currdate char(10)

select @groupcode = groupcode from party_user where usercode=@usercode
select @rolecode = rolecode from party_usergroup where groupcode=@groupcode

if (@pageIndex < 1 ) set @pageIndex = 1
set @offset= @pagesize * ( @pageIndex- 1 )
set @s_offset = ltrim(rtrim(str(@offset)))
set @s_pagesize = ltrim(rtrim(str(@pagesize)))
set @currdate = convert(char(10),getdate(),102)
set @fields = ' *,(select catename from party_cate c where c.catecode=doc.catecode ) as catename,(case status when 1 then ''通过'' else ''未通过'' end ) as statusname,(select groupname from party_usergroup ug where ug.groupcode=doc.groupcode) as groupname '
if @rolecode ='admin'
begin

set @cond = ' 1=1 '
if ( @catecode <> '0' ) set @cond = @cond + ' and doc.catecode=''' + @catecode + ''''
if ( @filter <> 100 ) set @cond = @cond + ' and isnull(doc.status,0)=' + ltrim(rtrim(str(@filter))) + ' '
set @tables = ' party_document doc '
set @sort = ' order by id desc '
end
else
begin
insert into #groups (groupcode ) select groupcode from party_usergroup where parentcode=@groupcode --获取子组
set @cond = ' doc.groupcode=g.groupcode'
if ( @catecode <> '0' ) set @cond = @cond + ' and doc.catecode=''' + @catecode + ''''
if ( @filter <> 100 ) set @cond = @cond + ' and isnull(doc.status,0)=' + ltrim(rtrim(str(@filter))) + ' '
set @tables = ' party_document doc,#groups g '
set @sort = ' order by id desc '
end
--select * from #groups

set @strsql = 'select top {top} {fields} from {tables} where {cond} and doc.id not in (select top {offset} id from {tables} where {cond} {sort}) {sort}'
set @strrc = ' select count(1) as rc from {tables} where {cond} '
set @strsql = replace(@strsql,'{top}',@s_pagesize)
set @strsql = replace(@strsql,'{tables}',@tables)
set @strsql = replace(@strsql,'{cond}',@cond)
set @strsql = replace(@strsql,'{offset}',@offset)
set @strsql = replace(@strsql,'{sort}',@sort)
set @strsql = replace(@strsql,'{fields}',@fields)

set @strrc = replace(@strrc,'{tables}',@tables)
set @strrc = replace(@strrc,'{cond}',@cond)

exec (@strrc)
exec (@strsql)

GO

已赞过 已踩过<

评论收起

hanxiaoll
2010-04-14 · 超过41用户采纳过TA的回答

知道小有建树答主

回答量：117

采纳率：100%

帮助的人：79.5万

我也去答题访问个人页

关注

展开全部

我刚看到是过程，呵呵，估计我的不行了，我给你发个新闻列表的例子吧，然后你就修改一下sql语句和字段名称就行了
<?
include "config.php";
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="keywords" content="<?=$titles?>" />
<meta name="description" content="<?=$titles?>" />
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title><?=$titles?></title>
<style type="text/css">

</style>
</head>
<LINK href="images/client/css.css" type=text/css rel=stylesheet>
<body>
<?
$sql="select Id,pid,subject,instime,hit from news where types=1 order by pid desc";
$rec=mysql_query($sql,$conn);
$total=mysql_num_rows($rec);
$PageSize=10;
$TotalRows=$total;//总共有多少记录
$TotalPages=ceil($TotalRows/$PageSize);//总共有多少页
$Rowstring=" "."共有 ".$TotalPages." 页";

if(isset($_GET["showPage"]))
{
$showPage=intval($_GET["showPage"]);
}
else
{
$showPage=1;
}
$CurrentLocation.=$_SERVER["PHP_SELF"];
$sql0="select Id,pid,subject,instime,hit from news where types=1 order by pid desc"." limit ".($showPage-1)*$PageSize.",".$PageSize;
//echo $sql;
$result=mysql_query($sql0,$conn);
$tmpi=0;
?>
<table width="670" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="751" height="26" background="images/client/news_x.jpg" style="border-bottom:#e5e5e5 1px solid;border-left:#e5e5e5 1px solid;border-right:#e5e5e5 1px solid;border-top:#c75701 2px solid;"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr align="center">
<td width="47" class="enfont">NO.</td>
<td width="1"></td>
<td width="441" class="enfont">Subject</td>
<td width="1"></td>
<td width="110" class="enfont">Date</td>
<td width="1"></td>
<td width="65" class="enfont">Hit</td>
</tr>
</table></td>
</tr>
<tr>
<td height="280" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<? while($row=mysql_fetch_array($result)){ ?>
<tr align="center" class="font">
<td width="47" height="24" style="border-bottom:#efefef 1px solid"><?=$row['pid']?></td>
<td width="1" style="border-bottom:#efefef 1px solid"></td>
<td width="441" align="left" style="border-bottom:#efefef 1px solid"><a href="news_detail.php?id=<?=$row['Id']?>" target="_blank">
<?=$row['subject']?>
</a></td>
<td width="1" style="border-bottom:#efefef 1px solid"></td>
<td width="110" style="border-bottom:#efefef 1px solid"><?=$row['instime']?></td>
<td width="1" style="border-bottom:#efefef 1px solid"></td>
<td width="65" style="border-bottom:#efefef 1px solid"><?=$row['hit']?></td>
</tr>
<?
$tmpi++;
}
?>
</table></td>
</tr>
<tr>
<td height="50" style="border-top:#c75701 2px solid;"><table cellspacing="0" cellpadding="0" width='100%' align="center">
<tr>
<td align="right" class="page_break"><?php if($showPage > 1){?>
<a href="<?php echo $CurrentLocation;?>?showPage=<?php echo $showPage-1;?>">上一页</a>
<?php }
if($TotalPages==1){}
else if($showPage==1&&$TotalPages>1){
echo "1";
for($p=2;$p<=5&&$p<=$TotalPages;$p++){?>
<a href="<?php echo $CurrentLocation;?>?showPage=<?php echo $p;?>"><?php echo $p;?></a>
<?php }}
else if($showPage<=5){
for($p=1;$p<=4+$showPage&&$p<=$TotalPages;$p++){
if($p==$showPage){
echo $p;?>
<?php }else{?>
<a href="<?php echo $CurrentLocation;?>?showPage=<?php echo $p;?>"><?php echo $p;?></a>
<?php }}}else if($showPage>5) {
for($p=$showPage-5;$p<=$showPage+4&&$p<=$TotalPages;$p++){
if($p==$showPage){
echo $p;?>
<?php }else{?>
<a href="<?php echo $CurrentLocation;?>?showPage=<?php echo $p;?>"><?php echo $p;?></a>
<?php }}}?>
<?php if(($showPage < $TotalPages)&&($TotalPages<>1)){?>
<a href="<?php echo $CurrentLocation;?>?showPage=<?php echo $showPage+1;?>">下一页</a>
<?php }?>
<?php
echo $Rowstring;
?>
</td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>

已赞过 已踩过<

评论收起

更多回答（1）

推荐律师服务：若未解决您的问题，请您详细描述您的问题，通过百度律临进行免费专业咨询

SQL 初级编程 分页显示数据

为你推荐：

SQL 初级编程分页显示数据