create table #temp(num int)
insert #temp values
(2),(3),(11),(12),(13),(31),(33),(34),(35),(42);
select * from #temp
/*
num
-----------
2
3
11
12
13
31
33
34
35
42
*/
| begin_num | end_num |
| 2 | 3 |
| 11 | 13 |
| 31 | 31 |
| 33 | 35 |
| 42 | 42 |
| begin_num | end_num |
| 4 | 10 |
| 14 | 30 |
| 32 | 32 |
| 36 | 41 |
--螻襴暑語
select
min(num) begin_num
, max(num) end_num
from (
select
num
, row_number() over(order by num) - num grp
from #temp
) t
group by
grp
order by 1
/*
begin_num end_num
----------- -----------
2 3
11 13
31 31
33 35
42 42
*/
--螳蟆覓語
;with temp
as
(
select
num
, row_number() over(order by num) rownum
from #temp
)
select
a.num + 1 begin_num
, b.num - 1 end_num
from temp a
inner join temp b
on a.rownum = b.rownum - 1
where b.num - a.num > 1
/*
begin_num end_num
----------- -----------
4 10
14 30
32 32
36 41
*/
use tempdb
go
create table [dbo].[t](
[rowseq_num] [bigint] null,
[mynjuk] [decimal](25, 2) null,
[dnum] [int] null
)
insert t(rowseq_num,mynjuk,dnum) values('11','630.00','18')
insert t(rowseq_num,mynjuk,dnum) values('12','315.00','0')
insert t(rowseq_num,mynjuk,dnum) values('13','945.00','0')
insert t(rowseq_num,mynjuk,dnum) values('14','162.00','0')
insert t(rowseq_num,mynjuk,dnum) values('15','71.50','19')
insert t(rowseq_num,mynjuk,dnum) values('16','39.00','0')
insert t(rowseq_num,mynjuk,dnum) values('17','156.00','0')
insert t(rowseq_num,mynjuk,dnum) values('18','1036.00','20')
insert t(rowseq_num,mynjuk,dnum) values('19','396.00','21')
select
b.min_seq
, b.max_seq
, sum(a.mynjuk) sum_mynjuk
from t a
inner join (
select
min(rowseq_num) min_seq
, case when min(rowseq_num) = max(rowseq_num) then max(rowseq_num) else max(rowseq_num)-1 end max_seq
from t
cross join (select 1 seq union all select 2) b
where dnum <> 0
group by
case when b.seq = 2 then dnum - 1 else dnum end
having min(case when b.seq = 1 then rowseq_num end) is not null
) b
on a.rowseq_num between b.min_seq and b.max_seq
group by
b.min_seq
, b.max_seq
select
min(rowseq_num) min_seq
, max(rowseq_num) max_seq
, sum(mynjuk) sum_val
from (
select
rowseq_num
, mynjuk
, dnum
, case
when grp = 1 then row_number() over(partition by grp order by rownum)
else rownum - row_number() over(partition by grp order by rownum)
end grp
from (
select
rowseq_num
, mynjuk
, dnum
, case when dnum = 0 then 0 else 1 end grp
, row_number() over(order by (select 1)) rownum
from t
) t
) t
group by
grp
order by 1
with data(val)
as
(
select 0 val union all
select -1 union all
select -1 union all
select -2 union all
select 0 union all
select -300 union all
select 0 union all
select -1 union all
select -500 union all
select 0 union all
select -1 union all
select -100 union all
select 0 union all
select -1 union all
select -1
), rs
as
(
select
val
, case when val <= -30 then 1 else 0 end grp
, row_number() over(order by (select 1)) seq
from data
)
select
val
, grp
, seq
, row_number() over(partition by grp order by seq) grp_seq
, seq - row_number() over(partition by grp order by seq) [seq - grp_seq]
, case
when grp = 1 then row_number() over(partition by grp order by seq)
else seq - row_number() over(partition by grp order by seq)
end grp
from rs
order by seq
/*
val grp seq grp_seq seq - grp_seq grp
----------- ----------- -------------------- -------------------- -------------------- --------------------
0 0 1 1 0 0
-1 0 2 2 0 0
-1 0 3 3 0 0
-2 0 4 4 0 0
0 0 5 5 0 0
-300 1 6 1 5 1 --> 蠍磯
0 0 7 6 1 1
-1 0 8 7 1 1
-500 1 9 2 7 2
0 0 10 8 2 2
-1 0 11 9 2 2
-100 1 12 3 9 3
0 0 13 10 3 3
-1 0 14 11 3 3
-1 0 15 12 3 3
*/
;with data(val)
as
(
select 0 val union all
select -1 union all
select -1 union all
select -2 union all
select 0 union all
select -300 union all
select 0 union all
select -1 union all
select -500 union all
select 0 union all
select -1 union all
select -100 union all
select 0 union all
select -1 union all
select -1
), rs
as
(
select
val
, case when val <= -30 then 1 else 0 end grp
, row_number() over(order by (select 1)) seq
from data
)
select
val
, grp
, seq
, row_number() over(partition by grp order by seq) grp_seq
, seq - row_number() over(partition by grp order by seq) [seq - grp_seq]
, case
when grp = 1 then row_number() over(partition by grp order by seq)-1 --蠍郁 るジ 覿覿
else seq - row_number() over(partition by grp order by seq)
end grp
from rs
order by seq
/*
val grp seq grp_seq seq - grp_seq grp
----------- ----------- -------------------- -------------------- -------------------- --------------------
0 0 1 1 0 0
-1 0 2 2 0 0
-1 0 3 3 0 0
-2 0 4 4 0 0
0 0 5 5 0 0
-300 1 6 1 5 0 ---> 蠍郁讌
0 0 7 6 1 1
-1 0 8 7 1 1
-500 1 9 2 7 1
0 0 10 8 2 2
-1 0 11 9 2 2
-100 1 12 3 9 2
0 0 13 10 3 3
-1 0 14 11 3 3
-1 0 15 12 3 3
*/