ALTER proc [dbo].[proc_enterylist] @qybm varchar(50),@strWhere varchar(2000)=‘‘,@stratrow int,@pagesize int ,@total int out
as
begin
SET NOCOUNT ON /*在此处添加*/
declare @fcompnbr varchar(50)
--如果传进来的是[FCompanyId] ,则取 太乱。
if exists(select 1 from [QY_JBXX] where [FCompanyId] = @qybm) --如果传进来的是[FCompanyId] ,则取 太乱。
begin
select @fcompnbr = @qybm
end
--结束
else
begin
select @fcompnbr = FCompanyId from [dbo].[QY_JBXX] where QYBM = @qybm
end
create table #b(fnumber int ,fname varchar(200),forder dec(10,4) ,ftype varchar(50))
create table #t(id int identity,xm varchar(200) null,sfzh varchar(50) null,XB varchar(10) null,zslx varchar(200) null,zslxname varchar(2000) null,zsjb varchar(100) null,
zczsh varchar(100) null,zczy varchar(100) null,zsyxqjssj datetime null,fzsj datetime null,fzjg varchar(200) null,forder int null,rowspan int null,showname int null,showid int null)
insert into #b
select distinct fnumber,fname,forder,FType from CF_Dic_Person
where ftype =‘zcrylx‘ order by forder
insert into #b
select distinct fnumber,fname,forder/100,FType from CF_Dic_Person a
where ftype =‘qtlbry‘ and not exists(select 1 from #b b where a.fnumber = b.fnumber)
update #b set forder = 5000 where fnumber=407
if (@strWhere=‘‘)
begin
insert into #t(xm,zslx,sfzh,xb,zsjb,zczsh,zczy,zsyxqjssj,fzsj,fzjg,zslxname,forder)
select b.xm,b.zslx,a.sfzh,a.xb,b.zsjb,b.zczsh,b.zczy,case when b.zslx=407 and b.zsjb=115 then ‘2250-12-31‘ else isnull(b.zsyxqjssj,‘2250-12-31‘) end ,b.fzsj,b.fzjg,c.fname,c.forder
from RY_RYZSXX b left join RY_RYJBXX a ON A.rybh = B.rybh
left join #b c on b.zslx = c.fnumber
where b.zszt!=-1 and b.qybm =@fcompnbr
order by sfzh,c.FOrder desc
end
else
begin
declare @strsql varchar(2000)
select @strsql = ‘
insert into #t(xm,zslx,sfzh,xb,zsjb,zczsh,zczy,zsyxqjssj,fzsj,fzjg,zslxname,forder)
select b.xm,b.zslx,a.sfzh,a.xb,b.zsjb,b.zczsh,b.zczy,case when b.zslx=407 and b.zsjb=115 then ‘‘2250-12-31‘‘ else isnull(b.zsyxqjssj,‘‘2250-12-31‘‘) end,b.fzsj,b.fzjg,c.fname,c.forder
from RY_RYZSXX b left join RY_RYJBXX a ON A.rybh = B.rybh
left join #b c on b.zslx = c.fnumber
where b.zszt!=-1 and b.qybm = ‘‘‘+ @fcompnbr + ‘‘‘
‘ + @strWhere + ‘
order by sfzh,c.FOrder‘
exec(@strsql)
end
update #t set forder = b.sumorder from #t a,(select sum(forder) sumorder,sfzh from #t group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
-- update #t set rowspan = b.rowspan from #t a,(select count(1) rowspan,sfzh from #t group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
-- update #t set showname = 1 from #t a,(select min(id) id,sfzh from #t group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0) and a.id = b.id
declare @shid table (id int identity,sfzh varchar(50) null)
insert into @shid(sfzh) select sfzh from (select sfzh,max(forder) forder,min(id) id from #t group by sfzh) dd order by forder desc,id
update #t set showid = b.id from #t a,@shid b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
select @total = count(1) from #t
select top (@pagesize) * into #output from (SELECT ROW_NUMBER() OVER(ORDER BY showid) AS RowNumber, * from #t) aa where RowNumber > @stratrow
--翻页重新计算
update #output set rowspan = sl from #output a ,(select sfzh,count(1) as sl from #output group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0)
update #output set showname = 1 from #output a ,(select sfzh,min(id) id from #output group by sfzh) b where isnull(a.sfzh,0) = isnull(b.sfzh,0) and a.id = b.id
select * from #output ORDER BY showid
select (select count(1) from #t a) as zssum ,count(1) sl,zslxname,zslx from #t c group by zslxname,zslx
end
GO