票据类型编码 张数 起号 止号
A01 100 1 100
A01 100 101 200
A01 100 201 300
A01 100 401 500
A01 100 501 600
-----------------------------------
结果应该为:
票据类型编码 张数 起号 止号
A01 300 1 300
A01 200 401 600
-----------------------------------
求诸位大大指教 谢谢
A01 100 1 100
A01 100 101 200
A01 100 201 300
A01 100 401 500
A01 100 501 600
-----------------------------------
结果应该为:
票据类型编码 张数 起号 止号
A01 300 1 300
A01 200 401 600
-----------------------------------
求诸位大大指教 谢谢
declare @T table
([票据类型编码] varchar(3),[张数] int,[起号] int,[止号] int)
insert @T
select 'A01',100,1,100 union all
select 'A01',100,101,200 union all
select 'A01',100,201,300 union all
select 'A01',100,401,500 union all
select 'A01',100,501,600;with t1 as
(
select (row_number() over (order by (select 1)))*100 as id,* from @T
)
select
max([票据类型编码]) as [票据类型编码],
sum([张数]) as [张数],
min([起号]) as [起号],
max([止号]) as [止号]
from t1 group by [止号]-id
/*
票据类型编码 张数 起号 止号
------ ----------- ----------- -----------
A01 300 1 300
A01 200 401 600
*/
楼上的有漏洞-- 这样的数据统计有问题
insert @T
select 'A01',100,1,100 union all
select 'A01',55,155,200 union all
select 'A01',100,201,300 union all
select 'A01',100,401,500 union all
select 'A01',100,501,600
--trydeclare @t table (t char(3),n int , sn int ,en int)
insert into @t
select 'A01', 100 ,1,100 union all
select 'A01', 55 ,155, 200 union all
select 'A01', 100 ,201, 300 union all
select 'A01', 100 ,401, 500 union all
select 'A01', 100 ,501, 600;with T1 as
(
select row_number()over (order by sn) as Px, * from @t
),t2 as
(
select 0 as px from T1 where px = 1 union all
select a.px from t1 a left join t1 b on a.px = b.px -1
where (b.sn-a.en <> 1) or (b.sn-a.en is null)
),t3 as
(
select row_number()over (order by px) as px, px as px1,(select top 1 px from t2 b where a.px <=b.px)+1 as px2 from t2 a
),t4 as
(
select a.px, a.px2 as px1,b.px1 as px2 from t3 a left join t3 b on a.px = b.px -1 where b.px1 is not null
)
select a.px,
max(t) as [票据类型编码],
sum(n) as [张数],
min(sn) as [起号],
max(en) as [止号]
from t4 a left join t1 b on b.px >= a.px1 and b.px <=a.px2
group by a.px
/*
(5 行受影响)
px 票据类型编码 张数 起号 止号
-------------------- ------ ----------- ----------- -----------
1 A01 100 1 100
2 A01 155 155 300
3 A01 200 401 600(3 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([编码] VARCHAR(3),[张数] INT,[起号] INT,[止号] INT)
INSERT [tb]
SELECT 'A01',100,1,100 UNION ALL
SELECT 'A01',100,101,200 UNION ALL
SELECT 'A01',100,201,300 UNION ALL
SELECT 'A01',100,401,500 UNION ALL
SELECT 'A01',100,501,600
--------------开始查询--------------------------
;WITH cte AS
(
SELECT row_id = ROW_NUMBER() OVER ( PARTITION BY [编码] ORDER BY [起号] ) * 100, *
FROM tb
)
SELECT MAX([编码]) AS [编码], SUM([张数]) AS [张数], MIN([起号]) AS [起号], MAX([止号]) AS [止号]
FROM cte
GROUP BY [编码], [止号] - row_id
/*
编码 张数 起号 止号
---- ----------- ----------- -----------
A01 300 1 300
A01 200 401 600(2 行受影响)*/
票据类型编码 张数 起号 止号 月份
A01 100 1 100 1
A01 100 101 200 1
B01 100 201 300 1
A01 100 401 500 1
A01 100 501 600 2
B01 120 311 330 1
-----------------------------------
结果应该为:
票据类型编码 张数 起号 止号 月份
A01 300 1 200 1
A01 100 401 500 1
A01 100 501 600 2
B01 100 201 300 1
B01 120 311 330 1