PDA

Archiv verlassen und diese Seite im Standarddesign anzeigen : SQL (Oracle) Pärchen ermitteln


ESAD
2007-12-02, 18:31:22
Ich habe mir mit folgenden Befehl die notwenigen daten aus den Tabellen zusammengeglaubt

select p.vorname, p.nachname, c.flugid ,f.dienstrangID,p.personID from person p
INNER JOIN flugpersonal f ON p.personID = f.personID AND (f.dienstrangID = 100 OR f.dienstrangID = 110)
INNER JOIN crew c ON p.personID = c.personID

nun soll ich aus den Daten:

vorname|nachname flugID DienstID PersonenID
MatthiasWall__________17 110 87
Roman Street_________17 100 88
Miriam Rapid__________16 110 92
Katrin Innsbruck_______16 100 93
Raimund van Saint-Omer_20 100 114
Kassandra Gehmeier____20 110 123
Nikolaus Luttkus_______27 100 150
Nikolaus Luttkus_______26 100 150
Nikolaus Luttkus_______25 100 150
Nikolaus Luttkus_______24 100 150
Christian Kaufmann_____1 110 2
Rene Kernegger________38 100 174
Rene Kernegger________33 100 174
Karin Popp____________38 110 179
Karin Popp____________33 110 179


ermitteln welche Personen bei wie vielen flügen als copilot (DienstID 110) und Pilot(DienstID 100) zusammengarbeitet haben...

ich steig aber da nicht ganz dahinter... müsste das mit sychronisierten subselects gehen?

nefu
2007-12-03, 17:00:24
Hi,

ich würde versuchen, die Daten in die folgende Form zu bringen :


scott@TEST102 >create table fluege(flugnr number, pilot varchar2(10), copilot varchar2(10));

Tabelle wurde erstellt.

scott@TEST102 >insert into fluege values(1, 'A', 'B');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(2, 'B', 'C');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(3, 'D', 'A');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(4, 'B', 'A');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(5, 'D', 'C');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(6, 'A', 'D');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(7, 'A', 'B');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(8, 'C', 'D');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(9, 'B', 'D');

1 Zeile wurde erstellt.

scott@TEST102 >insert into fluege values(10, 'A', 'D');

1 Zeile wurde erstellt.

scott@TEST102 >commit;

Transaktion mit COMMIT abgeschlossen.

scott@TEST102 >select * from fluege order by flugnr;

FLUGNR PILOT COPILOT
---------- ---------- ----------
1 A B
2 B C
3 D A
4 B A
5 D C
6 A D
7 A B
8 C D
9 B D
10 A D

10 Zeilen ausgewählt.


Jetzt willst Du also herausfinden, wer wie oft mit wem geflogen ist, egal wer Pilot war, und wer Copilot, wenn ich das Ganze richtig verstehe?


scott@TEST102 >select flugnr, least(pilot, copilot), greatest(pilot, copilot) from fluege
2 order by flugnr;

