sql 多表分页查询

USE [ data_smf ]
GO
/* ***** 对象: StoredProcedure [dbo].[catsearch] 脚本日期: 01/23/2011 04:34:30 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ catsearch ]

@perfectkey nvarchar ( 20 ) = ' close ' , -- 是否完全匹配
@pagecout int = 1 , -- 返回总页数或数据 等于0时 返回总页数 等于 -1时返回第一页和总页数
@searchvalue nvarchar ( 50 ) = ' - ' , -- 要查找的内容
@searchcolumn nvarchar ( 50 ) = ' - ' , -- 查询的列名
@country nvarchar ( 50 ) = ' - ' , -- 地域

@numberfew int =- 1 ,
@numbermany int =- 1 ,
@pricelow int =- 1 ,
@pricehigh int =- 1 ,
@days int = 3 ,

@ordercolmn nvarchar ( 50 ) = ' puttime ' , -- 用于排序的字段
@order nvarchar ( 50 ) = ' desc ' ,
@size int = 20 , -- 每页显示数据的条数
@column nvarchar ( 100 ) = ' * ' , -- 返回的字段
@table1 nvarchar ( 100 ) = ' produce ' , -- 要查询的表名称
@table2 nvarchar ( 100 ) = ' produce4 ' , -- 要查询的表名称
@table3 nvarchar ( 100 ) = ' - ' -- 要查询的表名称
AS
BEGIN
SET NOCOUNT ON ;
declare @shortcache nvarchar ( 1000 )
declare @selectstr nvarchar ( 1000 )
declare @sqlpagecountstr nvarchar ( 3000 )
declare @sqlstr nvarchar ( 3000 )
declare @sqlstr1 nvarchar ( 1000 )
declare @sqlstr2 nvarchar ( 1000 )
declare @sqlstr3 nvarchar ( 1000 )
declare @ifnull int
set @ifnull = 0
SET @selectstr = ' where '
set @shortcache = ' '



-- -----------数量 [count] 为用于排序的列名
-- -----------用于对商品数量的查询 产生一个由and 连接的条件 当不输入条件时跳过
-- --完成
if ( @numberfew <>- 1 and @numbermany <>- 1 )
begin
set @shortcache = ' ([counts] between ' + cast ( @numberfew as nvarchar ( 50 )) + ' and ' + cast ( @numbermany as nvarchar ( 50 )) + ' ) and '
end
else if ( @numberfew <>- 1 and @numbermany =- 1 )
begin
set @shortcache = ' ([counts]=> ' + cast ( @numberfew as nvarchar ( 50 )) + ' ) and '
end
else if ( @numberfew =- 1 and @numbermany <>- 1 )
begin
set @shortcache = ' ([counts]<= ' + cast ( @numbermany as nvarchar ( 50 )) + ' ) and '
end
if ( @shortcache <> ' ' )
begin
set @selectstr = @selectstr + @shortcache
set @shortcache = ' '
end
else
set @ifnull = @ifnull + 1

-- --价格 [price]price 为用于排序的列名
-- --用于限制商品价格由and 连接 当不输入值时跳过
-- -- 完成
if ( @pricelow <>- 1 and @pricehigh <>- 1 )
begin
set @shortcache = ' ([price] between ' + cast ( @pricelow as nvarchar ( 50 )) + ' and ' + cast ( @pricehigh as nvarchar ( 50 )) + ' ) and '
end
if ( @pricelow <>- 1 and @pricehigh =- 1 )
begin
set @shortcache = ' (cast([price] as int)> ' + cast ( @pricelow as nvarchar ( 50 )) + ' ) and '
end
if ( @pricelow =- 1 and @pricehigh <>- 1 )
begin
set @shortcache = ' (cast([price] as int)< ' + cast ( @pricehigh as nvarchar ( 50 )) + ' ) and '
end
if ( @shortcache <> ' ' )
begin
set @selectstr = @selectstr + @shortcache
set @shortcache = ' '
end
else
set @ifnull = @ifnull + 1


-- ------时间 [puttime]为用于排序的列名
if ( @days <>- 1 )
begin
set @shortcache = ' (cast(datediff(dd,[puttime],getdate()) as int ) between -1 and ' + convert ( nvarchar ( 50 ), @days ) + ' )and '
set @selectstr = @selectstr + @shortcache
end
else
set @ifnull = @ifnull + 1

-- ------地址
if ( @country <> ' - ' )
begin
set @shortcache = ' (country= ''' + @country + ''' ) and '
set @selectstr = @selectstr + @shortcache
end
else
set @ifnull = @ifnull + 1


-- ----完全匹配 已经检测完成 [name] [text][trader]为用于排序的列名
if ( @perfectkey = ' open ' and @searchvalue <> ' - ' )
begin
if ( @searchcolumn = ' - ' )
begin
set @shortcache = ' ([name]= ''' + @searchvalue + ''' or ' + ' [text]= ''' + @searchvalue + ''' or ' + ' [trader]= ''' + @searchvalue + ''' ) and '
-- --------set @shortcache=' where [name]=''100'' or [text]=''100'' or [price]=''100'''
set @selectstr = @selectstr + @shortcache
end
else
begin
set @shortcache = ' ([ ' + @searchcolumn + ' ]= ''' + @searchvalue + ''' ) and '
-- --------set @shortcache=' where [name]=''土豆'''
set @selectstr = @selectstr + @shortcache
end
end



-- ------不完全匹配 已经检测完成
else if ( @perfectkey = ' close ' and @searchvalue <> ' - ' )
begin
if ( @searchcolumn = ' - ' )
begin
set @shortcache = ' ([name] like '' % ' + @searchvalue + ' % '' or [text] like '' % ' + @searchvalue + ' % '' or [trader] like '' % ' + @searchvalue + ' % '' ) and '
-- --------set @shortcache='1=1'
set @selectstr = @selectstr + @shortcache
end
else
begin
set @shortcache = ' ( ' + @searchcolumn + ' like '' % ' + @searchvalue + ' % '' ) and '
set @selectstr = @selectstr + @shortcache
end
end
else
begin
set @ifnull = @ifnull + 1
end
if ( @ifnull = 5 )
begin
set @selectstr = ' '
end
else
begin
set @selectstr = @selectstr + ' 1=1 '
end

-- -------合并查询语句
set @sqlstr1 = ' select top 200 ' + @column + ' from ' + @table1 + ' ' + @selectstr + ' '
if ( @table2 <> ' - ' )
begin
set @sqlstr2 = ' select top 200 ' + @column + ' from ' + @table2 + ' ' + @selectstr + ' '
end
else
begin
set @sqlstr2 = ' '
end

if ( @table3 <> ' - ' )
begin
set @sqlstr3 = ' select top 200 ' + @column + ' from ' + @table3 + ' ' + @selectstr + ' '
end
else
begin
set @sqlstr3 = ' '
end
set @sqlstr = ' select * from ( select top 1000 row_number() over( order by ' + @ordercolmn + ' ' + @order + ' ) as RowNum ,*from ( ' + @sqlstr1 + ' union all ' + @sqlstr2 + ' ) s ) t where t.RowNum between ' + cast ( @size as nvarchar ( 20 )) + ' *( ' + cast ( @pagecout as nvarchar ( 20 )) + ' -1 ) and ' + cast ( @size as nvarchar ( 20 )) + ' * ' + cast ( @pagecout as nvarchar ( 20 )) + ' '
set @sqlpagecountstr = ' select RowNumber=ceiling(count(id) *1.0/ ' + cast ( @size as nvarchar ( 20 )) + ' from ( select top 1000 row_number() over( order by ' + @ordercolmn + ' ) as RowNum from ( ' + @sqlstr1 + ' union all ' + @sqlstr2 + ' ) s ) t '



-- --返回总行数
if ( @pagecout = 0 )
begin
exec ( @sqlpagecountstr )
end
-- ----返回调用页的数据
if ( @pagecout > 0 )
begin
exec ( @sqlstr )
end
-- ----返回总页数，并且返回第一页数据
if ( @pagecout =- 1 )
begin
exec ( @sqlstr )
set @pagecout = 1
set @sqlpagecountstr = ' select RowNumber=ceiling(count(id) *1.0/ ' + cast ( @size as nvarchar ( 20 )) + ' from ( select top 1000 row_number() over( order by ' + @ordercolmn + ' ) as RowNum from ( ' + @sqlstr1 + ' union all ' + @sqlstr2 + ' ) s ) t '
exec ( @sqlpagecountstr )
end
END

 

 

本人新手  刚写的多表分页查询

有不足之处 和改进之处 请指点

 

转载于:https://www.cnblogs.com/360016786/archive/2011/01/23/1942300.html