能横向翻页的动态交叉查询存储过程

最新推荐文章于 2024-08-20 09:30:53 发布

curllion

最新推荐文章于 2024-08-20 09:30:53 发布

阅读量1.2k

点赞数

分类专栏： SQL 文章标签：存储 join output scroll float c

本文链接：https://blog.csdn.net/curllion/article/details/667735

版权

SQL 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

-----------------------------------按经销售商列出报销汇总交叉表,没有必要按状态来筛-------------------------------------
CREATE PROCEDURE schmreimburse_bydealer
@depid nvarchar(10),
@rstime datetime,
@retime datetime,
@pagesize int,   --输入每页的记录数
@currentpage int,    --输入当前页，0为起始页
@totalpages int output, --返回总页数
@newcpage int output --返回新的当前页，0为起始页
AS
declare @current as int --定义当前记录的位置
declare @total as int --定义记录总条数
declare @i as int --定义循环变量
set @total =(SELECT COUNT(DISTINCT emsdealer.jxname)
             FROM emsreimburse LEFT OUTER JOIN
                   emsmperform ON emsreimburse.porno = emsmperform.orno LEFT OUTER JOIN
                   emsdealer RIGHT OUTER JOIN
                   emswldw ON emsdealer.jxid = emswldw.jxid ON
                   emsmperform.wlid = emswldw.wlid
             WHERE (emsreimburse.reidate between @rstime and @retime) AND (emsdealer.depid = @depid)
             )
set @totalpages = ceiling(cast(@total as float)/ @pagesize)
if @currentpage < @totalpages
begin
   if @currentpage < 0
   begin
      set @newcpage = 0
   end
   else
   begin
      set @newcpage = @currentpage
   end
end
else
begin
   set @newcpage = @totalpages - 1
end
set @current =@newcpage * @pagesize + 1
--区域编号的临时变量
declare @jxname as nvarchar(50)
--存储用游标生成的SQL语句,4000字符是nvarchar类型的极限
declare @tmpsql as nvarchar(4000)
set @tmpsql = ''
--声明一个游标用来从表中循环读取所有的区域名称
declare c cursor scroll
for SELECT emsdealer.jxname
    FROM emsreimburse LEFT OUTER JOIN
         emsmperform ON emsreimburse.porno = emsmperform.orno LEFT OUTER JOIN
         emsdealer RIGHT OUTER JOIN
         emswldw ON emsdealer.jxid = emswldw.jxid ON emsmperform.wlid = emswldw.wlid
    WHERE (emsreimburse.reidate between @rstime and @retime) AND (emsdealer.depid = @depid)
    GROUP BY emsdealer.jxname
    ORDER BY emsdealer.jxname
set @i = 0
open c
--读取第一条记录
fetch absolute @current from c into @jxname
--如果上一FETCH语句成功运行
while ((@@fetch_status = 0) and (@i < @pagesize))
begin
set @tmpsql = @tmpsql + ',SUM(CASE emsdealer.jxname WHEN '''+ @jxname + ''' THEN emsreimburse.money ELSE 0 END) as ''' + @jxname + ''' '
set @i = @i + 1
fetch next from c into @jxname
end
close c
deallocate c
set @tmpsql = ' select emsmperform.fyid as 费用ID, emsexpenss.fyname as 费用类型 ' + @tmpsql +
      ' FROM emsexpenss RIGHT OUTER JOIN
      emsmperform ON emsexpenss.fyid = emsmperform.fyid RIGHT OUTER JOIN
      emsreimburse ON emsmperform.orno = emsreimburse.porno LEFT OUTER JOIN
      emsdealer RIGHT OUTER JOIN
      emswldw ON emsdealer.jxid = emswldw.jxid ON
      emsmperform.wlid = emswldw.wlid
WHERE (emsreimburse.reidate BETWEEN ''' + cast(@rstime as nvarchar(10)) + ''' AND ''' + cast(@retime as nvarchar(10)) + ''') AND
      (emsdealer.depid = ''' + @depid + ''')
GROUP BY emsmperform.fyid, emsexpenss.fyname '
execute sp_executesql @tmpsql
--select @tmpsql as 'test'