在group by子句中使用min和max

I want below output in oracle sql.

我想在oracle sql下面的输出。

I have data in table as below :

我的数据如下表所示:

id    start_date      end_date      assignment number

1     2.02.2014       15.02.2014    10
2     25.02.2014      30.02.2014    20
3     26.03.2014      04.05.2014    30
4     06.06.2014      31.12.4712    10

I need output using group by

我需要使用group by输出

assignment_number   start_date          end_date

10                  02.02.2014          15.02.2014
10                  06.06.2014          31.12.4712
20                  25.02.2014          30.02.2014
30                  26.03.2014          04.05.2014

I tried using min(start_date) and max(end_date) for assignment 10 ia was getting output as

我尝试使用min(start_date)和max(end_date)进行赋值10 ia得到输出为

assignment_number   start_date          end_date
10                  02.02.2014          31.12.4712

But I want as :-

但我想要: -

assignment_number   start_date          end_date
10                  02.02.2014          15.02.2014
10                  06.06.2014          31.12.4712

Please help

2 个解决方案

#1

I think you'd have to calculate the min and max separately, then union them. Try something like this:

我认为你必须分别计算最小值和最大值,然后将它们联合起来。尝试这样的事情:

SELECT 
 assignment_number
 , start_date
 , end_date
 FROM 
      (SELECT
                assignment_number
                , start_date
                , end_date
                FROM TABLE
                GROUP BY assignment_number
                HAVING MIN(start_date)

           UNION

           SELECT
                assignment_number
                , start_date
                , end_date
                FROM TABLE
                GROUP BY assignment_number
                HAVING MAX(end_date)
      )
 ORDER BY 
      1 ASC
      , 2 ASC
      , 3 ASC

;

#2

sql fiddle

select id, to_char(start_date,'dd.mm.yyyy') start_date, to_char(end_date,'dd.mm.yyyy') end_date,ASSIGNMENT_NUMBER from sof1 s
where not exists 
                (select 1 from sof1 s2 
                 where s2.assignment_number=s.assignment_number 
                 and s2.start_date<s.start_date
                )
or not exists 
                (select 1 from sof1 s2 
                 where s2.assignment_number=s.assignment_number 
                 and s2.end_date>s.end_date
                )
order by ASSIGNMENT_NUMBER

With analytic function:

具有分析功能:

sql fiddle

select id, to_char(start_date,'dd.mm.yyyy') start_date, to_char(end_date,'dd.mm.yyyy') end_date,ASSIGNMENT_NUMBER from 

(select s.*
 , min (start_date) over (partition by ASSIGNMENT_NUMBER) sd
 , max (end_date) over (partition by ASSIGNMENT_NUMBER) ed
 from sof1 s
)
where start_date=sd or end_date=ed
order by ASSIGNMENT_NUMBER, start_date

#1