首页 > > 网页制作 > css >

那些可以在数据库里做的事：分页与过滤

2018-06-23 22:21:56来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

其实很多非业务逻辑的功能，比如分页，数据过滤，可以在程序上面节省很多内存和CPU时间，但往往又找不到一个比较通用有效的方法，花了点时间，终于把我想要的在数据库中分页和过滤的功能写了出来，在这里分享。

第一期望：我希望输入页码（pageIndex），每页显示的记录数(pageSize)，然后这个存储过程就可以给我一个当前页的数据集。

首先，我假设有个表叫Configuration，里面就3个字段，Id, Key, Value，就是一个简单的数据表，定义如下：

CREATE TABLE [dbo].[Configuration]
(
    [Id] int NOT NULL IDENTITY (1,1), 
    [Key] varchar(20) NOT NULL UNIQUE,
    [Value] nvarchar(max) NULL
)

然后，就可以写存储过程了，这里想要说下，我对存储过程命名的一些习惯，我会以"sp_"开头，为了标明这个存储过程是CRUD中的哪个操作，我会再附加一个缩写，比如这个分页的主要操作时READ，所以我的名字应该是"sp_r_"，然后为了说明它的主要功能是分页，就有了"sp_r_p_"，“p”表示paginate，最后跟上操作的主要的表名,"sp_r_p_configuration"。很多同学都喜欢用比如bypage等等，也可以，但是我更喜欢这种命名法，在特定的约定下，这种简洁性的命名可可读性。
再者，一个良好的代码书写规范是非常必要的，该注释的地方千万别省，在多数情况下，代码的可读性是非常重要的，除非因性能因素做必须得让步。

复制代码
/*
PROCEDURE:    [dbo].[sp_r_p_configuration]
OPERATION:    READ
FEATURES:        Paginate
PURPOSE:        provide a paginated list from the configuration table
CREATOR:        Jerry Weng
CREATETIME:    2014-4-9
UPDATETIME:    2014-4-9
VERSION:        1.0
*/

CREATE PROCEDURE [dbo].[sp_r_p_configuration]
    @pageIndex int = 1,                    /*which page of the total page*/
    @pageSize int=10,                    /*how many records show in one page*/
    @recordCount int OUTPUT,        /*output: return the count of the total records in all pages*/
    @pageCount int OUTPUT            /*output: return the count of the total pages*/
AS
    declare @startRow int;                -- the row number of the first record in the page
    declare @endRow int;                -- the row number of the last record in the page
    set @startRow = (@pageIndex - 1) * @pageSize + 1;
    set @endRow= @startRow + @pageSize - 1;

    ;with tmp as 
    (select (ROW_NUMBER() over(order by Id)) as row_id, [Id], [Key], [Value] 
    from dbo.Configuration)
    select [Id], [Key], [Value] from tmp
    where row_id between @startRow and @endRow
    
    -- calculate the record count in all pages
    select @recordCount=COUNT(*) from dbo.Configuration
    -- calculate the page count
    set @pageCount = CEILING(@recordCount/CAST(@pageSize as float))

RETURN 0
复制代码

这里用的ROW_NUMBER()来实现分页，性能必拼SQL的方法好，WITH...AS可以省下一个表变量。最后计算总记录数和页数很简单，算下就好了。

第二期望：这样的，似乎差不多了，但是我还希望能够有一套规则，来过滤结果集，并且还要有类似AND和OR的运算功能，而且我仍旧不希望用inline-script去实现，能不用尽量不要用。

为了实现这个期望，我需要先定义怎么传入过滤规则，我最终决定用的是xml数据类型而不是普通字符串，因为普通字符串有长度限制（如果你说可以用text，那我只能说太奢侈，小弟用不起），定短了可能被截断出现错误输出，定长了，最多也就8000个字符，也浪费，我不喜欢不确定因素，然而xml不一样，既是结构化数据，又有索引支持，可长可短，客户端也可直接序列化成字符串传入，非常方便。于是剩下的是，约定一个规则，确定过滤规则的数据结构，在这里我专门写了一个function来将xml转换成table已被后用。P.S. 如果想传一个数组到存储过程，也可以用xml类型，比字符串加分隔符更安全。

复制代码
/*
FUNCTION:        [dbo].[fun_xmlfilter_parse]
OPERATION:    READ
FEATURES:        
PURPOSE:        parse a table from a xml with the specific column name
CREATOR:        Jerry Weng
CREATETIME:    2014-4-9
UPDATETIME:    2014-4-9
VERSION:        1.0
*/

CREATE FUNCTION [dbo].[fun_xmlfilter_parse]
(
    @filter xml,                    /*    The format should be:
                                        <filter>
                                            <rule 
                                                column="COLUMN_NAME"    -- the column name which the rule should be applied
                                                match="xxx"                    -- the key which the rule defines to filter and the result should match
                                                required="0|1"                    -- if 1, the result collection has to meet this rule or return nothing
                                             />
                                             <rule ....
                                        </filter>
                                    */
    @column varchar(100)
)
RETURNS @returntable TABLE 
(
    [Match] nvarchar(100),
    [Required] bit
)
AS
BEGIN
    ;with tmp as
    (
    select
            [T].[RULE].value('@column','varchar(100)') as [Column],
            [T].[RULE].value('@match','nvarchar(100)') as [Match],
            [T].[RULE].value('@required','bit') as [Required]
    from @filter.nodes('filter//rule') [T]([RULE])
    )
    INSERT @returntable
    select [Match],[Required] from tmp where [Column] = @column
        
    RETURN 
