实现分页查询的一种方法,使用Microsoft SQL Server数据库
- 一、SQL语言用于分页的语句
- 二、后端过程设计示例
- 三、前端实现逻辑
- 总结
各数据库均提供了分页查询的语句和相应的方法,但是如果查询的结果较为复杂,如查询结果需要结过再计算、分段、添加汇总行等操作后再输出,就需要考虑的更为全面一些,而且还要配合前端实现的逻辑。本文是在实践中用到一种方法,仅供参考。
一、SQL语言用于分页的语句
在最新版本的Microsoft SQL Server中,分页查询通常是通过使用OFFSET
和FETCH NEXT
子句来实现的。这种方法是在SQL Server 2012及以后的版本中引入的,它提供了一种更直观和高效的方式来进行分页。
以下是一个使用OFFSET
和FETCH NEXT
进行分页查询的基本示例:
SELECT *
FROM YourTable
ORDER BY SomeColumn
OFFSET (@pageIndex - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;
在这个例子中:
@pageIndex
表示当前页码,它是一个变量,代表用户请求的页码。@pageSize
表示每页显示的记录数,它也是一个变量。OFFSET
用于跳过指定数量的行,计算方式是(当前页码 - 1) * 每页记录数
。FETCH NEXT
用于限制结果集只返回接下来的指定数量的行,即每页的记录数。
这种方法的优点是它简单、直观,并且性能良好,特别是在处理大量数据时。它避免了在旧版本中常用的一些复杂方法,如使用ROW_NUMBER()
函数或者多次查询来确定行的偏移量。
请注意,为了使用这种方法,你需要确保你的SQL Server版本至少是2012或更高版本。如果你使用的是更早的版本,你可能需要使用其他方法,如ROW_NUMBER()
或者CURSOR
等。
二、后端过程设计示例
USE [hyX1201]
GO
/****** Object: StoredProcedure [dbo].[X9_ZZCH_MXZZ] Script Date: 2024-03-21 15:39:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================================================
-- Author: 昆山华岳软件有限公司 岳国军
-- Script Date: 2023-11-30
-- Description: 总账查询:存货总账_明细总账 分页查询示例程序,本例不使用存储表
-- ===================================================================================
ALTER PROCEDURE [dbo].[X9_ZZCH_MXZZ](@KSNY CHAR(6),@JSNY CHAR(6),@CKMC NVARCHAR(50),@CXYC INT,@CXHS INT,@RQSJ VARCHAR(30))--查询页次:-1 获取总行数,>=0 查询的页次,--查询行数:每页查询行数,--日期时间:可用于命名临时表,由前端生成
ASDECLARE @LJZD VARCHAR(100);SET @LJZD ='存货明细账查询';IF @CXYC=-2RETURN;IF @CXYC=-1BEGIN--返回结果行数WITH MXZZ (CHXH,CHDH,CHMC,XH1,XH2,XH3,GG1,JLDW,QCSL,QCJS,QCJE,SRSL,SRJS,SRJE,FCSL,FCJS,FCJE,JCSL,JCJS,JCJE) AS (SELECT XM.CHXH, XM.CHDH, XM.CHMC, XM.XH1, XM.XH2, XM.XH3, XM.GG1, XM.JLDW,SUM(CASE ZZ.NY WHEN @KSNY THEN ZZ.QCSL ELSE 0 END) AS QCSL, SUM(CASE ZZ.NY WHEN @KSNY THEN ZZ.QCJS ELSE 0 END) AS QCJS, SUM(CASE ZZ.NY WHEN @KSNY THEN ZZ.QCJE ELSE 0 END) AS QCJE, SUM(ZZ.SRSL) AS SRSL, SUM(ZZ.SRJS) AS SRJS, SUM(ZZ.SRJE) AS SRJE, SUM(ZZ.FCSL) AS FCSL, SUM(ZZ.FCJS) AS FCJS, SUM(ZZ.FCJE) AS FCJE, SUM(CASE ZZ.NY WHEN @JSNY THEN ZZ.JCSL ELSE 0 END) AS JCSL, SUM(CASE ZZ.NY WHEN @JSNY THEN ZZ.JCJS ELSE 0 END) AS JCJS, SUM(CASE ZZ.NY WHEN @JSNY THEN ZZ.JCJE ELSE 0 END) AS JCJEFROM UTZZCH AS ZZ INNER JOIN MLCHXM AS XM ON ZZ.CHXH = XM.CHXHWHERE (ZZ.CKMC=@CKMC OR @CKMC='') AND (ZZ.NY BETWEEN @KSNY AND @JSNY)GROUP BY XM.CHXH, XM.CHDH, XM.CHMC, XM.XH1, XM.XH2, XM.XH3, XM.GG1, XM.JLDW)SELECT COUNT(*) AS CXHS FROM MXZZ ; ENDIF @CXYC>-1BEGIN--返回当前页所需要查询结果 WITH MXZZ (CHXH,CHDH,CHMC,XH1,XH2,XH3,GG1,JLDW,QCSL,QCJS,QCJE,SRSL,SRJS,SRJE,FCSL,FCJS,FCJE,JCSL,JCJS,JCJE) AS (SELECT XM.CHXH, XM.CHDH, XM.CHMC, XM.XH1, XM.XH2, XM.XH3, XM.GG1, XM.JLDW,SUM(CASE ZZ.NY WHEN @KSNY THEN ZZ.QCSL ELSE 0 END) AS QCSL, SUM(CASE ZZ.NY WHEN @KSNY THEN ZZ.QCJS ELSE 0 END) AS QCJS, SUM(CASE ZZ.NY WHEN @KSNY THEN ZZ.QCJE ELSE 0 END) AS QCJE, SUM(ZZ.SRSL) AS SRSL, SUM(ZZ.SRJS) AS SRJS, SUM(ZZ.SRJE) AS SRJE, SUM(ZZ.FCSL) AS FCSL, SUM(ZZ.FCJS) AS FCJS, SUM(ZZ.FCJE) AS FCJE, SUM(CASE ZZ.NY WHEN @JSNY THEN ZZ.JCSL ELSE 0 END) AS JCSL, SUM(CASE ZZ.NY WHEN @JSNY THEN ZZ.JCJS ELSE 0 END) AS JCJS, SUM(CASE ZZ.NY WHEN @JSNY THEN ZZ.JCJE ELSE 0 END) AS JCJEFROM UTZZCH AS ZZ INNER JOIN MLCHXM AS XM ON ZZ.CHXH = XM.CHXHWHERE (ZZ.CKMC=@CKMC OR @CKMC='') AND (ZZ.NY BETWEEN @KSNY AND @JSNY)GROUP BY XM.CHXH, XM.CHDH, XM.CHMC, XM.XH1, XM.XH2, XM.XH3, XM.GG1, XM.JLDW)SELECT CHXH,CHDH,CHMC,XH1,XH2,XH3,GG1,JLDW,QCSL,QCJS,CASE WHEN QCSL<>0 THEN QCJE/QCSL ELSE 0 END AS QCDJ,QCJE,SRSL,SRJS,SRJE,FCSL,FCJS,FCJE,JCSL,CASE WHEN JCSL>0 THEN JCJE/JCSL ELSE 0 END AS JCDJ,JCJS,JCJE,@LJZD AS 链接功能 FROM MXZZORDER BY CHDH,CHXH offset (@CXYC*@CXHS) rows fetch next @CXHS rows only; RETURN;END
RETURN;
三、前端实现逻辑
'由前端定义每次查询的行数Public pCXHS As Integer = 25 '查询行数,不再存储本地文件Dim DqPage As Integer = 0 '当前页Dim EndPage As Integer = 0 '最后一页Dim CXrqsj As String = ... '定义一时间戳Dim CXsql As String = ""dim Dt1 as DataTabledim Sql1 as String '原来的查询语句。 ...... sql1根据前端定义赋值EndPage = -1If InStr(UCase(Sql1), "<CXYC>") > 0 And InStr(UCase(Sql1), "<CXHS>") > 0 ThenDim CRsql As String = Replace(CXsql, "<CXYC>", "-1")CRsql = Replace(CRsql, "<CXHS>", pCXHS)CRsql = Replace(CRsql, "<RQSJ>", CXrqsj)'初次查询时获取总行数Dim cxrows As Integer = GetDBInteger(CRsql)If cxrows <= 0 ThenExit SubEnd If'计算出最大页号EndPage = cxrows \ pCXHSIf (cxrows Mod pCXHS) = 0 ThenEndPage -= 1End IfL3.Caption = "共[" & cxrows.ToString.Trim & "]行记录,分" & (EndPage + 1).ToString.Trim & "页查询"ElseL3.Caption = ""End If......根据前端查询状态确定当前页次后查询相应页次的数据Dt1 = New DataTableDim Sql1 As StringSql1 = Replace(CXsql, "<CXYC>", DqPage)Sql1 = Replace(Sql1, "<CXHS>", pCXHS)Sql1 = Replace(Sql1, "<RQSJ>", CXrqsj)Dt1 = GetDTread(Sql1)If IsNothing(Dt1) ThenXXshow("数据未能正确载入", Me)Exit SubEnd IfGridCX.DataSource = NothingGridCX.DataSource = Dt1Gdgv.RefreshData()'更新翻页按钮状态Btpg1.Enabled = (DqPage > 0) And (EndPage > 0)Btpg2.Enabled = (DqPage > 0) And (EndPage > 0)Btpg3.Enabled = (DqPage < EndPage) And (EndPage > 0)Btpg4.Enabled = (DqPage < EndPage) And (EndPage > 0)Lczs.Text = " 第 " & (DqPage + 1).ToString.Trim & " 页 共 " & (EndPage + 1).ToString.Trim & " 页 "......
总结
如果查询结果行数非常多或需要的计算较为复杂,应在初次查询使用(返回行数)时,将查询结果存在临时表中,后面查询就可以从此查询了。总之,不管什么方法,都是需要前端合理配合,才能收到更好的效果。