22,209
社区成员
发帖
与我相关
我的任务
分享
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(发票号码),发票结束号=max(发票号码),
发票张数=COUNT(*),发票合计金额=sum([缴费金额])
from #tmp group by (CAST([发票号码] as bigint)-1) /10
/*
发票起始号 发票结束号 发票张数 发票合计金额
------ ------ ----------- ---------------------------------------
000001 000009 9 1512.00
000012 000015 4 672.00
000023 000023 1 168.00
000033 000035 3 500.00
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)
*/