END
复制代码

xml的结构由一个根节点<filter>开始，里面是多个<rule>节点，具体的规则定义在rule的属性列表中。column表示具体要过滤哪列的数据，match表示要匹配的数据值，比如我想要过滤列"Value"下，值为"sample"的数据，这样的话我只需要把xml写成<filter><rule column="value" match="sample" required="1" /></filter>，最后的哪个required就是来实现AND和OR的操作的，如果是1表示必须匹配，类似AND，0的话可选匹配，类似OR，输出的表示由COLUMN的值过滤过的，因为比较是以COLUMN为维度的。

最后，新建一个存储过程，叫"sp_r_fp_configuration"，这里比上一个多了一个"f"，表示带有过滤功能。

 

复制代码
/*
PROCEDURE:    [dbo].[sp_r_fp_configuration]
OPERATION:    READ
FEATURES:        Paginate, Filter
PURPOSE:        provide a paginated & filterable list from the configuration table
CREATOR:        Jerry Weng
CREATETIME:    2014-4-9
UPDATETIME:    2014-4-9
VERSION:        1.0
*/

CREATE PROCEDURE [dbo].[sp_r_fp_configuration]
    @pageIndex    int = 1,                /*which page of the total page*/
    @pageSize        int=10,                /*how many records show in one page*/
    @filter            xml,                    /*filter string for the records*/
    @recordCount int OUTPUT,        /*output: return the count of the total records in all pages*/
    @pageCount    int OUTPUT            /*output: return the count of the total pages*/
AS
    if(@filter is null)
    begin
        exec dbo.sp_r_p_configuration 
                @pageIndex,
                @pageSize,
                @pageCount=@pageCount OUTPUT, 
                @recordCount=@recordCount OUTPUT
    end
    else
    begin
        
        declare @tmptbl table (row_id int, Id int, [Key] varchar(100), [Value] varchar(200))
    
        declare @startRow int;                -- the row number of the first record in the page
        declare @endRow int;                -- the row number of the last record in the page
        set @startRow = (@pageIndex - 1) * @pageSize + 1;
        set @endRow= @startRow + @pageSize - 1;

        insert into @tmptbl
            select (ROW_NUMBER() over (order by m.Id)) as row_id, m.[Id], m.[Key], m.[Value] 
            from dbo.Configuration as m
            outer apply fun_xmlfilter_parse(@filter,'key') as k
            outer apply fun_xmlfilter_parse(@filter,'value') as v
            where 
            (
                not (isnull(k.[Required],0)=0 and isnull(v.[Required],0)=0) and
                (isnull(k.[Required],0)=0 or (k.[Required] = 1 and isnull(m.[Key],'') = isnull(k.[Match],''))) and
                (isnull(v.[Required],0)=0 or (v.[Required] = 1 and isnull(m.[Value],'') = isnull(v.[Match],'')))
            ) or
            k.[Required] = 0 and isnull(m.[Key],'') = isnull(k.[Match],'') or
            v.[Required] = 0 and isnull(m.[Value],'') = isnull(v.[Match],'')
        
        select [Id], [Key], [Value] from @tmptbl
        where row_id between @startRow and @endRow
    
        -- calculate the record count in all pages
        select @recordCount=COUNT(*) from @tmptbl
        -- calculate the page count
        set @pageCount = CEILING(@recordCount/CAST(@pageSize as float))
    end
RETURN 0
复制代码

这里用了一个表变量，注意表变量是可被缓存的，临时表没有哦，用表变量不用with...as是因为后面在计算过滤后的总记录数的时候，还需用一次过滤后的集合。这里我希望列Key和列Value都可以被过滤，于是用了两个outer apply来将刚才那个函数的输出表附加到每行上，在where比较的地方，先比较Required是1的必要条件，当然如果针对某个column没有rule的话，也就是required is null的情况，是需要排除的，最后再附加连个required=0的可选条件，这样的话，一个select就可以把各种条件过滤出来了。不过，有个问题是，如果需要过滤的列比较多，那比较语句也要一条条加上去。

最后，来测试下：

我给表填充了这么些数据：

复制代码
SET IDENTITY_INSERT [dbo].[Configuration] ON
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (1, N'test1', N'123')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (2, N'test2', N'123')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (3, N'test3', N'567')
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (4, N'test4', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (5, N'test5', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (6, N'test6', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (7, N'test7', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (8, N'test8', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (9, N'test9', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (10, N'test10', NULL)
INSERT [dbo].[Configuration] ([Id], [Key], [Value]) VALUES (11, N'test11', NULL)
SET IDENTITY_INSERT [dbo].[Configuration] OFF
复制代码

测试脚本：

复制代码
declare @pageIndex int; set @pageIndex=1;
declare @pageSize int;    set @pageSize=5;
declare @filter xml; 
set @filter='<filter><rule column="key" match="test3" required="1"/><rule column="value" match="123" required="0"/></filter>'
declare @pageCount int;
declare @recordCount int;

exec dbo.sp_r_fp_configuration @pageIndex,@pageSize,@filter,@pageCount=@pageCount OUTPUT, @recordCount=@recordCount OUTPUT
select @pageCount as [PAGECOUNT], @recordCount as [RECORDCOUNT]
复制代码

输出，如果需要找null值的话，直接把match属性去掉就可以了<rule column="value" required="1"/>：

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：《面试系列一》一个怂到极点的开始

下一篇：log4net

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签