SQL 查询分页功能实现
1 use xyduane
2
3 create table test
4 (
5 id int identity(1,1) primary key,
6 name varchar(50),
7 email varchar(50),
8 tel varchar(50)
9 )
10
11 --批量插入数据多条数据
12 SET IDENTITY_INSERT test ON
13 declare @i int
14 set @i=1
15 while @i<=20000
16 begin
17 insert into test([id], name, email, tel) values(@i, 'xxxx','xxxx@qq.com','15273015047')
18 set @i=@i+1
19 end
20 SET IDENTITY_INSERT test OFF
21
22 declare @pageNo int=3
23 declare @pageSize int=300;
24
25 with pageList as
26 (
27 select top (@pageNo*@pageSize+1) ROW_NUMBER() over(order by id asc)as rowNum,* from test order by id
28 )
29 select * from pageList where rowNum>(@pageNo-1)*@pageSize and rowNum<=@pageSize*@pageNo
2
3 create table test
4 (
5 id int identity(1,1) primary key,
6 name varchar(50),
7 email varchar(50),
8 tel varchar(50)
9 )
10
11 --批量插入数据多条数据
12 SET IDENTITY_INSERT test ON
13 declare @i int
14 set @i=1
15 while @i<=20000
16 begin
17 insert into test([id], name, email, tel) values(@i, 'xxxx','xxxx@qq.com','15273015047')
18 set @i=@i+1
19 end
20 SET IDENTITY_INSERT test OFF
21
22 declare @pageNo int=3
23 declare @pageSize int=300;
24
25 with pageList as
26 (
27 select top (@pageNo*@pageSize+1) ROW_NUMBER() over(order by id asc)as rowNum,* from test order by id
28 )
29 select * from pageList where rowNum>(@pageNo-1)*@pageSize and rowNum<=@pageSize*@pageNo
1 CREATE PROC P_viewPage 2 3 /**//* 4 nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284 5 敬告：适用于单一主键或存在唯一值列的表或视图 6 ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 7 8 */ 9 10 @TableName VARCHAR(200), --表名 11 @FieldList VARCHAR(2000), --显示列名，如果是全部字段则为* 12 @PrimaryKey VARCHAR(100), --单一主键或唯一值键 13 @Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9 14 @Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc 15 --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷 16 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 17 @RecorderCount INT, --记录总数 0:会返回总记录 18 @PageSize INT, --每页输出的记录数 19 @PageIndex INT, --当前页数 20 @TotalCount INT OUTPUT, --记返回总记录 21 @TotalPageCount INT OUTPUT --返回总页数 22 AS 23 SET NOCOUNT ON 24 25 IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0 26 SET @Order = RTRIM(LTRIM(@Order)) 27 SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) 28 SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','') 29 30 WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0 31 BEGIN 32 SET @Order = REPLACE(@Order,', ',',') 33 SET @Order = REPLACE(@Order,' ,',',') 34 END 35 36 IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' 37 OR ISNULL(@PrimaryKey,'') = '' 38 OR @SortType < 1 OR @SortType >3 39 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 40 BEGIN 41 PRINT('ERR_00') 42 RETURN 43 END 44 45 IF @SortType = 3 46 BEGIN 47 IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC') 48 BEGIN PRINT('ERR_02') RETURN END 49 END 50 51 DECLARE @new_where1 VARCHAR(1000) 52 DECLARE @new_where2 VARCHAR(1000) 53 DECLARE @new_order1 VARCHAR(1000) 54 DECLARE @new_order2 VARCHAR(1000) 55 DECLARE @new_order3 VARCHAR(1000) 56 DECLARE @Sql VARCHAR(8000) 57 DECLARE @SqlCount NVARCHAR(4000) 58 59 IF ISNULL(@where,'') = '' 60 BEGIN 61 SET @new_where1 = ' ' 62 SET @new_where2 = ' WHERE ' 63 END 64 ELSE 65 BEGIN 66 SET @new_where1 = ' WHERE ' + @where 67 SET @new_where2 = ' WHERE ' + @where + ' AND ' 68 END 69 70 IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 71 BEGIN 72 IF @SortType = 1 73 BEGIN 74 SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC' 75 SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC' 76 END 77 IF @SortType = 2 78 BEGIN 79 SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC' 80 SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC' 81 END 82 END 83 ELSE 84 BEGIN 85 SET @new_order1 = ' ORDER BY ' + @Order 86 END 87 88 IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0 89 BEGIN 90 SET @new_order1 = ' ORDER BY ' + @Order 91 SET @new_order2 = @Order + ',' 92 SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},') 93 SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,') 94 SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1) 95 IF @FieldList <> '*' 96 BEGIN 97 SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',') 98 SET @FieldList = ',' + @FieldList 99 WHILE CHARINDEX(',',@new_order3)>0 100 BEGIN 101 IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0 102 BEGIN 103 SET @FieldList = 104 @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3)) 105 END 106 SET @new_order3 = 107 SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3)) 108 END 109 SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) 110 END 111 END 112 113 SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' 114 + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1 115 116 IF @RecorderCount = 0 117 BEGIN 118 EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', 119 @TotalCount OUTPUT,@TotalPageCount OUTPUT 120 END 121 ELSE 122 BEGIN 123 SELECT @TotalCount = @RecorderCount 124 END 125 126 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) 127 BEGIN 128 SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) 129 END 130 131 IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) 132 BEGIN 133 IF @PageIndex = 1 --返回第一页数据 134 BEGIN 135 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 136 + @TableName + @new_where1 + @new_order1 137 END 138 IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 139 BEGIN 140 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 141 + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize)) 142 + ' ' + @FieldList + ' FROM ' 143 + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' 144 + @new_order1 145 END 146 END 147 ELSE 148 BEGIN 149 IF @SortType = 1 --仅主键正序排序 150 BEGIN 151 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 152 BEGIN 153 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 154 + @TableName + @new_where2 + @PrimaryKey + ' > ' 155 + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' 156 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 157 + ' FROM ' + @TableName 158 + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1 159 END 160 ELSE --反向检索 161 BEGIN 162 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 163 + 'SELECT TOP ' + STR(@PageSize) + ' ' 164 + @FieldList + ' FROM ' 165 + @TableName + @new_where2 + @PrimaryKey + ' < ' 166 + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' 167 + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey 168 + ' FROM ' + @TableName 169 + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 170 + ' ) AS TMP ' + @new_order1 171 END 172 END 173 IF @SortType = 2 --仅主键反序排序 174 BEGIN 175 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 176 BEGIN 177 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 178 + @TableName + @new_where2 + @PrimaryKey + ' < ' 179 + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' 180 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 181 +' FROM '+ @TableName 182 + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1 183 END 184 ELSE --反向检索 185 BEGIN 186 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' 187 + 'SELECT TOP ' + STR(@PageSize) + ' ' 188 + @FieldList + ' FROM ' 189 + @TableName + @new_where2 + @PrimaryKey + ' > ' 190 + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' 191 + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey 192 + ' FROM ' + @TableName 193 + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2 194 + ' ) AS TMP ' + @new_order1 195 END 196 END 197 IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理 198 BEGIN 199 IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0 200 BEGIN PRINT('ERR_02') RETURN END 201 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 202 BEGIN 203 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' 204 + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' 205 + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList 206 + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' 207 + @new_order2 + ' ) AS TMP ' + @new_order1 208 END 209 ELSE --反向检索 210 BEGIN 211 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' 212 + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' 213 + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList 214 + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' 215 + @new_order1 + ' ) AS TMP ' + @new_order1 216 END 217 END 218 END 219 PRINT(@Sql) 220 EXEC(@Sql) 221 GO
1 CREATE PROC P_public_ViewPage 2 /**//* 3 no_mIss 通用分页存储过程 2007.3.1 QQ:34813284 4 适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开) 5 调用： 6 第一页查询时返回总记录和总页数及第一页记录： 7 EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3', 8 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',0,10,1, 9 @TotalCount OUTPUT,@TotalPageCount OUTPUT 10 其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)： 11 EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3', 12 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',2000000,10,89, 13 @TotalCount OUTPUT,@TotalPageCount OUTPUT 14 */ 15 16 @TableName VARCHAR(200), --表名 17 @FieldList VARCHAR(2000), --显示列名 18 @PrimaryKey VARCHAR(100), --单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开) 19 @Where VARCHAR(1000), --查询条件 不含'where'字符 20 @Order VARCHAR(1000), --排序 不含'order by'字符,用英文,隔开 21 @RecorderCount INT, --记录总数 0:会返回总记录 22 @PageSize INT, --每页输出的记录数 23 @PageIndex INT, --当前页数 24 @TotalCount INT OUTPUT, --返回记录总数 25 @TotalPageCount INT OUTPUT --返回总页数 26 AS 27 28 SET NOCOUNT ON 29 30 SET @FieldList = REPLACE(@FieldList,' ','') 31 IF @FieldList = '*' 32 BEGIN SET @FieldList = 'A.*'END 33 ELSE 34 BEGIN 35 SET @FieldList = 'A.' + REPLACE(@FieldList,',',',A.') 36 END 37 38 WHILE CHARINDEX(', ',@Order)>0 39 BEGIN 40 SET @Order = REPLACE(@Order,', ',',') 41 END 42 43 IF ISNULL(@TableName,'') = '' OR ISNULL(@PrimaryKey,'') = '' 44 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 45 BEGIN 46 RETURN 47 END 48 49 DECLARE @new_where1 VARCHAR(1000) 50 DECLARE @new_where2 VARCHAR(1000) 51 DECLARE @new_where3 VARCHAR(1000) 52 DECLARE @new_where4 VARCHAR(1000) 53 DECLARE @new_order1 VARCHAR(1000) 54 DECLARE @new_order2 VARCHAR(1000) 55 DECLARE @Fields VARCHAR(1000) 56 DECLARE @Sql VARCHAR(8000) 57 DECLARE @SqlCount NVARCHAR(4000) 58 59 SET @Fields = @PrimaryKey + ',' 60 SET @new_where2 = '' 61 SET @new_where4 = '' 62 63 64 IF ISNULL(@where,'') = '' 65 BEGIN 66 SET @new_where1 = ' ' 67 SET @new_where3 = ' WHERE ' 68 END 69 ELSE 70 BEGIN 71 SET @new_where1 = ' WHERE ' + @where + ' ' 72 SET @new_where3 = ' WHERE 1=1 ' 73 + REPLACE(' AND ' + @where,' AND ',' AND A.')+ ' AND ' 74 END 75 76 WHILE CHARINDEX(',',@Fields)>0 77 BEGIN 78 SET @new_where2 = @new_where2 79 + 'A.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) 80 + ' = B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' AND ' 81 SET @new_where4 = @new_where4 82 + 'B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' IS NULL AND ' 83 SET @Fields = SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields)) 84 END 85 SET @new_where2 = LEFT(@new_where2,LEN(@new_where2)-4) 86 SET @new_where4 = LEFT(@new_where4,LEN(@new_where4)-4) 87 88 IF ISNULL(@order,'') = '' 89 BEGIN 90 SET @new_order1 = '' 91 SET @new_order2 = '' 92 END 93 ELSE 94 BEGIN 95 SET @new_order1 = ' ORDER BY ' + @Order 96 SET @new_order2 = ' ORDER BY ' 97 + RIGHT(REPLACE(',' + @Order,',',', A.' ), 98 LEN(REPLACE(',' + @Order,',',', A.' ))-1) 99 END 100 101 SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' 102 + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName 103 + ' A ' + @new_where1 104 105 IF @RecorderCount = 0 106 BEGIN 107 EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', 108 @TotalCount OUTPUT,@TotalPageCount OUTPUT 109 END 110 ELSE 111 BEGIN 112 SELECT @TotalCount = @RecorderCount 113 END 114 115 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) 116 BEGIN 117 SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) 118 END 119 IF @PageIndex = 1 120 BEGIN 121 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 122 + @TableName + ' A'+ @new_where1 + @new_order1 123 END 124 ELSE 125 BEGIN 126 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 127 + @TableName + ' A LEFT JOIN (SELECT TOP ' 128 + STR(@PageSize*(@PageIndex-1)) 129 + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 130 + @new_order1 + ' )B ON ' + @new_where2 + @new_where3 131 + @new_where4 + @new_order2 132 END 133 134 EXEC(@Sql) 135 GO
http://www.cnblogs.com/cljdream/p/3327068.html 介绍的比较全面
浙公网安备 33010602011771号