Упорядочить результат запроса даты [оракул 11g]

Question

Это мой запрос, чтобы получить все возможные даты между двумя датами на основе дней.

    select A.presentationID,
       A.PRESENTATIONDAY,
       TO_CHAR(A.PRESENTATIONDATESTART+delta,'DD-MM-YYYY','NLS_CALENDAR=GREGORIAN') LIST_DATE
from 
  PRESENTATION A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(PRESENTATIONDATEEND- PRESENTATIONDATESTART) from PRESENTATION
     )
  )
where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
and
  a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))
order by 1,2,3;

Значения получены из presentation стол, который состоит из presentationday, presentationdatestart а также presentationdateend,

Результат этого запроса:

622 Monday  02-05-2016 12:00:00
622 Monday  09-05-2016 12:00:00
622 Monday  16-05-2016 12:00:00
622 Monday  23-05-2016 12:00:00
622 Monday  30-05-2016 12:00:00
623 Tuesday 03-05-2016 12:00:00
623 Tuesday 10-05-2016 12:00:00
623 Tuesday 17-05-2016 12:00:00
623 Tuesday 24-05-2016 12:00:00
623 Tuesday 31-05-2016 12:00:00
624 Wednesday   04-05-2016 12:00:00
624 Wednesday   11-05-2016 12:00:00
624 Wednesday   18-05-2016 12:00:00
624 Wednesday   25-05-2016 12:00:00
624 Wednesday   01-06-2016 12:00:00
625 Thursday    05-05-2016 12:00:00
625 Thursday    12-05-2016 12:00:00
625 Thursday    19-05-2016 12:00:00
625 Thursday    26-05-2016 12:00:00
625 Thursday    02-06-2016 12:00:00

Как я могу расположить эти значения примерно так:

    622 Monday      02-05-2016     
    623 Tuesday     03-05-2016     
    624 Wednesday   04-05-2016     
    625 Thursday    05-05-2016     
    622 Monday      09-05-2016     
    623 Tuesday     10-05-2016  
    624 Wednesday   11-05-2016    
    625 Thursday    12-05-2016 
    622 Monday      16-05-2016 
    ....
    625 Thursday    02-06-2016 

Answer 1

Я думаю, что вы только после этого:

select   a.presentationid,
         a.presentationday,
         to_char (a.presentationdatestart + delta, 'DD-MM-YYYY', 'NLS_CALENDAR=GREGORIAN') list_date
from     presentation a,
         (select     level - 1 as delta
          from       dual
          connect by level - 1 <= (select max (presentationdateend - presentationdatestart)
                                   from   presentation))
where    a.presentationdatestart + delta <= a.presentationdateend
and      a.presentationday = to_char(a.presentationdatestart + delta, 'fmDay')
order by a.presentationdatestart + delta,
         a.presentationid;

Обратите внимание, как я удалил ваш trim() и заменил его fm в маске формата.

PS Вы можете переписать свой запрос, чтобы удалить условие соединения (и дополнительный вызов таблицы презентации), выполнив это следующим образом:

with presentation as (select 622 presentationid, 'Monday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('31/05/2016', 'dd/mm/yyyy') presentationdateend from dual union all
                      select 623 presentationid, 'Tuesday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('31/05/2016', 'dd/mm/yyyy') presentationdateend from dual union all
                      select 624 presentationid, 'Wednesday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('07/06/2016', 'dd/mm/yyyy') presentationdateend from dual union all
                      select 625 presentationid, 'Thursday' presentationday, to_date('01/05/2016', 'dd/mm/yyyy') presentationdatestart, to_date('07/06/2016', 'dd/mm/yyyy') presentationdateend from dual)
-- end of mimicking your presentation table with data in it. You wouldn't need this subquery as you have the table; see SQL below.
select presentationid,
       presentationday,
       to_char(next_day(presentationdatestart -1, presentationday) + 7*(level - 1), 'DD-MM-YYYY') list_date
from   presentation
connect by prior presentationid = presentationid
           and prior sys_guid() is not null
           and next_day(presentationdatestart -1, presentationday) + 7*(level - 1) <= presentationdateend
order by next_day(presentationdatestart -1, presentationday) + 7*(level - 1),
         presentationid;

PRESENTATIONID PRESENTATIONDAY LIST_DATE 
-------------- --------------- ----------
           622 Monday          02-05-2016
           623 Tuesday         03-05-2016
           624 Wednesday       04-05-2016
           625 Thursday        05-05-2016
           622 Monday          09-05-2016
           623 Tuesday         10-05-2016
           624 Wednesday       11-05-2016
           625 Thursday        12-05-2016
           622 Monday          16-05-2016
           623 Tuesday         17-05-2016
           624 Wednesday       18-05-2016
           625 Thursday        19-05-2016
           622 Monday          23-05-2016
           623 Tuesday         24-05-2016
           624 Wednesday       25-05-2016
           625 Thursday        26-05-2016
           622 Monday          30-05-2016
           623 Tuesday         31-05-2016
           624 Wednesday       01-06-2016
           625 Thursday        02-06-2016

Answer 2

Использовать этот:

TO_CHAR(A.PRESENTATIONDATESTART+delta,'DD-MM-YYYY','NLS_CALENDAR=GREGORIAN' ) LIST_DATE

вместо

A.PRESENTATIONDATESTART+delta LIST_DATE

это форматирует вашу дату

обновленный ответ на ваш новый вызов:

select * from (
  select A.presentationID,
       A.PRESENTATIONDAY,


          TO_CHAR(A.PRESENTATIONDATESTART+delta,'DD-MM-YYYY','NLS_CALENDAR=GREGORIAN') LIST_DATE,
    row_number() over (partition by presentationID,PRESENTATIONDATESTART+delta
order by presentationID,PRESENTATIONDATESTART+delta) r
    from 
      PRESENTATION A, 
      (
         select level-1 as delta 
         from dual 
         connect by level-1 <= (
           select max(PRESENTATIONDATEEND- PRESENTATIONDATESTART) from PRESENTATION
         )
      )
    where A.PRESENTATIONDATESTART+delta <= A.PRESENTATIONDATEEND
    and
      a.presentationday = trim(to_char(A.PRESENTATIONDATESTART+delta, 'Day'))

    )
    order by r