MS-SQL 分页查询
下面语法在处理分页的程式上很重要笔记笔记~
【ROW_NUMBER()】
利用ROW_NUMBER(),建立一个新表格,再针对新表格查询NewRow,
由程式控制分页数
SELECT NewTable.*
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ACPT_VOU_NO) AS NewRow
,ACPT_VOU_NO,ACPT_VOU_TYPE,CASH_AMT
FROM AC_ACPT_M
WHERE COMPANY_ID = 'PT') AS NewTable
WHERE NewRow>= 1 AND NewRow<= 40
【NOT IN】
利用NOT IN和 TOP 来排除最前面的资料,达成可查询中间的资料,
但小弟比较偏好第一种写法,好程序性也直接
SELECT TOP 5 ACPT_VOU_NO,ACPT_VOU_TYPE,CASH_AMT
FROM AC_ACPT_M AS QQ
WHERE QQ.ACPT_VOU_NO NOT IN (SELECT TOP 5 ACPT_VOU_NO
FROM AC_ACPT_M
WHERE COMPANY_ID = 'PT'
ORDER BY ACPT_VOU_NO)
AND QQ.COMPANY_ID = 'PT'
ORDER BY QQ.ACPT_VOU_NO