SQL分页问题
展示一个用户关系，多级的
数据格式如下
IDParentIDLevelOrderNum
96087812
96196023
96296134
96387817
96496328
96596439
966963211
967878113
968878114
969878115
请问根据 level 字段来进行分页，orderNum排序不能变
比如 按2个用户分页
第一页数据如下
IDParentIDLevelOrderNum
96087812
96196023
96296134
96387817
96496328
96596439
966963211
第二页
967878113
968878114
第三页
969878115
在线等答案！！
[解决办法]
IF OBJECT_ID('T') IS NOT NULL DROP TABLE TCREATE TABLE T(ID INT, ParentID INT, LEVEL INT,OrderNum int)--测试数据INSERT INTO TSELECT 960, 878, 1, 2 UNION ALLSELECT 961, 960, 2 ,3 UNION ALLSELECT 962, 961, 3 ,4 UNION ALLSELECT 963, 878, 1 ,7 UNION ALLSELECT 964, 963, 2 ,8 UNION ALLSELECT 965, 964, 3 ,9 UNION ALLSELECT 966, 963, 2 ,11 UNION ALLSELECT 967, 878, 1 ,13 UNION ALLSELECT 968, 878, 1 ,14 UNION ALLSELECT 969, 878, 1 ,15 CREATE PROC sp_getUser( @level INT ,--每页几个用户 @PageIndex int--页面索引)AS BEGIN DECLARE @_tab TABLE (id INT IDENTITY(1,1),startIndex INT, endIndex INT,userID INT) DECLARE @_startIndex INT DECLARE @_endIndex INT ;WITH cte AS ( SELECT [主ID]=ID, * FROM T WHERE [LEVEL]=1 UNION ALL SELECT [主ID]=c.[主ID],a.* FROM T a JOIN cte c ON a.ParentID =c.Id ) INSERT INTO @_tab SELECT MIN(id),MAX(id),[主ID] FROM cte GROUP BY [主ID] -- ORDER BY OrderNum --每页的开始索引 SELECT @_startIndex=startIndex FROM ( SELECT * FROM @_tab WHERE id BETWEEN @level*@PageIndex-1 AND @level*@PageIndex ) AS t WHERE t.id=@level*@PageIndex-1 --每页的结束索引 SELECT @_endIndex=endIndex FROM ( SELECT * FROM @_tab WHERE id BETWEEN @level*@PageIndex-1 AND @level*@PageIndex ) AS t WHERE t.id=@level*@PageIndex PRINT 'start:'+ CAST(@_startIndex AS VARCHAR ) PRINT 'end:'+CAST(@_endIndex AS VARCHAR ) IF @_endIndex IS NULL OR LEN(@_endIndex)=0 BEGIN SELECT * FROM T WHERE id >=@_startIndex END ELSE BEGIN SELECT * FROM T WHERE id BETWEEN @_startIndex AND @_endIndex END END--每页2个用户,第一页EXEC sp_getUser 2,1/*ID ParentID LEVEL OrderNum----------- ----------- ----------- -----------960 878 1 2961 960 2 3962 961 3 4963 878 1 7964 963 2 8965 964 3 9966 963 2 11(7 行受影响)*/--每页2个用户,第二页EXEC sp_getUser 2,2/*ID ParentID LEVEL OrderNum----------- ----------- ----------- -----------967 878 1 13968 878 1 14(2 行受影响)*/--每页2个用户,第三页EXEC sp_getUser 2,3/*ID ParentID LEVEL OrderNum----------- ----------- ----------- -----------969 878 1 15(1 行受影响)*/