建立测试表:
if object_id('tablename') is not 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( as numeric(18, 2)) as lLoad
into tablename
union select '2006-01-01', '......', , 14.00
union select '2006-01-01', '......', , 14.00
union select '2006-01-04', '......', 10.00,
union select '2006-01-04', '......', , 14.00
union select '2006-01-05', '......', , 14.00
union select '2006-01-02', '......', 25.00,
union select '2006-01-03', '......', 45.00,
union select '2006-02-01', '......', , 10.00
union select '2006-02-05', '......', 20.00,
union select '2006-02-05', '......', , 10.00
union select '2006-02-07', '......', 20.00,
union select '2006-02-07', '......', , 10.00
union select '2006-02-07', '......', 20.00,
union select '2006-02-01', '......', , 10.00
union select '2006-02-01', '......', 20.00,
union select '2006-03-01', '......', , 150.00
union select '2006-03-02', '......', 20.00,
union select '2006-03-03', '......', 14.00,
union select '2006-03-01', '......', , 150.00
union select '2006-03-02', '......', 20.00,
union select '2006-03-03', '......', 14.00,
union select '2006-03-01', '......', , 150.00
union select '2006-03-02', '......', 20.00,
union select '2006-03-03', '......', 14.00, 测试代码:
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( as varchar(2)) as bBaland, cast( as numeric(18, 2)) as Amount
from tablename
union
select dVoucherDate, , '本月合计',
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(is (lLend, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLend,
(select sum(is (lLoad, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLoad,
(select sum(is (lLend, 0)) from tablename where convert(varchar(10), dVoucherDate, 121) < convert(varchar(7), a.dVoucherDate, 121) + '-32') as sumlLend,
(select sum(is (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), ,'上年结转',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
注:如果页末刚好是“本月合计”的行,也就是这个月刚好在这页结束,那就不要‘过次页’,‘承上页’的行了,
我知道这个可能要用游标的方法做但我游标不是很熟悉,希望大家帮帮忙。
if object_id('tablename') is not 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( as numeric(18, 2)) as lLoad
into tablename
union select '2006-01-01', '......', , 14.00
union select '2006-01-01', '......', , 14.00
union select '2006-01-04', '......', 10.00,
union select '2006-01-04', '......', , 14.00
union select '2006-01-05', '......', , 14.00
union select '2006-01-02', '......', 25.00,
union select '2006-01-03', '......', 45.00,
union select '2006-02-01', '......', , 10.00
union select '2006-02-05', '......', 20.00,
union select '2006-02-05', '......', , 10.00
union select '2006-02-07', '......', 20.00,
union select '2006-02-07', '......', , 10.00
union select '2006-02-07', '......', 20.00,
union select '2006-02-01', '......', , 10.00
union select '2006-02-01', '......', 20.00,
union select '2006-03-01', '......', , 150.00
union select '2006-03-02', '......', 20.00,
union select '2006-03-03', '......', 14.00,
union select '2006-03-01', '......', , 150.00
union select '2006-03-02', '......', 20.00,
union select '2006-03-03', '......', 14.00,
union select '2006-03-01', '......', , 150.00
union select '2006-03-02', '......', 20.00,
union select '2006-03-03', '......', 14.00, 测试代码:
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( as varchar(2)) as bBaland, cast( as numeric(18, 2)) as Amount
from tablename
union
select dVoucherDate, , '本月合计',
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(is (lLend, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLend,
(select sum(is (lLoad, 0)) from tablename where convert(varchar(7), dVoucherDate, 121) = convert(varchar(7), a.dVoucherDate, 121)) as lLoad,
(select sum(is (lLend, 0)) from tablename where convert(varchar(10), dVoucherDate, 121) < convert(varchar(7), a.dVoucherDate, 121) + '-32') as sumlLend,
(select sum(is (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), ,'上年结转',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
注:如果页末刚好是“本月合计”的行,也就是这个月刚好在这页结束,那就不要‘过次页’,‘承上页’的行了,
我知道这个可能要用游标的方法做但我游标不是很熟悉,希望大家帮帮忙。
问题看上去是有点长,但不是很复杂的,希望大家看看,
对了,我前几天发了一个帖子:
http://community.csdn.net/Expert/topic/5079/5079981.xml?temp=.3157312
这个问题是解决了,而我这里的意思是要求是希望再改一些内容。
建立测试表:
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
http://community.csdn.net/Expert/topic/5086/5086282.xml?temp=.524975
谢谢大家了
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 '過次頁' end
from #tempselect * ,case when id_num=pageno*8 and sFullDesc<>'本月合計' then '過次頁' end
from
(select * ,(id_num-1)/8+1 as pageno
from #temp) a其中的數字8可以用參數來替代
功能還沒完全達到樓主的要求
from
(select * ,(id_num-1)/8+1 as pageno
from #temp) a
這些是不要的
一不注意就給貼上來了
2 2006-01-01 00:00:00.000 ...... NULL 14.00 NULL NULL 1 NULL
3 2006-01-01 00:00:00.000 adasfdasdfasd 20.00 NULL NULL NULL 1 NULL
4 2006-01-02 00:00:00.000 ...... 25.00 NULL NULL NULL 1 NULL
5 2006-01-03 00:00:00.000 ...... 45.00 NULL NULL NULL 1 NULL
6 2006-01-04 00:00:00.000 ...... NULL 14.00 NULL NULL 1 NULL
7 2006-01-04 00:00:00.000 ...... 10.00 NULL NULL NULL 1 NULL
8 2006-01-05 00:00:00.000 ...... NULL 14.00 NULL NULL 1 過次頁
9 NULL 本月合計 100.00 42.00 借 78.00 2 NULL
10 2006-02-01 00:00:00.000 ...... NULL 10.00 NULL NULL 2 NULL
11 2006-02-01 00:00:00.000 ...... 20.00 NULL NULL NULL 2 NULL
12 2006-02-05 00:00:00.000 ...... NULL 10.00 NULL NULL 2 NULL
13 2006-02-05 00:00:00.000 ...... 20.00 NULL NULL NULL 2 NULL
14 2006-02-07 00:00:00.000 ...... NULL 10.00 NULL NULL 2 NULL
15 2006-02-07 00:00:00.000 ...... 20.00 NULL NULL NULL 2 NULL
16 NULL 本月合計 60.00 30.00 借 108.00 2 NULL
17 2006-03-01 00:00:00.000 ...... NULL 150.00 NULL NULL 3 NULL
18 2006-03-02 00:00:00.000 ...... 20.00 NULL NULL NULL 3 NULL
19 2006-03-03 00:00:00.000 ...... 14.00 NULL NULL NULL 3 NULL
20 NULL 本月合計 34.00 150.00 貸 8.00 3 NULL結果
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
..........
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
..........