报表每页固定行数使用SQL分页示例

zhouyangping · 发表于 2014-12-11 11:04:23

客户要求效果

使用SQL分页

DECLARE @iPageCount INT = 8 /*每页8条*/
CREATE TABLE #tmpIndex (iIndex INT)
DECLARE @i INT = 1
WHILE @i <=@iPageCount
BEGIN
INSERT #tmpIndex (iIndex) VALUES(@I) ;
SET @I = @I+1
END
SELECT sMaterialNo,ummMaterialGUID
INTO #tmpHdr
FROM dbo.mmMaterialFabric a
WHERE a.sMaterialNo IN
(
'S141009004AR1'
,'S141013007D'
)
SELECT A.ummMaterialGUID,b.sYarnNo,sYarnName=c.sMaterialNo,b.sUsage
,iOrder=ROW_NUMBER() OVER(PARTITION BY B.utmBOMHdrGUID,B.sUsage ORDER BY b.sYarnNo)
,iIndex=CONVERT(INT,0)
,iPage=CONVERT(INT,0)
INTO #tmpBom
FROM dbo.tmBOMHdr a
JOIN dbo.tmBOMDtl b ON b.utmBOMHdrGUID = a.uGUID
JOIN dbo.mmMaterialYarn c ON c.ummMaterialGUID = b.ummMaterialGUID
WHERE a.ummMaterialGUID IN
(
SELECT ummMaterialGUID FROM #tmpHdr
)
UPDATE #tmpBom SET iIndex = (iOrder - 1) % @iPageCount + 1,iPage = (iOrder - 1) / @iPageCount + 1
SELECT DISTINCT ummMaterialGUID,iPage
INTO #tmpPage
FROM #tmpBom
SELECT a.sMaterialNo,a.ummMaterialGUID,b.iPage,c.iIndex
INTO #tmpTotal
FROM #tmpHdr a
JOIN #tmpPage b ON b.ummMaterialGUID = a.ummMaterialGUID,#tmpIndex c
SELECT A.sMaterialNo,a.iPage,a.iIndex
,sYarnNoJ=B.sYarnNo,sYarnNameJ=B.sYarnName
,sYarnNoW=C.sYarnNo,sYarnNameW=C.sYarnName
FROM #tmpTotal A
LEFT JOIN #tmpBom B ON B.ummMaterialGUID = A.ummMaterialGUID AND b.iPage = a.iPage AND b.iIndex = a.iIndex AND B.sUsage = 'J'
LEFT JOIN #tmpBom C ON C.ummMaterialGUID = A.ummMaterialGUID AND c.iPage = a.iPage AND c.iIndex = a.iIndex AND C.sUsage = 'W'
ORDER BY sMaterialNo,iPage,iIndex
DROP TABLE #tmpIndex
DROP TABLE #tmpHdr
DROP TABLE #tmpBom
DROP TABLE #tmpPage
DROP TABLE #tmpTotal

复制代码

		自动登录	找回密码
密码			立即注册