Tabelle in Blöcke selektieren

Wise Elisha · 14.11.2011, 05:50

Hallo Freunde,

ich brauche dringend Eure Hilfe. Mein SQL-Kopf raucht und ich kapiere die Group-Regeln nicht, oder JOINs genau so wenig.

folgende Testtabelle liegt vor:

Code:

------------------------------------------------------------
Block    Patienten_Nr          Name               Haus          Aufsicht
------------------------------------------------------------
  1           1                      Peter                4               13
  1           2                      Hans                5                13 
  1           4                      Karl                  4               14
  1           5                      Heinz                4               14
  1           10                    Gaby                 4               14
  1           11                    Lilly                   5               17
  1           12                    Leon                  3               17
  1           15                    Kent                  3               13

Ich möchte jetzt eine SELECT Anweisung schreiben, die mir jene Patienten-Nr. auswirft, die im gleichen Haus UND bei der gleichen Aufsicht sind. Wäre ja einfach:

Code:

SELECT MIN(Patienten_Nr) as 'von', MAX(Patienten_Nr) as 'bis', count(*) as 'Menge', Haus, Aufsicht
FROM Tabelle_Patienten_Uebersicht
WHERE block=1
GROUP BY Haus, Aufsicht
ORDER BY Patienten_nr

liefert mir das Ergbnis:

Code:

------------------------------------------------------------
von     bis         Menge        Haus          Aufsicht
------------------------------------------------------------
  1        1            1             4               13
  2        2            1             5               13 
  4        10           3            4               14   (!)
  11      11           1             5               17
  12      12           1             3               17
  15      15           1             3               13

Da man in der Zeile 3 (!) nicht sehen kann welche ID's dort fehlen, evtl. an der Menge erraten kann, daß hier etwas nicht stimmt, brauche ich einen SELECT-Befehl, der irgendwie einen Group über Patienten_Nr durchführt, so daß die Zeile Drei in zwei "Blöcke" unterteilt wird. Bei Sprügen in der Patienten-Nr. soll eine neue Gruppe gestartet werden, so daß alle fehlenden Zahlen ausgeschlossen werden.

In diesem Beispiel:

Code:

  4        5            2            4               14 
  10      10           1            4               14

Nur wie bekomme ich das hin?

Lieber Gruß vorweg.
---------------------------------
PHP 5.3.2-1ubuntu4.9 with Suhosin-Patch (cli) (built: May 3 2011 00:45:52)

eagle275 · 14.11.2011, 08:04

tja - dein Problem ist eigentlich, dass deine Abfrage falsch ist -

der count und das group ist natürlich völliger Humbug (sic.)

wenn du wirklich wissen willst, WELCHE EINZELNEN Patienten in welchem Haus / sortiert und bei der gleichen Aufsicht sind, dann darfst du eben nicht summieren

anstelle dessen solltest du mit einem SELF-Join arbeiten (gleiche Tabelle unter anderem Alias joinen) und dann kannst du mit Unique dafür sorgen, dass jede Patienten ID nur einmal vorkommt

Wise Elisha · 14.11.2011, 16:44

Hallo eagle275 und danke für die Antwort.

Vorneweg: Ich habe zur Zeit einen massiven burnout durch Überarbeitung, kann mit aber keinen Urlaub leisten, also weiter.

Deine Antwort verstehe ich teilweise, glaube aber auch, daß ich mich nicht 100%ig korrekt ausgedrückt habe. War wohl heute Nacht zu müde.

Ich möchte schon, daß Zeilen ausgeblendet werden, aber nur wenn sie aufeinanderfolgen, also z. B. so:

(auf andere Beispieltabelle ebzogenes Ergebnis)

Code:

------------------------------------------------------------------------
ID von - bis         Menge        Haus          Aufsicht
------------------------------------------------------------------------
  1    -   15          15             4               13
(hier fehler 4 Nummern "ID": Deshalb ein neuer Block :!:)
  19   -   31           12            4               13
(Hier ist die Aufsicht unterschiedlich: Neuer Block)
  32   -   55           24            4               14
(Hier ist Haus und Aufsicht unterschiedlich: Neuer Block)
  56   -   87           33            5               17
(hier fehler 7 Nummern "ID": Deshalb ein neuer Block :!:)
  95   -   97           3             5               17
(Hier ist das Haus unterschiedlich: Neuer Block)
  98   -   115         18            3               17

Die Differenzierung in ID-Blöcke bei fehlenden Nummern bekomme ich nicht hin.

Die Beispieltabelle sähe dann wie folgt aus:

Code:

------------------------------------------------------------------------
  ID          Haus          Aufsicht
