数据库中复杂的联查+筛选条件+排序+分页一直是比较头疼的问题
为了模拟这个问题，首先建立两个表
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'
可以兼容表或视图的分页，sqlserver2000下测试通过