oracle:
select
t.vul_id,
t.deal_status,
t.create_time,
row_number() over(partition by t.vul_id order by t.create_time desc) lastnum,
row_number() over(partition by t.vul_id,t.deal_status order by t.create_time asc) finishnum
from VUL_TASK_DEAL t where t.deal_source = 1
order by t.deal_id
mysql:
select
t.vul_id,
t.deal_status,
t.create_time,
tt.lastnum,
ttt.finishnum
from VUL_TASK_DEAL t
left join (
select
m.deal_id,
@st_row:= case when @st_type=m.vul_id THEN @st_row+1 else 1 end as lastnum,
@st_type:=m.vul_id as st_type
from (
select s.deal_id, s.vul_id, s.deal_status, s.create_time
from VUL_TASK_DEAL s
where s.deal_source = 1
ORDER BY s.vul_id asc, s.create_time desc
) m, (select @st_row:=0 ,@st_type:='') st
) tt on t.deal_id = tt.deal_id
left join (
select
m.deal_id,
@st_row:= case when @st_type=concat(m.vul_id, '-', m.deal_status) THEN @st_row+1 else 1 end as finishnum,
@st_type:=concat(m.vul_id, '-', m.deal_status) as st_type
from (
select s.deal_id, s.vul_id, s.deal_status, s.create_time
from VUL_TASK_DEAL s
where s.deal_source = 1
ORDER BY s.vul_id, s.deal_status asc, s.create_time asc
) m, (select @st_row:=0 ,@st_type:='') st
) ttt on t.deal_id = ttt.deal_id
where t.deal_source = 1
order by t.deal_id