------------------------------------------------------------------------
  1              4               13
  2              4               13
  3              4               13
  4              4               13
  5              4               13
  6              4               13
  7              4               13
  8              4               13
  9              4               13
  10             4               13
  11             4               13
  12             4               13
  13             4               13
  14             4               13
  15             4               13
(hier fehler 4 Nummern "ID")
  19             4               13
  20             4               13
  21             4               13
  22             4               13
...

Ich verstehe nicht, wie ich mit einem SELF-JOIN arbeiten soll, denn ein SELF JOIN bietet mir doch nur die Möglichkeit eine Tabelle mit sich selbst zu vergleichen. Wenn ich aber eine Tabelle, in der 4 Zeilen fehlen, mit einer Tabelle, in der auch 4 Zeilen fehlen, vergleiche, bekomme ich ein eindeutiges Ergebnis: 1:1 = True, aber keinen Hinweis auf die fehlenden Zeilen.
Irgendwie müßte man sowas wie einen Vergleich machen:
"Wenn (dieseZeile.`ID`+1) != nächsteZeile.`ID`, dann Ausgabe", aber MySQL kann solche Zeilenübergreifenden Ausdrücke nicht.

Ich habe schon versucht, einen Self-JOIN zu machen, mit einem
... ON A.`ID`=B.INCREMENT(`ID`)
Aber das hat natürlich nicht funktioniert.

Wie gesagt: Ich habe eine Lösung in PHP, die die gesamte Tabelle zeilenweise ausliest und mit der vorherigen Zeile vergleicht, aber bei 55.000 Zeilen läuft das Script schon einige Zeit...

und machmal bricht es ab.

Ich muß das hinbekommen, daß MySQL die Arbeit macht.

hausl · 14.11.2011, 18:05

Ev ??

http://forum.de.selfhtml.org/archiv/2006/11/t140928/

achtelpetit · 14.11.2011, 20:44

Vielleicht hilft Die der Befehl "GROUP_CONCAT" weiter, damit könntest Du die von der Gruppierung erfassten Werte "nebeneinander" schreiben, also zeilenweise zusammenfassen.
Genaueres siehe hier: http://dev.mysql.com/doc/refman/5.1/...functions.html

Wise Elisha · 15.11.2011, 00:49

Herzlichen Dank an "hausl" und "achtelpetit" für die Antworten.

@hausl:
Ich habe die Seiten durchgelesen und viel Neues gelernt, aber wie ich meine einzelne (!) Tabelle blockweise nach fehlenden Zeilen ausgeben kann, das wurde dort auch nicht gelöst.

@achtelpetit:
GROUP_CONCAT hätte ich jetzt so nie verwendet, aber es liese sich bestimmt eine Funktion daraus basteln. Ich muß mal überlegen, ob's eine Möglichkeit gibt mit einem Subquery diesen CONCAT-String auf den ersten fehlenden Wert zu prüfen, dann könnte ich den Vorgang beim folgenden Wert fortsetzen, bis ich alle Blöcke kenne... Ich muß da mal überlegen. Ich rieche eine Lösung, sie liegt mir auf der Zunge...

***grübel***

Ich versuche vorerst mal folgendes:
- CONCAT-String ausgeben lassen.
- Ergebnis in ARRAY "IDs" exploden.
- Array "IDs" einmal linear durchlaufen und Lücken herausfinden: Start + End ID in neuen Array "Blocks" merken.

Sind zwar noch einige Schritte, aber DEUTLICH weniger Aufwand, als den gesamten Tabelleninhalt in PHP zu laden und zu bearbeiten.

Auf jeden Fall scheint sich eine vorläufige, praktikable Lösung gefunden zu haben. Vielen DANK!

Ich poste später nochmal, ob's geklappt hat.

Wise Elisha · 15.11.2011, 13:15

Hallo,

habe es gestern umgesetzt und es geht.

Entscheidend ist die 2-stufige Vorgehensweise:
1.) Mit GROUP_CONCAT die ID-Nummern aufwerfen lassen und in Array speichern. Array linear durchlaufen und Blockliste in zweitem Array merken.
3.) Jeden ID-Block separat mit GROUB BY filtern und ausgeben.

Ein wenig umständlich wird diese Methode bei sehr großen Datentabellen.
Man muß aufpassen, daß in den GROUP-CONCAT auch genügend Zeichen hineinpassen und den Parameter
my_sql('SET @@group_concat_max_len := @@max_allowed_packet');
setzen. Natürlich muß evtl. auch der "max_allowed_packet" noch erhöht werden.
Für sehr große Tabellen ist dies sicher nicht die Lösung, aber da finde ich später auch noch einen Weg das MySQL in die Schuhe zu schieben. Der SQL-Sklave darf hier nicht geschont werden.

