分页、查询、排序

作者：ikmb@163.com

create procedure sum_one --主表名 @table_name varchar(50),--从表名 @sec_table_name varchar(50),--主表主键名 @col_name varchar(50),--从表外键名 @sec_col_name varchar(50),--主表聚合列 @pri_sum_col varchar(350),--从表聚合列 @sec_sum_col varchar(350),--页大小,默认10 @page_size int=10,--当前页,默认第一页 @current_page int=1,--where子句,默认空,格式为 and+条件 @the_where varchar(350)='',--排序列：1:主键名；2：“ck”主表聚合列；3：“rk”从表聚合列;4：“(ck-rk)”差值(默认) @order_TYPE varchar(50)='(ck-rk)',--升降(asc/desc),默认DESC @the_order varchar(10)='DESC' AS declare @the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> varchar(5000) /* ----------- set @table_name ='tbChuKuWuZiJiLu' set @sec_table_name ='tbKuCunJiLuBiao' set @col_name='char_CKWZJL_SN' set @sec_col_name ='vchar_XiangGuanRuKuWuZiSN' set @pri_sum_col ='float_CKWZJL_YingTiShuLiang' set @sec_sum_col ='float_KCJLB_ZhongLiang' set @the_where ='and 1=1' set @order_TYPE ='(ck-rk)' set @the_order='ASC' set @page_size=10 set @current_page=1 ----------- */ --选出聚合比较的两个量和主表主键 select @the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>='create table #tem1 (sn char(11) primary key,ck int,rk int) insert into #tem1 select '+@col_name+',avg('+@pri_sum_col+') as ck,sum('+@sec_sum_col+') as rk from '+@table_name+' as t1,'+@sec_table_name+' as t2 where 1=1 '+@the_where +' and t1.'+@col_name+'=t2.'+@sec_col_name+' group by '+@col_name+';' --<a href="https://www.jb51.cc/tag/fenye/" target="_blank" class="keywords">分页</a> if @the_order='ASC' --升 begin select @the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>=@the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>+'SELECT * FROM ( SELECT TOP '+str(@page_size)+' temp_ck.*,ck,rk,(ck-rk) AS ck_rk FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE ( tem_1.sn=temp_ck.char_CKWZJL_SN and '+@col_name+' <= ( SELECT MAX('+@col_name+') FROM ( SELECT TOP '+str(@page_size*@current_page)+' '+@col_name+' FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE tem_1.sn=temp_ck.char_CKWZJL_SN '+@the_where+' ORDER BY '+@order_TYPE+' ASC ) AS TT1 ) ) '+@the_where+' ORDER BY '+@order_TYPE+' DESC ) as T2 ORDER BY '+@order_TYPE+' ASC ' end else --降 begin select @the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>=@the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>+'SELECT * FROM ( SELECT TOP '+str(@page_size)+' temp_ck.*,(ck-rk) AS ck_rk FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE ( tem_1.sn=temp_ck.char_CKWZJL_SN and '+@col_name+' >= ( SELECT MIN('+@col_name+') FROM ( SELECT TOP '+str(@page_size*@current_page)+' '+@col_name+' FROM '+@table_name+' as temp_ck,#tem1 as tem_1 WHERE tem_1.sn=temp_ck.char_CKWZJL_SN '+@the_where+' ORDER BY '+@order_TYPE+' DESC ) AS TT1 ) ) '+@the_where+' ORDER BY '+@order_TYPE+' ASC ) as T2 ORDER BY '+@order_TYPE+' DESC ' end EXEC(@the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>) --select @the_<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a> GO