请大家修改一段SQL语句(2)

Question

前面发的那个帖子不对：
http://community.csdn.net/Expert/topic/5085/5085553.xml?temp=.6611444
从新再发一个首先建立一个测试表：
-------------------
if object_id('tablename') is not null drop table tablename
select cast('2006-01-01' as datetime) as dVoucherDate, 'adasfdasdfasd' as sFullDesc,
    cast(20.00 as numeric(18, 2)) as lLend, cast(null as numeric(18, 2)) as lLoad
into tablename
union select '2006-01-01', '......', null, 14.00
union select '2006-01-01', '......', null, 14.00
union select '2006-01-04', '......', 10.00, null
union select '2006-01-04', '......', null, 14.00
union select '2006-01-05', '......', null, 14.00
union select '2006-01-02', '......', 25.00, null
union select '2006-01-03', '......', 45.00, null
union select '2006-02-01', '......', null, 10.00
union select '2006-02-05', '......', 20.00, null
union select '2006-02-05', '......', null, 10.00
union select '2006-02-07', '......', 20.00, null
union select '2006-02-07', '......', null, 10.00
union select '2006-02-07', '......', 20.00, null
union select '2006-02-01', '......', null, 10.00
union select '2006-02-01', '......', 20.00, null
union select '2006-03-01', '......', null, 150.00
union select '2006-03-02', '......', 20.00, null
union select '2006-03-03', '......', 14.00, null
union select '2006-03-01', '......', null, 150.00
union select '2006-03-02', '......', 20.00, null
union select '2006-03-03', '......', 14.00, null
union select '2006-03-01', '......', null, 150.00
union select '2006-03-02', '......', 20.00, null
union select '2006-03-03', '......', 14.00, null 测试代码：
---------
declare @lBaband int
declare @lAount numeric(8,2)
set @lBaband=1
set @lAount=20select dVoucherDate, sFullDesc, lLend, lLoad, bBaland, Amount
from (select convert(varchar(10), dVoucherDate, 121) as sOrder, --排序字段
          dVoucherDate, sFullDesc, lLend, lLoad,
          cast(null as varchar(2)) as bBaland, cast(null as numeric(18, 2)) as Amount
      from tablename
      union
      select dVoucherDate, null, '本月合计',
          lLend, lLoad, (case (case when @lBaband=1 then sign(sumlLend - sumlLoad + @lAount) else sign(sumlLend - sumlLoad - @lAount) end) when -1 then '贷' when 0 then '平' when 1 then '借' end),
          case when @lBaband=1 then abs(sumlLend - sumlLoad + @lAount) else abs(sumlLend - sumlLoad - @lAount) end
      from (select distinct convert(varchar(7), a.dVoucherDate, 121) + '-32' as dVoucherDate,
              (select sum(isnull(lLend, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLend,
              (select sum(isnull(lLoad, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLoad,
              (select sum(isnull(lLend, 0)) from tablename where convert(varchar(10), dVoucherDate, 121) < convert(varchar(7), a.dVoucherDate, 121) + '-32') as sumlLend,
               (select sum(isnull(lLoad, 0)) from tablename where convert(varchar(10), dVoucherDate, 121) < convert(varchar(7), a.dVoucherDate, 121) + '-32') as sumlLoad
            from tablename a) aa
     union 
     select convert(varchar(7), '0001-01-01', 121),null,'上年结转',0,0,(case when @lBaband=1 then '借' else '贷' end) as bBaland,@lAount as Amount
     from tablename ) aaaorder by sOrder得到结果：
---------
 dVoucherDate        sFullDesc      lLend        lLoad        bBaland        Amount
  上年结转 .00 .00 借 20.00
2006-01-01       ......   14.00    
2006-01-01      adasfdasdfasd 20.00      
2006-01-02       ...... 25.00      
2006-01-03       ...... 45.00      
2006-01-04       ......   14.00    
2006-01-04       ...... 10.00      
2006-01-05       ......   14.00    
  本月合计 100.00 42.00 借 78.00
2006-02-01       ......   10.00    
2006-02-01       ...... 20.00      
2006-02-05       ......   10.00    
2006-02-05       ...... 20.00      
2006-02-07       ......   10.00    
2006-02-07       ...... 20.00      
  本月合计 60.00 30.00 借 108.00
2006-03-01       ......   150.00    
2006-03-02       ...... 20.00      
2006-03-03       ...... 14.00      
  本月合计 34.00 150.00 贷 8.00 _____________________________________________________________________________________现在我想给测试代码传入一个参数@PageSize表示页的行数大小（但暂时还没有分页），使数据集多出一个列PageNo
和一些刚好在分页处的行，如@PageSize=8，得到的结果：
dVoucherDate          sFullDesc        lLend     lLoad     bBaland      Amount      PageNo
  上年结转 .00 .00 借 20.00 1
2006-01-01       ......   14.00     1
2006-01-01      adasfdasdfasd 20.00       1
2006-01-02       ...... 25.00       1
2006-01-03       ...... 45.00       1
2006-01-04       ......   14.00     1
2006-01-04       ...... 10.00       1
    过次页 100.00 28.00 借 92.00 1
    承上页 100.00 28.00 借 92.00 2
2006-01-05       ......   14.00     2
  本月合计 100.00 42.00 借 78.00 2
2006-02-01       ......   10.00     2
2006-02-01       ...... 20.00       2
2006-02-05       ......   10.00     2
2006-02-05       ...... 20.00       2
    过次页 40.00 20.00 借 98.00 2
    承上页 40.00 20.00 借 98.00 3
2006-02-07       ......   10.00     3
2006-02-07       ...... 20.00       3
  本月合计 60.00 30.00 借 108.00 3
2006-03-01       ......   150.00     3
2006-03-02       ...... 20.00       3
2006-03-03       ...... 14.00       3
  本月合计 34.00 150.00 贷 8.00 3
注：如果页末刚好是“本月合计”的行，也就是这个月刚好在这页结束，那就不要‘过次页’，‘承上页’的行了，
我知道这个可能要用游标的方法做但我游标不是很熟悉，希望大家帮帮忙。

Answer 1

可以将参数传递 到存储过程里，结合存储过程与页面代码进行编写分页存储我的方法是先将查询出来的数据存放在自动增量虚拟表里，然后对其实进行操作
create table #temp
(
TID int identity(1,1)
,name
)
insert into #temp(name)
select a.name from sysobjects a ,sysobjects bselect * from #temp
where TID between ((@ipageindex-1)*@ipagesize+1) and (@ipageindex*@ipagesize)
至于这些全部是变量。。
具体格式如上。希望能给楼主一些启发。。

Answer 2

添上承上頁功能declare @lBaband int
declare @lAount numeric(8,2)
set @lBaband=1
set @lAount=20
select IDENTITY(int, 1,1) as id_num, dVoucherDate, sFullDesc, lLend, lLoad, bBaland, Amount into #temp
from (select convert(varchar(10), dVoucherDate, 121) as sOrder, --排序字段
          dVoucherDate, sFullDesc, lLend, lLoad,
          cast(null as varchar(2)) as bBaland, cast(null as numeric(18, 2)) as Amount
      from tablename
      union
      select dVoucherDate, null, '本月合計',
          lLend, lLoad, (case (case when @lBaband=1 then sign(sumlLend - sumlLoad + @lAount) else sign(sumlLend - sumlLoad - @lAount) end) when -1 then '貸' when 0 then '平' when 1 then '借' end),
          case when @lBaband=1 then abs(sumlLend - sumlLoad + @lAount) else abs(sumlLend - sumlLoad - @lAount) end
      from (select distinct convert(varchar(7), a.dVoucherDate, 121) + '-32' as dVoucherDate,
              (select sum(isnull(lLend, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLend,
              (select sum(isnull(lLoad, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLoad,
              (select sum(isnull(lLend, 0)) from tablename where convert(varchar(10), dVoucherDate, 121) < convert(varchar(7), a.dVoucherDate, 121) + '-32') as sumlLend,
               (select sum(isnull(lLoad, 0)) from tablename where convert(varchar(10), dVoucherDate, 121) < convert(varchar(7), a.dVoucherDate, 121) + '-32') as sumlLoad
            from tablename a) aa
     union 
     select convert(varchar(7), '0001-01-01', 121),null,'上年結轉',0,0,(case when @lBaband=1 then '借' else '貸' end) as bBaland,@lAount as Amount
     from tablename ) aaa
order by sOrder--drop table #temp
select * ,(id_num-1)/8+1,case when id_num=((id_num-1)/8+1)*8 and sFullDesc<>'本月合計' then '過次頁'   when  id_num=((id_num-2)/8+1)*8+1 then '承上頁' end
from #temp這個隻是一思路而已，並非樓主要的最終結果

Answer 3

出来了，用游标实现，结果就有那个 上年结转 的偏差if object_id('tablename') is not null drop table tablename
select cast('2006-01-01' as datetime) as dVoucherDate, 'adasfdasdfasd' as sFullDesc,
    cast(20.00 as numeric(18, 2)) as lLend, cast(null as numeric(18, 2)) as lLoad
into tablename
union select '2006-01-01', '......', null, 14.00
union select '2006-01-04', '......', 10.00, null
union select '2006-01-04', '......', null, 14.00
union select '2006-01-05', '......', null, 14.00
union select '2006-01-02', '......', 25.00, null
union select '2006-01-03', '......', 45.00, null
union select '2006-02-01', '......', null, 10.00
union select '2006-02-01', '......', 20.00, null
union select '2006-02-05', '......', 20.00, null
union select '2006-02-05', '......', null, 10.00
union select '2006-02-07', '......', 20.00, null
union select '2006-02-07', '......', null, 10.00
union select '2006-03-01', '......', null, 150.00
union select '2006-03-02', '......', 20.00, null
union select '2006-03-03', '......', 14.00, null
declare @PageSize int
set @PageSize = 6                  --每页的记录条数
---------------------------------------------------------
declare @dVoucherDate datetime, @sFullDesc varchar(20), @lLend numeric(18, 2), @lLoad numeric(18, 2)
declare @sMonth varchar(7), @sumLend numeric(18, 2), @sumLoad numeric(18, 2), @Amount numeric(18, 2), @n int
select @sumLend = 0, @sumLoad = 0, @Amount = 0, @n = 0
if object_id('tempdb..#') is not null drop table #
select identity(int, 1, 1) as [id], *, cast(null as numeric(18, 2)) as Amount
into # from tablename where 1 = 0
declare testcur cursor for select * from tablename order by dVoucherDate
open testcur
fetch next from testcur into @dVoucherDate, @sFullDesc, @lLend, @lLoad
set @sMonth = convert(varchar(7), @dVoucherDate, 121)
while @@fetch_status = 0
begin
  set @n = @n + 1
  if convert(varchar(7), @dVoucherDate, 121) <> @sMonth   --添加本月合计
  begin
    set @Amount = @Amount + @sumLend - @sumLoad
    insert into #(dVoucherDate, sFullDesc, lLend, lLoad, Amount)
    select null, '本月合计', @sumLend, @sumLoad, @Amount
    set @sMonth = convert(varchar(7), @dVoucherDate, 121)
    set @sumLend = 0
    set @sumLoad = 0
  end
  else if @n < @PageSize                                 --添加原记录
  begin
    insert into #(dVoucherDate, sFullDesc, lLend, lLoad, Amount)
    select @dVoucherDate, @sFullDesc, @lLend, @lLoad, null
    select @sumLend = @sumLend + isnull(@lLend, 0), @sumLoad = @sumLoad + isnull(@lLoad, 0)
    fetch next from testcur into @dVoucherDate, @sFullDesc, @lLend, @lLoad
  end
  else 
  begin                                                  --添加两条过页记录
    insert into #(dVoucherDate, sFullDesc, lLend, lLoad, Amount)
    select null, '过次页', @sumLend, @sumLoad, @sumLend - @sumLoad
    union select null, '承上页', @sumLend, @sumLoad, @sumLend - @sumLoad
    set @n = @n + 1
  end
  set @n = @n % @PageSize
end
close testcur
deallocate testcur
insert into #(dVoucherDate, sFullDesc, lLend, lLoad, Amount)
select null, @sMonth + '合计', @sumLend, @sumLoad, @sumLend - @sumLoad
select dVoucherDate, sFullDesc, lLend, lLoad, case sign(Amount) when -1 then '贷' 
    when 0 then '平' when 1 then '借' end as bBaland, abs(Amount) as Amount,
    ([id] - 1) / @PageSize + 1 as PageNo
from #
order by [id]
/*
dVoucherDate            sFullDesc     lLend                lLoad                bBaland Amount               PageNo      
----------------------- ------------- -------------------- -------------------- ------- -------------------- ----------- 
2006-01-01 00:00:00.000 ......        NULL                 14.00                NULL    NULL                 1
2006-01-01 00:00:00.000 adasfdasdfasd 20.00                NULL                 NULL    NULL                 1
2006-01-02 00:00:00.000 ......        25.00                NULL                 NULL    NULL                 1
2006-01-03 00:00:00.000 ......        45.00                NULL                 NULL    NULL                 1
2006-01-04 00:00:00.000 ......        NULL                 14.00                NULL    NULL                 1
NULL                    承上页           90.00                28.00                借       62.00                1
NULL                    过次页           90.00                28.00                借       62.00                2
2006-01-04 00:00:00.000 ......        10.00                NULL                 NULL    NULL                 2
2006-01-05 00:00:00.000 ......        NULL                 14.00                NULL    NULL                 2
NULL                    本月合计          100.00               42.00                借       58.00                2
2006-02-01 00:00:00.000 ......        NULL                 10.00                NULL    NULL                 2
NULL                    承上页           .00                  10.00                贷       10.00                2
NULL                    过次页           .00                  10.00                贷       10.00                3
2006-02-01 00:00:00.000 ......        20.00                NULL                 NULL    NULL                 3
2006-02-05 00:00:00.000 ......        NULL                 10.00                NULL    NULL                 3
2006-02-05 00:00:00.000 ......        20.00                NULL                 NULL    NULL                 3
2006-02-07 00:00:00.000 ......        NULL                 10.00                NULL    NULL                 3
NULL                    承上页           40.00                30.00                借       10.00                3
NULL                    过次页           40.00                30.00                借       10.00                4
2006-02-07 00:00:00.000 ......        20.00                NULL                 NULL    NULL                 4
NULL                    本月合计          60.00                30.00                借       88.00                4
2006-03-01 00:00:00.000 ......        NULL                 150.00               NULL    NULL                 4
2006-03-02 00:00:00.000 ......        20.00                NULL                 NULL    NULL                 4
NULL                    承上页           20.00                150.00               贷       130.00               4
NULL                    过次页           20.00                150.00               贷       130.00               5
2006-03-03 00:00:00.000 ......        14.00                NULL                 NULL    NULL                 5
NULL                    2006-03合计     34.00                150.00               贷       116.00               5*/
drop table #
---------------------------------------------------------
drop table tablename

Answer 4

过次页的顺序反了，改一下
..........
  begin                                                  --添加两条过页记录
    insert into #(dVoucherDate, sFullDesc, lLend, lLoad, Amount)
    select null, '过次页', @sumLend, @sumLoad, @sumLend - @sumLoad
    insert into #(dVoucherDate, sFullDesc, lLend, lLoad, Amount)
    select null, '承上页', @sumLend, @sumLoad, @sumLend - @sumLoad
    set @n = @n + 1
  end
..........