Küßchen und vielen Dank vorerst.

achtelpetit · 15.11.2011, 20:49

Ich hab' noch mal nachgedacht, tu ich manchmal.
Es könnte sein, daß die Normalisierung der Tabellen nicht in Ordnung ist. Gibt es pro Haus in einem Zeitraum immer nur eine Aufsicht? Ist denn die Zuordnung Patient zu Haus nicht eine dauerhafte Sache?
Gib doch bitte ein bisserl Info, welche Dinge da wie miteinander in Beziehung stehen.
Mit "GROUP_CONCAT" arbeitet man nicht in der von Dir beschriebenen Form, das ist 'ne Krücke. Das müffelt nach Normalisierungsfehler.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Join auf einen Wertebereich der 3. Tabelle	red-head	Datenbanken	5	09.11.2011 11:05
[Erledigt] Zwei oder eine Tabelle für die Benutzer einer Webapplikation ?	dreamcatcher	Datenbanken	5	01.09.2011 23:12
Daten aus Tabelle 1 in Tabelle 2 schreiben	Orakel	PHP Einsteiger	2	21.07.2011 19:01
Ausgabe Tabellen dynamisch aus Tabelleninhalt erstellen	titus81	Datenbanken	5	20.05.2011 19:58
Eine Tabelle, oder lieber mit zweite Tabelle arbeiten und diese Joinen?	freshjayson	Datenbanken	3	11.04.2011 13:04
Bilder in Tabelle einfügen Mysql oder direkt?!	DKuhn	PHP Tipps 2009	3	30.09.2009 10:14
Löschen von Datensätzen	pioneer01	Datenbanken	2	02.08.2009 13:09
tabelle 2 in tabelle 1 updaten	steffen_dk	Datenbanken	11	04.07.2009 15:04
[Erledigt] mysql abfrage über 4 Tabelle - bis 3 geht, bei der 4. habert		Datenbanken	2	08.09.2005 11:59
tabelle in tabelle ohne aussenrand	noskule	HTML, Usability und Barrierefreiheit	6	25.08.2005 14:17
spielplan, und Tabelle erstellen	alias_zappler	PHP Tipps 2005-2	3	01.08.2005 00:25
[Erledigt] Tabelle in Tabelle automatisch anpassen		HTML, Usability und Barrierefreiheit	3	04.11.2004 21:37
[Erledigt] Letzter Eintrag in einer Tabelle und Top Five einer Tabelle		Datenbanken	2	27.09.2004 06:50
Sortieren anhand einer zweiten Tabelle		PHP Tipps 2004	6	20.09.2004 09:03

14.11.2011, 05:50
Wise Elisha Neuer Benutzer Registriert seit: 20.09.2011 Beiträge: 4 PHP-Kenntnisse: Fortgeschritten	Tabelle in Blöcke selektieren Hallo Freunde, ich brauche dringend Eure Hilfe. Mein SQL-Kopf raucht und ich kapiere die Group-Regeln nicht, oder JOINs genau so wenig. folgende Testtabelle liegt vor: Code: ------------------------------------------------------------ Block Patienten_Nr Name Haus Aufsicht ------------------------------------------------------------ 1 1 Peter 4 13 1 2 Hans 5 13 1 4 Karl 4 14 1 5 Heinz 4 14 1 10 Gaby 4 14 1 11 Lilly 5 17 1 12 Leon 3 17 1 15 Kent 3 13 Ich möchte jetzt eine SELECT Anweisung schreiben, die mir jene Patienten-Nr. auswirft, die im gleichen Haus UND bei der gleichen Aufsicht sind. Wäre ja einfach: Code: SELECT MIN(Patienten_Nr) as 'von', MAX(Patienten_Nr) as 'bis', count(*) as 'Menge', Haus, Aufsicht FROM Tabelle_Patienten_Uebersicht WHERE block=1 GROUP BY Haus, Aufsicht ORDER BY Patienten_nr liefert mir das Ergbnis: Code: ------------------------------------------------------------ von bis Menge Haus Aufsicht ------------------------------------------------------------ 1 1 1 4 13 2 2 1 5 13 4 10 3 4 14 (!) 11 11 1 5 17 12 12 1 3 17 15 15 1 3 13 Da man in der Zeile 3 (!) nicht sehen kann welche ID's dort fehlen, evtl. an der Menge erraten kann, daß hier etwas nicht stimmt, brauche ich einen SELECT-Befehl, der irgendwie einen Group über Patienten_Nr durchführt, so daß die Zeile Drei in zwei "Blöcke" unterteilt wird. Bei Sprügen in der Patienten-Nr. soll eine neue Gruppe gestartet werden, so daß alle fehlenden Zahlen ausgeschlossen werden. In diesem Beispiel: Code: 4 5 2 4 14 10 10 1 4 14 Nur wie bekomme ich das hin? Lieber Gruß vorweg. --------------------------------- PHP 5.3.2-1ubuntu4.9 with Suhosin-Patch (cli) (built: May 3 2011 00:45:52)


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

