各种数据库分页查询语句汇总（Various database paging query statement summary）

各种数据库分页查询语句汇总（Various database paging query statement summary） 各种数据库分页查询语句汇总(Various database paging query statement summary) Pagination is often used when displaying recorded entries. A common method is to rewrite the original query statements by using the positioning interfaces of various databases, so as to extract only certain records in a specific range. Different databases, query positioning interface is not the same, the following do a summary: data base Paging query statement Explain MySql "QUERY_SQL limit??" using the limit keyword, the first "?" is the starting line number, Second "is the number of entries to return?" Oracle SELECT * FROM (SELECT A.*, ROWNUM RN FROM (QUERY_SQL) A WHERE ROWNUM = RN)? Combining the rownum keyword, the nested three layer select is used Sentence realization. The first "?" means termination of line numbers, The second "?" means the actual line number Sql Server There is no universal statement to use top n to return to the previous n record or to use stored procedures DB2 Hypothetical query: select t1.* from T1 order By t1.id; pagination statements can be: "Select * from (select rownumber () over") (order by t1.id) as row_, t1.* from T1 Order by t1.id) as temp_ where row_ Between? +1 and? "Returns two"? "Records InterBase "QUERY_SQL row? To?" returns the record between the two "?" PostgreSQL "QUERY_SQL limit? Offset?" the first "?" is the starting line number, and the second "?" represents Return record number Summary of Sql Server stored procedure Pagination Paging scheme one: (using Not In and SELECT TOP paging) Sentence form: SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN) (SELECT TOP 20 ID FROM TestTable ORDER BY ID) ORDER BY ID SELECT TOP page size * FROM TestTable WHERE (ID NOT IN) (SELECT TOP (page size * page number) id FROM table ORDER BY ID) ORDER BY ID Paging scheme two: Sentence form: Create PROCEDURE ListPage @tblName nvarchar (200) - the connection of a table or multiple tables to be displayed @fldName nvarchar (200) = '*', to display a list of fields. @pageSize int = 10, the number of records displayed per page @page int = 1, - to show the record of that page @pageCount int = 1 output, the total number of pages after the query results are paged @Counts int = 1 output, - the number of records to be checked @fldSort nvarchar (100) = null, - sort field list or condition @Sort bit = 0, - sort method, 0 in ascending order, 1 in descending order @strCondition nvarchar (200) = null, - query condition, without where @ID nvarchar (50) -- the primary key of the master table ) AS SET NOCOUNT ON Declare @sqlTmp nvarchar (1000) - storing dynamically generated SQL statements Declare @strTmp nvarchar (1000) -- the query statement that stores the total number of query results Declare @strID nvarchar (1000) -- a query that stores the beginning or end of the query ID declare @ sqlsort nvarchar (200) - - - - - - - - - - 存放临 时生成的排序条件 declare @ intcounts int - - - - - - - - - - 要移动的 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 数 declare @ beginid int - - - - - - - - - - 开始的id declare @ endid int - - - - - - - - - - 结束的id -------- 首先生成排序方法 --------- if @ sort = 0 - - - - - - - - 升序 begin if not (@ fldsort is null) set @ sqlsort = "order by" + @ fldsort else set @ sqlsort = "order by" + @ id end else - - 降序 begin if not (@ fldsort is null) set @ sqlsort = "order by" + @ fldsort + 'desc' else set @ sqlsort = "order by" + + "desc" @ id end -------- 生成查询语句 -------- - - - - - - - - 此处 @ strtmp为取得查询结果数量的语句 if @ strcondition is null - - 没有设置显示条件 begin set @ sqltmp = @ fldname + 'from' + @ tblname set @ strtmp = "select @ counts = count ('+ @ id +') from a '+ @ tblname set @ strid = 'from' + @ tblname end else begin set @ sqltmp = + @ fldname + 'from' + @ tblname + 'where' + @ strcondition set @ strtmp = "select @ counts = count ('+ @ id +') from a '+ @ tblname +' where '+ @ strcondition set @ strid = 'from' + @ tblname + 'where' + @ strcondition end - - - - - - - - - - - - - - - - 取得查询结果总数量 ----- exec sp _ executesql @ strtmp, n '@ counts int out', @ counts out - - - - - - - - 取得分页总数 if @ counts < = @ pagesize set @ pagecount = 1 else the set pagecount = @ (@ counts / @ pagesize) + 1 - - - - - - - - 计算要移动的记录数 if @ page = 1 set @ intcounts = @ pagesize else begin the set intcounts = @ (@ page - 1) * @ pagesize + 1 end ----- 取得分页后此页的第一条记录的id set @ strid = "select @ beginid = '+ @ id +' '+ @ strid set @ intcounts = @ intcounts - @ pagesize + 1 set rowcount @ intcounts exec sp _ executesql @ strid, n '@ beginid int out', @ beginid out ----- 取得分页后此页的最后一条记录的id set @ intcounts = @ intcounts + @ pagesize - 1 print @ intcounts set rowcount @ intcounts exec sp _ executesql @ strid, n '@ beginid int out', @ endid out ------ 恢复系统设置 ----- set rowcount 0 set nocount off ------ 返回查询结果 ----- if @ strcondition is null set @ strtmp = 'select' + @ sqltmp + 'where' + @ id + "between" + str (@ beginid) + "and" + str (@ endid) else set @ strtmp = 'select' + @ sqltmp + 'where' + @ id + '(between "+ str (@ beginid) +" and "+ str (@ endid) +') and '+ @ strcondition if not (@ sqlsort is null) set @ strtmp = @ strtmp + @ sqlsort exec sp _ executesql @ strtmp go 分页 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 三: 语句形式: create procedure sy _ more _ cs ( @ mode int = 0, @ pagesize int = 10, the pageindex int = 1 ) as set nocount on if @ mode = 0 begin select count (*) as 记录数 from dbo.house _ info _ cs; end else begin declare @ indextable table (id int identity (1.1), nid int) declare @ pagelowerbound int declare @ pageupperbound int set @ (@ pageindex pagelowerbound = - 1) * @ pagesize set @ pageupperbound = @ pagelowerbound + @ pagesize insert into @ indextable (nid) select [id] from dbo.house _ info _ cs order by dt desc; select * from dbo.house ls. _ info _ cs ls, @ indextable t where ls.id = t.nid and t.id > @ pagelowerbound and t.id < = @ pageupperbound order by t.id