sql: paging in SQL Server

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
--sql server 2012 及以上
SELECT  * FROM BookKindList
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
go
 
-- geovindu Geovin Du 涂聚文
Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=1
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by BookKindID Desc) AS 'RowNum',*
From BookKindList Where BookKindID > 0)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)
go
 
Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=2
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by BookKindID Desc) AS 'RowNum',*
From BookKindList Where BookKindID > 0)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)
go
 
 
 
 
 
-- 2 - QUERY USING "ROW_NUMBER"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 4
SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY BookKindID) AS Numero,
                    * FROM BookKindList
               ) AS TBL
WHERE BookKindID BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY BookKindID
GO
 
 
 
-- 3 - QUERY USING "OFFSET" AND "FETCH NEXT" (SQL SERVER 2012) Geovin Du geovindu 涂聚文
DECLARE @PageNumber AS INT, @PageSize AS INT
SET @PageNumber = 1
SET @PageSize = 4
SELECT *
FROM BookKindList
ORDER BY BookKindID
OFFSET ((@PageNumber - 1) * @PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY
GO
 
 
DECLARE @PageNumber AS INT, @PageSize AS INT,@totalcount INT,@pagecount int,@c int
SET @PageNumber = 2
SET @PageSize = 4
SELECT *
FROM BookKindList
ORDER BY BookKindID
OFFSET ((@PageNumber - 1) * @PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY;
 
SELECT @totalcount=count(*) FROM BookKindList
--Select @c=@totalcount%@PageSize
--Select @pagecount=@totalcount/@PageSize
SELECT  @totalcount as '总记录'
--if @c>0  
--Select  @pagecount+1 as '共页数'
--else
--Select @pagecount as '共页数'
--Select @c
Select @pagecount=dbo.getPageModulus(@totalcount,@PageSize)
GO
 
--'*'#查询字段  涂聚文 Geovin Du geovindu
--,'bookkindlist'#表名
--,'1=1'#条件
--,'BookKindID desc'#排序
--,1 #页码
--,5 #每页记录数
 
DECLARE
@sql as nvarchar(2000),  ---
@sqlt as nvarchar(2000),
@fields as nVARCHAR(1000),  --要查询的字段，用逗号(,)分隔
@tables as nvarchar(150),        --要查询的表
@where as nVARCHAR(2000),  --查询条件
@orderby as nVARCHAR(200),  ---BookKindID desc 排序规则
@pageindex as INT, --查询页码 geovindu
@pageSize as INT,  --每页记录数
@totalcount as INT,    --总记录数 out
@pagecount as INT    --总页数 out
 
set @fields='*';
set @tables='BookKindList';
set @where='1=1';
set @orderby='BookKindID desc'
set @pageindex=1;
set @pageSize=14;
set @sql='SELECT '+@fields+' FROM '+@tables+''
if @where<>''
  set @sql=@sql+' where '+@where
if @orderby<>''
  set @sql=@sql+' ORDER BY '+@orderby+''
 
set @sql=@sql+' OFFSET (('+CONVERT(nVARCHAR(20),@pageindex)+' - 1) * '+CONVERT(nVARCHAR(20),@PageSize)+') ROWS FETCH NEXT '+CONVERT(nVARCHAR(20),@PageSize)+' ROWS ONLY;'
set @sqlt='SELECT @totalcount=count(*) FROM BookKindList'
if @where<>''
   set @sqlt= @sqlt+' where '+@where;
--set @sql=@sql+' Select @pagecount=dbo.getPageModulus(@totalcount,@PageSize);'
print(@sql);
exec(@sql);
EXEC sp_executesql @sqlt,N'@totalcount int OUTPUT',@totalcount OUTPUT
Select @pagecount=dbo.getPageModulus(@totalcount,@PageSize)
Select @pagecount as '总页数',@totalcount as '总记录'
GO
 
 
                Declare @sql nvarchar(4000),@SqlWhere nvarchar(1000),@TableName nvarchar(100);
                Declare @totalRecord int;   
            --Declare @TotalPage int;
                --计算总记录数         geovindu
                set @TableName='BookKindList'
                set @sqlWhere='1=1';
                if (@SqlWhere='' or @sqlWhere=NULL)
                    set @sql = 'select @totalRecord = count(*) from ' + @TableName
                else
                    set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
                EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
                select @totalRecord
                --计算总页数
               print @Sql
go
 
 
declare @tableName nvarchar(100)
declare @sqlQuery nvarchar(max)
declare @fields varchar(500)
set @tableName = 'BookKindList'
set @fields = 'BookKindName,BookKindParent'
set @sqlQuery = 'select ' + @fields + ' from ' + QUOTENAME(@tableName)
execute sp_executesql @sqlQuery
go

　　

https://www.codeproject.com/articles/55616/custom-paging-stored-procedure
https://www.programmerall.com/article/37701010712/

 


According to TOP ID

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE PROC [dbo].[proc_select_page_top]
    @pageindex INT=1,--current page number
    @pagesize INT=10,--Page size
    @tablename VARCHAR(50)='',--Table Name
    @fields VARCHAR(1000)='',--Query field collection
    @keyid VARCHAR(50)='',--Primary key
    @condition NVARCHAR(1000)='',--Query conditions
    @orderstr VARCHAR(500),--Sort condition
    @totalRecord BIGINT OUTPUT--total
AS
    IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
    IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
    IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
    DECLARE @sql NVARCHAR(4000)
    --The total number of records in the table
    --IF(@totalRecord IS NULL)
    --BEGIN
        SET @sql=N'SELECT @totalRecord=COUNT(*)'
            +N' FROM '+@tablename
            +N' WHERE '+@condition
        EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
    —END
    IF(@pageindex=1)
    BEGIN
        SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
        EXEC(@sql)
    END
    ELSE
    BEGIN
        SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+
                 N' WHERE  '+@keyid+N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+
                 N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr
        EXEC(@sql)
    END
GO

　　


According to row_number () over

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE PROC [dbo].[proc_select_page_row]
    @pageindex INT=1,--current page number
    @pagesize INT=10,--Page size
    @tablename VARCHAR(50)='',--Table Name
    @fields VARCHAR(1000)='*',--Query field collection
    @keyid VARCHAR(50)='',--Primary key
    @condition NVARCHAR(1000)='',--Query conditions
    @orderstr VARCHAR(500),--Sort condition
    @totalRecord BIGINT  OUTPUT--total
AS
    IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
    IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
    IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
    DECLARE @sql NVARCHAR(4000)
    --The total number of records in the table
    --  IF @totalRecord IS NULL
    --  BEGIN
            SET @sql=N'SELECT @totalRecord=COUNT(*)'
                +N' FROM '+@tablename
                +N' WHERE '+@condition
            EXEC sp_executesql @sql,N'@totalRecord bigint OUTPUT',@totalRecord OUTPUT
    --END
    IF(@pageindex=1)
    BEGIN
        SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
        EXEC(@sql)
    END
    ELSE
    BEGIN
        DECLARE @StartRecord INT
        SET @StartRecord = (@pageindex-1)*@pagesize + 1
        SET @sql=N'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+ @orderstr +N') AS rowId,'+@fields+
                 N' FROM '+ @tablename+N') AS T WHERE rowId>='+STR(@StartRecord)+
                 N' and rowId<='+STR(@StartRecord + @pagesize - 1)
        EXEC(@sql)
    END
GO

　　


According to the max (min) ID

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
--According to MAX(MIN)ID
CREATE PROC [dbo].[proc_select_id]
    @pageindex int=1,--current page number
    @pagesize int=10,--Page size
    @tablename VARCHAR(50)='',--Table Name
    @fields VARCHAR(1000)='',--Query field collection
    @keyid VARCHAR(50)='',--Primary key
    @condition NVARCHAR(1000)='',--Query conditions
    @orderstr VARCHAR(500),--Sort condition
    @totalRecord BIGINT OUTPUT--total
AS
    IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
    IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
    IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
    DECLARE @sql NVARCHAR(4000)
    --The total number of records in the table
    --IF(@totalRecord IS NULL)
    --BEGIN
        SET @sql=N'SELECT @totalRecord=COUNT(*)'
            +N' FROM '+@tablename
            +N' WHERE '+@condition
        EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
    --END
    IF(@pageindex=1)
    BEGIN
        SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
        EXEC(@sql)
    END
    ELSE
    BEGIN
        DECLARE @operatestr CHAR(3),@comparestr CHAR(1)
        SET @operatestr='MAX'
        SET @comparestr='>'
        IF(@orderstr<>'')
        BEGIN
            IF(CHARINDEX('desc',LOWER(@orderstr))<>0)
            BEGIN
                SET @operatestr='MIN'
                SET @comparestr='<'
            END
        END
        SET @sql=N'SELECT top '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid+@comparestr
            +N'(SELECT '+@operatestr+N'('+@keyid+N') FROM '+@tablename+N' WHERE '+@keyid
            +N' IN (SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '+@tablename+N' WHERE '
            +@condition+N' '+@orderstr+N')) AND '+@condition+N' '+@orderstr
        EXEC(@sql)
    END
GO

　　

 

 

 

 

posted @   ®Geovin Du Dream Park™  阅读(46)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2015-12-28 csharp: json to csharp
2014-12-28 csharp:Learn how to post JSON string to generic Handler using jQuery in ASP.Net C#.
2012-12-28 Csharp: listview control binding database from datatable
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示