业务数据库是sql server 2000
有张缴费表，数据量为百万级别的,
单据号码是不连续的并且会有断号，缺号的，金额也是不一定的，
我为了演示方便把单据号码排了序，金额也大部分固定为168.00单据号码 缴费金额 缴费日期
000001 168.00 2010-01-12
000002 168.00 2010-01-13
000003 168.00 2010-01-14
000004 168.00 2010-02-12
000005 168.00 2010-03-12
000006 168.00 2010-04-12
000007 168.00 2010-03-12
000008 168.00 2010-03-12
000009 168.00 2010-03-12000012 168.00 2010-03-12
000013 168.00 2010-03-12
000014 168.00 2010-03-12
000015 168.00 2010-04-12000023 168.00 2010-09-12000033 100.00 2010-04-12
000034 200.00 2010-03-12
000035 200.00 2010-03-12
现在要做个报表统计，要显示成按单据号码段分开，先得到单据数量，再按每个月分开算出金额，有的单据有的月份是不一有的就不显示的，请问各位大大有好的高效算法或者函数么？单据起始号 单据结束号 单据数量 开具金额 开具月份
000001 000009 9 504.00 1月
168.00 2月
672.00 3月
168.00 4月000012 000015 4 504.00 3月
168.00 4月
000023 000023 1 168.00 9月000033 000035 3 400.00 3月
100.00 4月
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([单据号码] varchar(6),[缴费金额] numeric(5,2),[缴费日期] datetime)
insert [tb]
select '000001',168.00,'2010-01-12' union all
select '000002',168.00,'2010-01-13' union all
select '000003',168.00,'2010-01-14' union all
select '000004',168.00,'2010-02-12' union all
select '000005',168.00,'2010-03-12' union all
select '000006',168.00,'2010-04-12' union all
select '000007',168.00,'2010-03-12' union all
select '000008',168.00,'2010-03-12' union all
select '000009',168.00,'2010-03-12' union all
select '000012',168.00,'2010-03-12' union all
select '000013',168.00,'2010-03-12' union all
select '000014',168.00,'2010-03-12' union all
select '000015',168.00,'2010-04-12' union all
select '000023',168.00,'2010-09-12' union all
select '000033',100.00,'2010-04-12' union all
select '000034',200.00,'2010-03-12' union all
select '000035',200.00,'2010-03-12'
-->查询
;with test1 as(
select px=row_number() over(order by getdate()),*
from [tb] t
where not exists(select * from tb where 单据号码=t.单据号码-1)
),
test2 as(
select px=row_number() over(order by getdate()),*
from [tb] t
where not exists(select * from tb where 单据号码=t.单据号码+1)
),
test3 as(
select a.单据号码 单据起始号,b.单据号码 单据结束号
from test1 a,test2 b
where a.px=b.px
),
test4 as(
select px=row_number() over(partition by b.单据起始号 order by month(a.缴费日期)),
b.单据起始号,b.单据结束号,
count(1) 单据数量,sum(缴费金额) 开具金额,ltrim(month(a.缴费日期))+'月' 开具月份
from tb a
join test3 b on a.单据号码 between b.单据起始号 and 单据结束号
group by b.单据起始号,b.单据结束号,month(缴费日期)
)
select 单据起始号=case when px=1 then 单据起始号 else '' end,
单据结束号=case when px=1 then 单据结束号 else '' end,
单据数量=case when px!=1 then '' else ltrim((select sum(单据数量) from test4 where 单据起始号=t.单据起始号)) end,
开具金额,开具月份
from test4 t/**
单据起始号 单据结束号 单据数量 开具金额 开具月份
------ ------ ------------ --------------------------------------- --------------
000001 000009 9 504.00 1月
168.00 2月
672.00 3月
168.00 4月
000012 000015 4 504.00 3月
168.00 4月
000023 000023 1 168.00 9月
000033 000035 3 400.00 3月
100.00 4月(9 行受影响)
**/
1、连续号码段及统计的SQL我已经写好（是wufeng4552大大帮忙的，见下面）
2、现在就是分月统计 不知道该如何写
3、您的那个网页只是连续号码段的取号
if not object_id('Tempdb..#tmp') is null
drop table #tmp
Go
Create table #tmp([发票号码] nvarchar(6),[缴费金额] decimal(18,2))
Insert #tmp
select N'000001',168.00 union all
select N'000002',168.00 union all
select N'000003',168.00 union all
select N'000004',168.00 union all
select N'000005',168.00 union all
select N'000006',168.00 union all
select N'000007',168.00 union all
select N'000008',168.00 union all
select N'000009',168.00 union all
select N'000012',168.00 union all
select N'000013',168.00 union all
select N'000014',168.00 union all
select N'000015',168.00 union all
select N'000023',168.00 union all
select N'000033',100.00 union all
select N'000034',200.00 union all
select N'000035',200.00
Go
select min(t.[发票号码])发票起始号,
max(t.[发票号码])发票结束号,
count(*)发票张数,
sum([缴费金额])发票合计金额
from(
select [发票号码],
cnt=cast([发票号码] as int)-(select count(*)from #tmp n where m.[发票号码]>n.[发票号码]),
[缴费金额]
from #tmp m
)t group by cnt
/*
发票起始号 发票结束号 发票张数 发票合计金额
------ ------ ----------- ---------------------------------------
000001 000009 9 1512.00
000012 000015 4 672.00
000023 000023 1 168.00
000033 000035 3 500.00(4 row(s) affected)
*/
这位大大 SQL2000不支持with啊，谢谢，有其他好的办法么
select 发票起始号,发票结束号,发票张数,sum(缴费金额) 开具金额,ltrim(month(缴费日期))+'月' 开具月份
from
(
select min(t.[发票号码])发票起始号,
max(t.[发票号码])发票结束号,
count(*)发票张数,
sum([缴费金额])发票合计金额
from(
select [发票号码],
cnt=cast([发票号码] as int)-(select count(*)from tb n where m.[发票号码]>n.[发票号码]),
[缴费金额]
from tb m
)t group by cnt
) a
join tb b on b.发票号码 between a.发票起始号 and a.发票结束号
group by 发票起始号,发票结束号,发票张数,month(缴费日期)
格式可以通过前台程序来调，或者使用临时表
insert [#tb]
select '000001',168.00,'2010-01-12' union all
select '000002',168.00,'2010-01-13' union all
select '000003',168.00,'2010-01-14' union all
select '000004',168.00,'2010-02-12' union all
select '000005',168.00,'2010-03-12' union all
select '000006',168.00,'2010-04-12' union all
select '000007',168.00,'2010-03-12' union all
select '000008',168.00,'2010-03-12' union all
select '000009',168.00,'2010-03-12' union all
select '000012',168.00,'2010-03-12' union all
select '000013',168.00,'2010-03-12' union all
select '000014',168.00,'2010-03-12' union all
select '000015',168.00,'2010-04-12' union all
select '000023',168.00,'2010-09-12' union all
select '000033',100.00,'2010-04-12' union all
select '000034',200.00,'2010-03-12' union all
select '000035',200.00,'2010-03-12'select isnull(a.单据起始号,'') as 单据起始号,isnull(a.单据结束号,'')as 单据结束号,isnull(a.单据数量,'') as 单据数量,b.开具金额,ltrim(b.开具月份)+'月' as 开具月份 from
(
select ID=row_number() over(order by (select 1)),left(right(单据号码,2),1) as type, min(单据号码) as 单据起始号,
max(单据号码) as 单据结束号,count(*) as 单据数量 from #tb group by left(right(单据号码,2),1)
)a right join
(
select ID=row_number() over(partition by left(right(单据号码,2),1) order by left(right(单据号码,2),1)),sum(缴费金额) as 开具金额, left(right(单据号码,2),1) as type ,month(缴费日期)as 开具月份
from #tb group by left(right(单据号码,2),1),month(缴费日期)
)b
on a.type= b.type and b.id =1
/*
单据起始号 单据结束号 单据数量 开具金额 开具月份
------ ------ ----------- --------------------------------------- --------------
000001 000009 9 504.00 1月
0 168.00 2月
0 672.00 3月
0 168.00 4月
000012 000015 4 504.00 3月
0 168.00 4月
000023 000023 1 168.00 9月
000033 000035 3 400.00 3月
0 100.00 4月(9 row(s) affected)
*/
select isnull(a.单据起始号,'') as 单据起始号,isnull(a.单据结束号,'')as 单据结束号,isnull(ltrim(a.单据数量),'') as 单据数量,b.开具金额,ltrim(b.开具月份)+'月' as 开具月份 from
(
select ID=row_number() over(order by (select 1)),left(right(单据号码,2),1) as type, min(单据号码) as 单据起始号,
max(单据号码) as 单据结束号,count(*) as 单据数量 from #tb group by left(right(单据号码,2),1)
)a right join
(
select ID=row_number() over(partition by left(right(单据号码,2),1) order by left(right(单据号码,2),1)),sum(缴费金额) as 开具金额, left(right(单据号码,2),1) as type ,month(缴费日期)as 开具月份
from #tb group by left(right(单据号码,2),1),month(缴费日期)
)b
on a.type= b.type and b.id =1
稍微做下修改
2000下先插入2个临时表然后在又连接----SQL 2000select left(right(单据号码,2),1) as type, min(单据号码) as 单据起始号,
max(单据号码) as 单据结束号,count(*) as 单据数量 into #t1 from #tb group by left(right(单据号码,2),1)
select ID=identity(int,1,1),sum(缴费金额) as 开具金额, left(right(单据号码,2),1) as type ,month(缴费日期)as 开具月份 into #t2
from #tb group by left(right(单据号码,2),1),month(缴费日期)select isnull(a.单据起始号,'') as 单据起始号,isnull(a.单据结束号,'')as 单据结束号,isnull(ltrim(a.单据数量),'') as 单据数量,b.开具金额,ltrim(b.开具月份)+'月' as 开具月份
from #t1 a right join #t2 b on a.type = b.type and b.id =(select min(id) from #t2 where type = b.type) order by b.type , b.开具月份
你的月份金额应该和连续字段没什么关系吧？
select sum(缴费金额), datepart(mm,缴费日期) as 月份 from tablename group by 月份
create table [#tb]([单据号码] varchar(6),[缴费金额] numeric(5,2),[缴费日期] datetime)
insert [#tb]
select '000001',168.00,'2010-01-12' union all
select '000002',168.00,'2010-01-13' union all
select '000003',168.00,'2010-01-14' union all
select '000004',168.00,'2010-02-12' union all
select '000005',168.00,'2010-03-12' union all
select '000006',168.00,'2010-04-12' union all
select '000007',168.00,'2010-03-12' union all
select '000008',168.00,'2010-03-12' union all
select '000009',168.00,'2010-03-12' union all
select '000012',168.00,'2010-03-12' union all
select '000013',168.00,'2010-03-12' union all
select '000014',168.00,'2010-03-12' union all
select '000015',168.00,'2010-04-12' union all
select '000023',168.00,'2010-09-12' union all
select '000033',100.00,'2010-04-12' union all
select '000034',200.00,'2010-03-12' union all
select '000035',200.00,'2010-03-12'
---step 1 insert 临时表
select *,identity(int,1,1)as id,0 as type into #ta from #tb ---按照连续单据号这个规则来更新临时表
declare @num int
set @num =0
update #ta set type = @num ,
@num = (case when cast(a.单据号码 as int) <> (select cast(单据号码 as int)+1 from #ta where id = a.id-1) then @num+1 else @num end)
from #ta a---创建查询
select isnull(a.单据起始号,'') as 单据起始号,isnull(a.单据结束号,'')as 单据结束号,isnull(ltrim(a.单据数量),'') as 单据数量,b.开具金额,ltrim(b.开具月份)+'月' as 开具月份
from
(select type, min(单据号码) as 单据起始号,
max(单据号码) as 单据结束号,count(*) as 单据数量 from #ta group by type) a
right join
(select sum(缴费金额) as 开具金额, type ,month(缴费日期)as 开具月份 ,min(id) as id
from #ta group by type,month(缴费日期) )
b on a.type = b.type and b.id =(select min(id) from #ta where type = b.type) order by b.type , b.开具月份--释放临时表 drop table #ta
drop table #tb