求SQL分页的语句

发布网友 发布时间：2022-04-25 09:39

我来回答

共5个回答

热心网友 时间：2022-04-07 16:52

这是我以前写的分页存储过程..楼主用这个来分页试试
--SELECT * FROM AAA ORDER BY BOMHID DESC
--EXEC up_MovePage 'AAA','*','BOMHID','Y',57,8,8,''
ALTER PROCEDURE up_MovePage
@pTabName NVARCHAR(100)='AAA', -- 所要查询的表名
@pFields NVARCHAR(1000) = '*', -- 需要返回的列,逗号隔开
@pIndexKey NVARCHAR(100)='', -- 查询的唯一Key
@pIsDesc CHAR(1)='Y', -- 排序的顺序.Y:DESC;N:ASC
@pRecCount INT=1, -- 表的记录总数
@pPageIndex INT=1, -- 查询页码,默认设置为1
@pPageNum INT=8, -- 每页设置记录数(这里应该为8)
@pQuery NVARCHAR(1500)= '' -- 查询条件 (提示:不要加 WHERE)
AS
BEGIN
SET NOCOUNT ON
DECLARE @AStrSQL NVARCHAR(2000) -- 最终SQL查询语句
DECLARE @AStrLocate NVARCHAR(250) -- 查询范围
DECLARE @AStrOrder NVARCHAR(250) -- 排序
DECLARE @ARecCount INT -- 表@pTabName的总记录数
DECLARE @AOrderStr NVARCHAR(255)
IF @pIsDesc='Y'
BEGIN
SET @AOrderStr= ' ORDER BY '+@pIndexKey+' DESC'
IF @pPageIndex<=1
BEGIN
IF LEN(@pQuery)=0 OR @pQuery IS NULL -- 查询条件为空,即没有查询条件
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM ' + @pTabName + ' ' + @AOrderStr
ELSE
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM ' + @pTabName + ' WHERE ' + @pQuery + @AOrderStr
END
ELSE
BEGIN
IF LEN(@pQuery)=0 OR @pQuery IS NULL -- 查询条件为空,即没有查询条件
BEGIN
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM '+@pTabName+' WHERE '+ @pIndexKey + ' IN '
+'(SELECT TOP '+STR(@pRecCount-@pPageNum*(@pPageIndex-1))+ ' ' + @pIndexKey +' FROM '+@pTabName+' ORDER BY '+@pIndexKey+' ASC'
+')'
+@AOrderStr
END
ELSE
BEGIN
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM '+@pTabName+' WHERE '+ @pIndexKey + ' IN '
+'(SELECT TOP '+STR(@pRecCount-@pPageNum*(@pPageIndex-1))+ ' ' + @pIndexKey +' FROM '+@pTabName+' WHERE ' + @pQuery + ' ORDER BY '+@pIndexKey+' ASC'
+') WHERE '+ @pQuery
+@AOrderStr
END
END
END
ELSE
BEGIN
SET @AOrderStr= ' ORDER BY '+@pIndexKey+' ASC'
IF @pPageIndex<=1
BEGIN
IF LEN(@pQuery)=0 OR @pQuery IS NULL -- 查询条件为空,即没有查询条件
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM ' + @pTabName + ' ' + @AOrderStr
ELSE
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM ' + @pTabName + ' WHERE ' + @pQuery + @AOrderStr
END
ELSE
BEGIN
IF LEN(@pQuery)=0 OR @pQuery IS NULL -- 查询条件为空,即没有查询条件
BEGIN
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM '+@pTabName+' WHERE '+ @pIndexKey + ' IN '
+'(SELECT TOP '+STR(@pRecCount-@pPageNum*(@pPageIndex-1))+ ' ' + @pIndexKey +' FROM '+@pTabName+' ORDER BY '+@pIndexKey+' DESC'
+')'
+@AOrderStr
END
ELSE
BEGIN
SET @AStrSQL = 'SELECT TOP ' + STR(@pPageNum) + ' ' + @pFields + ' FROM '+@pTabName+' WHERE '+ @pIndexKey + ' IN '
+'(SELECT TOP '+STR(@pRecCount-@pPageNum*(@pPageIndex-1))+ ' ' + @pIndexKey +' FROM '+@pTabName+' WHERE ' + @pQuery + ' ORDER BY '+@pIndexKey+' DESC'
+') WHERE '+ @pQuery
+@AOrderStr
END
END
END
EXEC (@AStrSQL)
SET NOCOUNT OFF
END

热心网友 时间：2022-04-07 18:10

2个参数Start end
start 开始 end 结束
例如第二页从Start11条开始 结束end 21百

select * from (select rownum a,NEWS.* from NEWS where rownum<='"+end+"') b where b.a>'"+start+"'"

方法2 select * from NEWS limit a,b
a就是从第几条开始 b就是查询出的条数
例如 从第10条开始后的10条select * from NEWS limit 10,10
从第6条开始后的8条select * from NEWS limit 6,8

热心网友 时间：2022-04-07 19:44

select top 10 * from news where id not in(select top 10 * from news),一条sql 语句就可实现 其中id 是你表中的主键

热心网友 时间：2022-04-07 21:36

public class Sql {
public List parameter(Pager page){
List list=new ArrayList();

Connection con=null;

int begin = 1;
int end = page.getPageSize();
if(null != page)
{
begin = (page.getCurrentPage()-1)*page.getPageSize() + 1;
end = page.getCurrentPage()*page.getPageSize();
}
//查询SQL
String sq="select rownum R,t.* from NEWS t"

// 查询总数
String sql1 = "SELECT COUNT(*)COU FROM NEWS P WHERE 1= 1 ";

//分页查询
String sql2 = "SELECT O.*,ROWNUM FROM (" + sq + ")O WHERE O.R >= " + begin + " and O.R <=" + end;

try{
con=Conn.getConnection();

Statement stat = con.createStatement();

ResultSet rs = stat.executeQuery(sql1);
while(rs.next())
{
page.setTotalRows(rs.getInt("COU"));
page.setPageCount();
}

rs.close();

stat = con.createStatement();

rs = stat.executeQuery(sql2);
while (rs.next()){
List alist=new ArrayList();
alist.add(rs.getString(2));
list.add(alist);
}
}catch (Exception e) {
e.printStackTrace();
}
finally
{
if(null != con)
{
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;

}

你只要SQL的代码！
如果没有Pager 的话！ 在加20分 给你！

热心网友 时间：2022-04-07 23:44

下面是一个HQL分页查询:(pagenum:第几页;pagesize:每页几条记录)

public List findAll(int pagenum,int pagesize){
String hql = "from Email";
Query query = getSession().createQuery(sql);
query.setFirstResult((pagenum-1)*pagesize);
query.setMaxResults(pagesize);
return list;
}