Oracle SQL如何分组，但如果以后重复分组，则有多行

小编典典

Oracle SQL如何分组，但如果以后重复分组，则有多行

sql

我有以下查询

select paaf.assignment_id,
       paaf.position_id,
       paaf.effective_start_date effective_start_date,
       paaf.effective_end_date   effective_end_date
from   per_all_assignments_f paaf
where  paaf.position_id is not null
and    paaf.assignment_type in ('E', 'C')
and    paaf.primary_flag = 'Y'
and    paaf.assignment_number like '209384%'
order  by 3

哪个返回

"assignment_id" "position_id"   "effective_start_date"  "effective_end_date"
6518    5323    01/01/2013  28/02/2014
6518    8133    01/03/2014  30/06/2014
6518    8133    01/07/2014  31/10/2015
6518    239570  01/11/2015  15/11/2015
6518    239570  16/11/2015  31/12/2015
6518    8133    01/01/2016  27/07/2016
6518    8133    28/07/2016  31/12/4712

我将其分组为：

select paaf.assignment_id,
       paaf.position_id,
       min(paaf.effective_start_date) effective_start_date,
       max(paaf.effective_end_date)   effective_end_date
from   per_all_assignments_f paaf
where  paaf.position_id is not null
and    paaf.assignment_type in ('E', 'C')
and    paaf.primary_flag = 'Y'
and    paaf.assignment_number like '209384%'
group  by paaf.assignment_id, paaf.position_id

哪个返回：

"assignment_id" "position_id"   "effective_start_date"  "effective_end_date"
6518    5323    01/01/2013  28/02/2014
6518    8133    01/03/2014  31/12/4712
6518    239570  01/11/2015  31/12/2015

但是我需要一个返回的查询

"assignment_id" "position_id"   "effective_start_date"  "effective_end_date"
6518    5323    01/01/2013  28/02/2014
6518    8133    01/03/2014  31/10/2015
6518    239570  01/11/2015  31/12/2015
6518    8133    01/01/2016  31/12/4712

也就是说8133的position_id必须具有两行，因为按时间顺序有两个部分必须分组为2行而不是1（对于8133）。

是否有某种使用日期顺序完成此操作的方法？

答案是：

with paaf as
            (
               select paaf.assignment_id,
                      paaf.position_id,
                      paaf.effective_start_date effective_start_date,
                      paaf.effective_end_date   effective_end_date
               from   per_all_assignments_f paaf
               where  paaf.position_id is not null
               and    paaf.assignment_type in ('E', 'C')
               and    paaf.primary_flag = 'Y'
            -- and    paaf.assignment_number like '209384%'
               order  by 1, 3
            )
            select paaf2.assignment_id,
                   paaf2.position_id,
                   min(paaf2.effective_start_date) as effective_start_date,
                   max(paaf2.effective_end_date)   as effective_end_date
            from   (
                      select paaf.*,
                             row_number() over (order by paaf.assignment_id, paaf.effective_start_date) as seqnum,
                             row_number() over (partition by paaf.assignment_id, paaf.position_id order by paaf.assignment_id, paaf.effective_start_date) as seqnum_p
                      from   paaf
                   )  paaf2
            group  by (paaf2.seqnum - paaf2.seqnum_p), paaf2.assignment_id, paaf2.position_id

阅读 165

2021-05-16

共1个答案

小编典典

这是一个孤岛问题。有不同的方法，但是一种简单的方法使用不同的行号：

with paaf as (<your first query here>
     )
select paaf.assignment_id,
       paaf.position_id,
       min(paaf.effective_start_date) as effective_start_date,
       max(paaf.effective_end_date) as effective_end_date
from (select paaf.*,
             row_number() over (order by effective_start_date) as seqnum,
             row_number() over (partition by position_id order by effective_start_date) as seqnum_p
      from paaf
     ) paaf
group by (seqnum - seqnum_p), position_id, assignment_id;

2021-05-16