Auswahl von Transaktionen in einer einzigen Zeile

Question

Ich habe eine Anforderung, bei der ich einen Bericht über aktuelle Softwareprojekte erstellen muss. Zwei der Spalten sind das Datum des letzten Meilensteins und das Datum des davor liegenden Meilensteins. Natürlich werden die Meilensteine in einer Transaktionstabelle gespeichert, so dass wir viele Meilensteine pro Projekt haben können.

Bis hierher habe ich es geschafft, aber jetzt habe ich Probleme:

select  proj.*
from zsof_projects proj
join zsof_milestones current_milestone on current_milestone.product_id = proj.id
join zsof_milestones last_milestone on last_milestone.product_id = proj.id
join    (
        select product_id, max(actual_date) maxDate
        from zsof_milestones
        group by product_id
        ) a on a.product_id = current_milestone.product_id and a.maxDate = current_milestone.actual_date
join    (
        select mile.product_id, max(actual_date) maxDate
        from zsof_milestones mile
        join    (
                select product_id, max(actual_date) maxDate
                from zsof_milestones
                group by product_id
                ) a on a.product_id = mile.product_id and mile.actual_date < a.maxDate
        group by mile.product_id
        ) b on b.product_id = last_milestone.product_id and b.maxDate = last_milestone.actual_date
order by proj.id;

Das Problem, das ich habe, ist, dass nicht alle Projekte einen letzten Meilenstein haben und nicht alle Projekte mehr als einen Meilenstein haben werden. Ich habe versucht, linke Verknüpfungen zu erstellen, aber dann erhalte ich mehrere Zeilen pro Projekt zurück (was ich vermeiden muss).

Ich verwende Oracle 10. Wenn es also etwas gibt, das ich in PL/SQL verwenden kann, werde ich auch das nehmen.

Answer 1

Analytik nutzen :)

SELECT v.*
  FROM (SELECT proj.*, actual_date, 
               MAX(actual_date) over(PARTITION BY ms.product_id) last_milestone,
               lag(actual_date) over(PARTITION BY ms.product_id 
                                     ORDER BY actual_date) previous_milestone
           FROM zsof_projects proj
           LEFT JOIN zsof_milestones ms ON ms.product_id = proj.id) v
 WHERE last_milestone = actual_date
    OR (actual_date IS NULL AND last_milestone IS NULL)

Aktualisierung: Ich habe den JOIN in einen LEFT JOIN umgewandelt, für den Fall, dass ein Projekt keinen Meilenstein hat.