用视图+存储过程解决复杂查询的排序分页问题
为了模拟这个问题，首先建立两个表。
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 id (@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_null on
go
再实现相同的功能就可以这样写
exec proc_selectByPage 'view_user_andType','id',3,2,'','name','false'
可以兼容表或视图的分页，sqlserver2000下测试通过