14.11.2011, 08:04
eagle275 Erfahrener Benutzer Registriert seit: 01.09.2010 Beiträge: 4.561 PHP-Kenntnisse: Fortgeschritten	tja - dein Problem ist eigentlich, dass deine Abfrage falsch ist - der count und das group ist natürlich völliger Humbug (sic.) wenn du wirklich wissen willst, WELCHE EINZELNEN Patienten in welchem Haus / sortiert und bei der gleichen Aufsicht sind, dann darfst du eben nicht summieren anstelle dessen solltest du mit einem SELF-Join arbeiten (gleiche Tabelle unter anderem Alias joinen) und dann kannst du mit Unique dafür sorgen, dass jede Patienten ID nur einmal vorkommt __________________ "Irren ist männlich", sprach der Igel und stieg von der Drahtbürste

14.11.2011, 16:44
Wise Elisha Neuer Benutzer Registriert seit: 20.09.2011 Beiträge: 4 PHP-Kenntnisse: Fortgeschritten	Abfrage falsch Hallo eagle275 und danke für die Antwort. Vorneweg: Ich habe zur Zeit einen massiven burnout durch Überarbeitung, kann mit aber keinen Urlaub leisten, also weiter. Deine Antwort verstehe ich teilweise, glaube aber auch, daß ich mich nicht 100%ig korrekt ausgedrückt habe. War wohl heute Nacht zu müde. Ich möchte schon, daß Zeilen ausgeblendet werden, aber nur wenn sie aufeinanderfolgen, also z. B. so: (auf andere Beispieltabelle ebzogenes Ergebnis) Code: ------------------------------------------------------------------------ ID von - bis Menge Haus Aufsicht ------------------------------------------------------------------------ 1 - 15 15 4 13 (hier fehler 4 Nummern "ID": Deshalb ein neuer Block :!:) 19 - 31 12 4 13 (Hier ist die Aufsicht unterschiedlich: Neuer Block) 32 - 55 24 4 14 (Hier ist Haus und Aufsicht unterschiedlich: Neuer Block) 56 - 87 33 5 17 (hier fehler 7 Nummern "ID": Deshalb ein neuer Block :!:) 95 - 97 3 5 17 (Hier ist das Haus unterschiedlich: Neuer Block) 98 - 115 18 3 17 Die Differenzierung in ID-Blöcke bei fehlenden Nummern bekomme ich nicht hin. Die Beispieltabelle sähe dann wie folgt aus: Code: ------------------------------------------------------------------------ ID Haus Aufsicht ------------------------------------------------------------------------ 1 4 13 2 4 13 3 4 13 4 4 13 5 4 13 6 4 13 7 4 13 8 4 13 9 4 13 10 4 13 11 4 13 12 4 13 13 4 13 14 4 13 15 4 13 (hier fehler 4 Nummern "ID") 19 4 13 20 4 13 21 4 13 22 4 13 ... Ich verstehe nicht, wie ich mit einem SELF-JOIN arbeiten soll, denn ein SELF JOIN bietet mir doch nur die Möglichkeit eine Tabelle mit sich selbst zu vergleichen. Wenn ich aber eine Tabelle, in der 4 Zeilen fehlen, mit einer Tabelle, in der auch 4 Zeilen fehlen, vergleiche, bekomme ich ein eindeutiges Ergebnis: 1:1 = True, aber keinen Hinweis auf die fehlenden Zeilen. Irgendwie müßte man sowas wie einen Vergleich machen: "Wenn (dieseZeile.`ID`+1) != nächsteZeile.`ID`, dann Ausgabe", aber MySQL kann solche Zeilenübergreifenden Ausdrücke nicht. Ich habe schon versucht, einen Self-JOIN zu machen, mit einem ... ON A.`ID`=B.INCREMENT(`ID`) Aber das hat natürlich nicht funktioniert. Wie gesagt: Ich habe eine Lösung in PHP, die die gesamte Tabelle zeilenweise ausliest und mit der vorherigen Zeile vergleicht, aber bei 55.000 Zeilen läuft das Script schon einige Zeit... und machmal bricht es ab. Ich muß das hinbekommen, daß MySQL die Arbeit macht.