FLUGNR LEAST(PILO GREATEST(P
---------- ---------- ----------
1 A B
2 B C
3 A D
4 A B
5 C D
6 A D
7 A B
8 C D
9 B D
10 A D

10 Zeilen ausgewählt.

scott@TEST102 >


Das sollte Dir die Daten in der gewünschten Form präsentieren.
Spalte "flugnr" weglassen, und die Anzahl der "identischen" Sätze ermitteln ...
Auf der "Ask Tom" Webseite gibt es im folgenden Beitrag


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:49819421061280

(aus dem ich mich bedient habe :redface:) weitere Hinweise zu least/greatest.
Ich hoffe, das hilft Dir weiter.

Gruß!

nefu

nefu
2007-12-04, 21:55:19
Hallo ESAD,

ich habe noch ein paar Sätze in die Tabelle aufgenommen :


scott@TEST102 >select flugnr, pilot, copilot
2 from fluege
3 order by flugnr;

FLUGNR PILOT COPILOT
---------- ---------- ----------
1 A B
2 B C
3 D A
4 B A
5 D C
6 A D
7 A B
8 C D
9 B D
10 A D
11 D A
12 B A
13 D C
14 B D
15 C A
16 D A
17 B A
18 C B
19 D A
20 D B

20 Zeilen ausgewählt.

scott@TEST102 >


Die folgende Abfrage zeigt, wie das "least(...)" und "greatest(...)" funktioniert:


scott@TEST102 >select flugnr, pilot, copilot,
2 least(pilot, copilot)||' mit '||greatest(pilot, copilot) as X
3 from fluege order by flugnr;

FLUGNR PILOT COPILOT X
---------- ---------- ---------- -------------------------
1 A B A mit B
2 B C B mit C
3 D A A mit D
4 B A A mit B
5 D C C mit D
6 A D A mit D
7 A B A mit B
8 C D C mit D
9 B D B mit D
10 A D A mit D
11 D A A mit D
12 B A A mit B
13 D C C mit D
14 B D B mit D
15 C A A mit C
16 D A A mit D
17 B A A mit B
18 C B B mit C
19 D A A mit D
20 D B B mit D

20 Zeilen ausgewählt.

scott@TEST102 >


Über die Ergebnismenge dieses SELECT Statements wird jetzt noch eine passende Abfrage formuliert :


scott@TEST102 >select sq.X, count(*) from
2 (select flugnr, pilot, copilot,
3 least(pilot, copilot)||' mit '||greatest(pilot, copilot) as X
4 from fluege
5 order by flugnr) sq
6 group by sq.X
7 order by 2 desc;

X COUNT(*)
------------------------- ----------
A mit D 6
A mit B 5
C mit D 3
B mit D 3
B mit C 2
A mit C 1

6 Zeilen ausgewählt.

scott@TEST102 >



Es gibt bestimmt noch mehr Lösungswege.

Gruß!

nefu

ESAD
2007-12-04, 22:17:37
wow

hast du eigentlich einige gute seiten zum thema sql? auch in hinsicht zu problemlösungen also hier ist ein problem und so kann man es lösen z.B.?

vielen dank!

nefu
2007-12-04, 22:31:57
Hallo ESAD,

die erwähnte Seite von Tom Kyte benutze ich öfters, um nach Problemlösungen zu suchen.

http://asktom.oracle.com

Oft muss ich dann noch die offizielle Oracle Doku zu Hilfe nehmen, da ja selten genau das Problem besprochen wird, das man selbst gerade hat.
Aber die ist ja im Oracle technology network verfügbar, in der HTML Version kann man sie auch gut nach Begriffen durchsuchen.
Gute Seiten mit "how to's" habe ich leider selber noch keine gefunden.

Gruß!

nefu

nefu
2007-12-05, 10:53:09
Hi,

eine 'schlanke' Version meiner Lösung wäre z.B. auch


hr@GLTEST10> select * from fluege order by flugnr;

FLUGNR PILOT COPILOT
---------- ---------- ----------
1 A B
2 B C
3 D A
4 B A
5 D C
6 A D
7 A B
8 C D
9 B D
10 A D
11 D A
12 B A
13 D C
14 B D
15 C A
16 D A
17 B A
18 C B
19 D A
20 D B

20 Zeilen ausgewählt.

hr@GLTEST10> SELECT distinct(X), SUM(ANZAHL) OVER (PARTITION by X)
2 FROM (SELECT least(pilot, copilot)||' mit '||greatest(pilot, copilot) as X,
3 1 as ANZAHL
4 FROM FLUEGE)
5 ORDER BY 2 DESC, 1 ASC;

X SUM(ANZAHL)OVER(PARTITIONBYX)
------------------------- -----------------------------
A mit D 6
A mit B 5
B mit D 3
C mit D 3
B mit C 2
A mit C 1

6 Zeilen ausgewählt.

hr@GLTEST10>


... und mit der 'model clause' ab Oracle 10g lassen sich noch viele weitere tolle Sachen machen:

http://www.oracle.com/technology/products/bi/pdf/10gr1_twp_bi_dw_sqlmodel.pdf

In dem PDF File wird auch das "Partition by" erklärt.

Gruß!

nefu