Iată două tabele.
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 PERSONS
PERSON_ID + NAME ================= 111222 ABC 222111 XYZ Iată interogarea mea oracle.
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; ceea ce oferă aceste rezultate
LAST_UPDATE SCHOOL_CODE PERSON_ID ===========+===========+========= 24-JAN-13 ABE 111222 09-FEB-12 ABE 222111 Vreau să îl selectez pe primul pentru școala cu ultima dată.
Mulțumesc.
Răspuns
Interogarea dvs. curentă nu oferă rezultatul dorit deoarece utilizați un GROUP BY clauză pe coloana PERSON_ID care are o valoare unică pentru ambele intrări. Drept urmare, veți returna ambele rânduri.
Există câteva modalități prin care puteți rezolva acest lucru. Puteți utiliza o interogare pentru a aplica funcția agregată pentru a returna max(LAST_UPDATE_DATE_TIME) pentru fiecare SCHOOL_CODE:
select s1.LAST_UPDATE_DATE_TIME, s1.SCHOOL_CODE, s1.PERSON_ID from SCHOOL_STAFF s1 inner join ( select max(LAST_UPDATE_DATE_TIME) LAST_UPDATE_DATE_TIME, SCHOOL_CODE from SCHOOL_STAFF group by SCHOOL_CODE ) s2 on s1.SCHOOL_CODE = s2.SCHOOL_CODE and s1.LAST_UPDATE_DATE_TIME = s2.LAST_UPDATE_DATE_TIME; Consultați Fiolă SQL cu demonstrație
Sau puteți utiliza un funcție de fereastră pentru a returna rândurile de date pentru fiecare școală cu cel mai recent 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; Consultați Fiolă SQL cu demonstrație
Această interogare implementează row_number() care atribuie un număr unic fiecărui rând din partiția SCHOOL_CODE și plasat într-o ordine descrescătoare bazată pe LAST_UPDATE_DATE_TIME.
Ca o notă laterală, funcția JOIN cu agregat nu este exact aceeași cu versiunea row_number(). Dacă aveți două rânduri cu același timp al evenimentului, JOIN va returna ambele rânduri, în timp ce row_number() va returna doar unul. Dacă doriți să reveniți pe amândouă cu o funcție de fereastră, luați în considerare utilizarea funcției de fereastră rank(), deoarece va returna legăturile:
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; Consultați Demo
Comentarii
- Mulțumesc, găsesc îmbinarea interioară cu un tabel de subinterogare (exemplul 1 de mai sus) pentru a fi cea mai intuitivă .. și nu ' îmi cere să aflu ce este partiția de despre. Iată o privire la o sintaxă similară cu exemplul 1: selectați oT.dateField, oT.siteID, oT.field1, oT.field2, oT.field3, din OriginalTable ca oT b . ' se întâmplă în subinterogare.
Răspuns
I „Mă mir că nimeni nu a profitat de funcțiile ferestrei dincolo de row_number ()
Iată câteva date cu care să te joci:
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"); Clauza OVER () creează o fereastră pentru care vă veți defini grupurile agregate. În acest caz, partiționez doar pe SHOOL_CODE, așa că vom vedea FIRST_VALUE, care va veni de la LAST_UPDATE_DATE_TIME, grupate după SCHOOL_CODE și în ordinea LAST_UPDATE_DATE_TIME, în ordine descrescătoare. Această valoare va fi aplicată întregii coloane pentru fiecare SCHOOL_CODE.
Este important să acordați o atenție deosebită partiționării și ordonării dvs. în clauza over ().
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 Returnează:
24-JAN-13 ABE 111222 Acest lucru ar trebui să vă elimine nevoia de GROUP BY și Subquies în majoritatea cazurilor. Veți dori să vă asigurați că includeți DISTINCT.
Comentarii
- Este frumos, dar există o modalitate de a evita repetarea clauzei over pentru toate coloanele?
Răspuns
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) Comentarii
- În loc să postați doar cod, ar trebui să încercați să explicați modul în care aceasta răspunde la întrebare; și potențial ceea ce făcea PO incorect.