14.11.2011, 18:05
hausl Erfahrener Benutzer Registriert seit: 03.08.2010 Beiträge: 1.140 PHP-Kenntnisse: Anfänger	Ev ?? http://forum.de.selfhtml.org/archiv/2006/11/t140928/ __________________ Keine Zahl != ein Zeichen das keine Zahl ist

14.11.2011, 20:44
achtelpetit Erfahrener Benutzer Registriert seit: 01.06.2011 Beiträge: 389 PHP-Kenntnisse: Anfänger	Vielleicht hilft Die der Befehl "GROUP_CONCAT" weiter, damit könntest Du die von der Gruppierung erfassten Werte "nebeneinander" schreiben, also zeilenweise zusammenfassen. Genaueres siehe hier: http://dev.mysql.com/doc/refman/5.1/...functions.html

15.11.2011, 00:49
Wise Elisha Neuer Benutzer Registriert seit: 20.09.2011 Beiträge: 4 PHP-Kenntnisse: Fortgeschritten	Eine Lösung ist in Sicht! Herzlichen Dank an "hausl" und "achtelpetit" für die Antworten. @hausl: Ich habe die Seiten durchgelesen und viel Neues gelernt, aber wie ich meine einzelne (!) Tabelle blockweise nach fehlenden Zeilen ausgeben kann, das wurde dort auch nicht gelöst. @achtelpetit: GROUP_CONCAT hätte ich jetzt so nie verwendet, aber es liese sich bestimmt eine Funktion daraus basteln. Ich muß mal überlegen, ob's eine Möglichkeit gibt mit einem Subquery diesen CONCAT-String auf den ersten fehlenden Wert zu prüfen, dann könnte ich den Vorgang beim folgenden Wert fortsetzen, bis ich alle Blöcke kenne... Ich muß da mal überlegen. Ich rieche eine Lösung, sie liegt mir auf der Zunge... *grübel* Ich versuche vorerst mal folgendes: - CONCAT-String ausgeben lassen. - Ergebnis in ARRAY "IDs" exploden. - Array "IDs" einmal linear durchlaufen und Lücken herausfinden: Start + End ID in neuen Array "Blocks" merken. Sind zwar noch einige Schritte, aber DEUTLICH weniger Aufwand, als den gesamten Tabelleninhalt in PHP zu laden und zu bearbeiten. Auf jeden Fall scheint sich eine vorläufige, praktikable Lösung gefunden zu haben. Vielen DANK! Ich poste später nochmal, ob's geklappt hat.

15.11.2011, 13:15
Wise Elisha Neuer Benutzer Registriert seit: 20.09.2011 Beiträge: 4 PHP-Kenntnisse: Fortgeschritten	GELÖST: Fehlende Zeilen in Tabelle finden Hallo, habe es gestern umgesetzt und es geht. Entscheidend ist die 2-stufige Vorgehensweise: 1.) Mit GROUP_CONCAT die ID-Nummern aufwerfen lassen und in Array speichern. Array linear durchlaufen und Blockliste in zweitem Array merken. 3.) Jeden ID-Block separat mit GROUB BY filtern und ausgeben. Ein wenig umständlich wird diese Methode bei sehr großen Datentabellen. Man muß aufpassen, daß in den GROUP-CONCAT auch genügend Zeichen hineinpassen und den Parameter my_sql('SET @@group_concat_max_len := @@max_allowed_packet'); setzen. Natürlich muß evtl. auch der "max_allowed_packet" noch erhöht werden. Für sehr große Tabellen ist dies sicher nicht die Lösung, aber da finde ich später auch noch einen Weg das MySQL in die Schuhe zu schieben. Der SQL-Sklave darf hier nicht geschont werden. Küßchen und vielen Dank vorerst.

15.11.2011, 20:49
achtelpetit Erfahrener Benutzer Registriert seit: 01.06.2011 Beiträge: 389 PHP-Kenntnisse: Anfänger	Ich hab' noch mal nachgedacht, tu ich manchmal. Es könnte sein, daß die Normalisierung der Tabellen nicht in Ordnung ist. Gibt es pro Haus in einem Zeitraum immer nur eine Aufsicht? Ist denn die Zuordnung Patient zu Haus nicht eine dauerhafte Sache? Gib doch bitte ein bisserl Info, welche Dinge da wie miteinander in Beziehung stehen. Mit "GROUP_CONCAT" arbeitet man nicht in der von Dir beschriebenen Form, das ist 'ne Krücke. Das müffelt nach Normalisierungsfehler.