用视图+存储过程解决复杂查询的排序分页问题

原文地址：http://www.cnblogs.com/S-E-P/archive/2012/06/10/2543970.html

数据库中复杂的联查+筛选条件+排序+分页一直是比较头疼的问题

 

为了模拟这个问题，首先建立两个表

create table t_userType (    id int identity(1,1) not null primary key,    name varchar(50))GOcreate table t_user (    id int identity(1,1) not null primary key,    t_userTypeId int not null,    name varchar(50),    foreign key (t_userTypeId) references t_userType(id))GO

 

下面插入一些测试数据

　　

 

在t_user这个表中，t_userTypeId字段关联到了t_userType这个表

我们希望在查询用户时，同时查询到这个用户类型的name，可以通过联查实现

select u.*, t.name as typeName    from t_user u    inner join t_userType t    on t.id = u.t_userTypeId

 

如果联查的表多了，就会比较复杂，所以建立一个视图

create view view_user_andTypeas    select u.*, t.name as typeName    from t_user u    inner join t_userType t    on t.id = u.t_userTypeIdgo

 

这时，使用下面的语句，就能得到我们想要的结果

select * from view_user_andType

 

如果想提供分页功能的话，需要这样写

select top 5 * from view_user_andType where id not in (select id top 0 view_user_andType)

 

加入条件过滤和排序

select top 5 * from view_user_andType where id>1 and     id not in (        select top 0 id view_user_andType         where id>1 order by id) order by id

 

如果每个表的联查都写成这样，也是比较头大的

所以通过一个存储过程，封装分页和排序逻辑

-- 存储过程：通用分页---- 分页查询某个表或视图---- 参数列表：--        srcTableName：视图或表名--        idColumnName：主键列名--        pageSize：每页长度（1～n）--        pageIndex：页码（1～n）--        condition：过滤条件--        orderBy：排序方式，必须为查询结果中的字段名--        isDesc：是否倒序，可选值（true, false）--SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOIF OBJECT_ID ( 'proc_selectByPage', 'P' ) IS NOT NULL     Drop PROCEDURE [proc_selectByPage];GOcreate  procedure [dbo].[proc_selectByPage]    @srcTableName varchar(50),    @idColumnName varchar(50) = 'id',    @pageSize int = 10,    @pageIndex int = 1,    @condition varchar(500) = '',    @orderBy varchar(50),    @isDesc varchar(50) = 'false'ASbegin    -- 参数容错    if (@pageIndex <= 0)    begin        set @pageIndex = 1    end    -- 组装语句    declare @sql1 varchar(4000)    set @sql1 = 'select top ' + cast (@pageSize as varchar(50)) +                 ' * from ' + @srcTableName +                ' where (' + @idColumnName +                        ' not in (select top ' + cast ((@pageSize * (@pageIndex-1)) as varchar(50)) +                        ' ' + @idColumnName +                        ' from ' + @srcTableName        if ( @condition <> '' )    begin        set @sql1 = @sql1 + ' where ' + @condition    end                            set @sql1 = @sql1 + ' order by ' + @orderBy                            if ( @isDesc = 'true' )     begin         set @sql1 = @sql1 + ' desc ';    end    else if ( @isDesc = 'false' )    begin        set @sql1 = @sql1 + ' asc ';    end    set @sql1 = @sql1 + '  ) '        if ( @condition <> '' )    begin        set @sql1 = @sql1 + ' and ' + @condition    end        set @sql1 = @sql1 + ')'        set @sql1 = @sql1 + ' order by ' + @orderBy        if ( @isDesc = 'true' )     begin         set @sql1 = @sql1 + ' desc ';    end    else if ( @isDesc = 'false' )    begin        set @sql1 = @sql1 + ' asc ';    end        -- 输出语句，并执行    print @sql1    exec(@sql1)endGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

 

再实现相同的功能就可以这样写

exec proc_selectByPage 'view_user_andType', 'id', 3, 2, '', 'name', 'false'