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

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

 

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

create table t_userType (
    id int identity(1,1) not null primary key,
    name varchar(50)
)
GO

create 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_andType
as
    select u.*, t.name as typeName
    from t_user u
    inner join t_userType t
    on t.id = u.t_userTypeId
go

 

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

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 
GO
SET ANSI_NULLS ON 
GO
IF OBJECT_ID ( 'proc_selectByPage', 'P' ) IS NOT NULL 
    Drop PROCEDURE [proc_selectByPage];
GO
create  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'
AS
begin

    -- 参数容错
    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)

end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

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

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

 

可以兼容表或视图的分页，sqlserver2000下测试通过