Hier sind zwei Tabellen.
SCHOOL_STAFF
SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID ================================================================= ABE Principal 24-JAN-13 111222 ABE Principal 09-FEB-12 222111 PERSONEN
PERSON_ID + NAME ================= 111222 ABC 222111 XYZ Hier ist meine Orakelabfrage.
SELECT MAX(LAST_UPDATE_DATE_TIME) AS LAST_UPDATE, SCHOOL_CODE, PERSON_ID FROM SCHOOL_STAFF WHERE STAFF_TYPE_NAME="Principal" GROUP BY SCHOOL_CODE, PERSON_ID ORDER BY SCHOOL_CODE; mit diesen Ergebnissen
LAST_UPDATE SCHOOL_CODE PERSON_ID ===========+===========+========= 24-JAN-13 ABE 111222 09-FEB-12 ABE 222111 Ich möchte die erste für die Schule mit dem neuesten Datum auswählen.
Danke.
Antwort
Ihre aktuelle Abfrage liefert nicht das gewünschte Ergebnis, da Sie eine GROUP BY -Klausel in der Spalte PERSON_ID, die für beide Einträge einen eindeutigen Wert hat. Als Ergebnis geben Sie beide Zeilen zurück.
Es gibt verschiedene Möglichkeiten, dies zu lösen. Sie können eine Unterabfrage verwenden, um die Aggregatfunktion anzuwenden und die max(LAST_UPDATE_DATE_TIME) für jede SCHOOL_CODE:
Siehe SQL-Geige mit Demo
Oder Sie können eine Fensterfunktion zum Zurückgeben der Datenzeilen für jede Schule mit der neuesten LAST_UPDATE_DATE_TIME:
select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME from ( select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME, row_number() over(partition by SCHOOL_CODE order by LAST_UPDATE_DATE_TIME desc) seq from SCHOOL_STAFF where STAFF_TYPE_NAME="Principal" ) d where seq = 1; Siehe SQL-Geige mit Demo
Diese Abfrage implementiert row_number() , die jeder Zeile in der Partition von SCHOOL_CODE eine eindeutige Nummer zuweist und in absteigender Reihenfolge basierend auf LAST_UPDATE_DATE_TIME.
Nebenbei bemerkt, die Funktion JOIN mit Aggregat entspricht nicht genau der Version row_number(). Wenn Sie zwei Zeilen mit derselben Ereigniszeit haben, gibt JOIN beide Zeilen zurück, während row_number() nur eine zurückgibt. Wenn Sie beide mit einer Fensterfunktion zurückgeben möchten, sollten Sie stattdessen die Fensterfunktion rank() verwenden, da sie Bindungen zurückgibt:
select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME from ( select SCHOOL_CODE, PERSON_ID, LAST_UPDATE_DATE_TIME, rank() over(partition by SCHOOL_CODE order by LAST_UPDATE_DATE_TIME desc) seq from SCHOOL_STAFF where STAFF_TYPE_NAME="Principal" ) d where seq = 1; Siehe Demo
Kommentare
- Danke, finde ich Die innere Verknüpfung mit einer Unterabfragetabelle (Beispiel 1 oben) ist die intuitivste. Für ' muss ich nicht lernen, welche Partition von alles ist Über. Hier ist ein Blick auf eine ähnliche Syntax wie in Beispiel 1: Wählen Sie oT.dateField, oT.siteID, oT.field1, oT.field2, oT.field3 aus originalTable als oT innerer Join (wählen Sie max (dateField) als latestDate, siteID aus originalTable-Gruppe nach siteID) als newTable auf oT.siteID = newTable.site_ID und oT.dateField = newTable.newestDate order by oT.siteID asc Für mich erklärt das besser, was ' geschieht in der Unterabfrage.
Antwort
I. „Ich bin überrascht, dass niemand Fensterfunktionen außerhalb von row_number ()
genutzt hat. Hier sind einige Daten zum Spielen:
CREATE TABLE SCHOOL_STAFF ( LAST_UPDATE_DATE_TIME VARCHAR(20), SCHOOL_CODE VARCHAR(20), PERSON_ID VARCHAR(20), STAFF_TYPE_NAME VARCHAR(20) ); INSERT INTO SCHOOL_STAFF VALUES ("24-JAN-13", "ABE", "111222", "Principal"); INSERT INTO SCHOOL_STAFF VALUES ("09-FEB-12", "ABE", "222111", "Principal"); Die Die OVER () -Klausel erstellt ein Fenster, für das Sie Ihre Aggregatgruppen definieren. In diesem Fall partitioniere ich nur auf dem SHOOL_CODE, sodass wir den FIRST_VALUE sehen, der von LAST_UPDATE_DATE_TIME stammt, gruppiert nach SCHOOL_CODE und in absteigender Reihenfolge in der Reihenfolge LAST_UPDATE_DATE_TIME. Dieser Wert wird für jeden SCHOOL_CODE auf die gesamte Spalte angewendet.
Es ist wichtig, dass Sie Ihre Partitionierung und Reihenfolge in der over () -Klausel genau beachten.
SELECT DISTINCT FIRST_VALUE(LAST_UPDATE_DATE_TIME) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS LAST_UPDATE ,FIRST_VALUE(SCHOOL_CODE) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS SCHOOL_CODE ,FIRST_VALUE(PERSON_ID) OVER (PARTITION BY SCHOOL_CODE ORDER BY LAST_UPDATE_DATE_TIME DESC) AS PERSON_ID FROM SCHOOL_STAFF WHERE STAFF_TYPE_NAME = "Principal" ORDER BY SCHOOL_CODE Rückgabe:
24-JAN-13 ABE 111222 Dadurch sollten Sie GROUP BY und Unterabfragen nicht mehr benötigen hauptsächlich. Sie sollten jedoch sicherstellen, dass DISTINCT enthalten ist.
Kommentare
- Dies ist nett, aber es gibt eine Möglichkeit, die Wiederholung der over-Klausel für zu vermeiden alle Spalten?
Antwort
select LAST_UPDATE_DATE_TIME as LAST_UPDATE, SCHOOL_CODE, PERSON_ID from SCHOOL_STAFF WHERE STAFF_TYPE_NAME="Principal" AND LAST_UPDATE_DATE_TIME = (SELECT MAX(LAST_UPDATE_DATE_TIME) FROM SCHOOL_STAFF s2 WHERE PERSON_ID = s2.PERSON_ID) Kommentare
- Anstatt nur Code zu veröffentlichen, sollten Sie versuchen zu erklären, wie dies die Frage beantwortet. und möglicherweise, was das OP falsch gemacht hat.