_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 螻襴暑語螳蟆覓語

Contents

1 蠍磯蓋
2 1
3 2


1 蠍磯蓋 #

一危
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_numend_num
23
1113
3131
3335
4242

螳蟆覓語 蟆郁骸
begin_numend_num
410
1430
3232
3641

螻襴暑語
--螻襴暑語
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
*/

2 1 #


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

3 2 #

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
*/
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:52

螻殊れ 語伎 る 蟆企 襷 覦 煙る所 覩碁リ 蠍一 襷豢 讌る蟆 . (伎螻)