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