mySQL - globale Variable?!

[Speedy]

Hi,

mein Problem besteht aus folgendem Query:

SELECT felder, SUM( a ) / COUNT( a ) AS ar, SUM( b ) / COUNT( b ) AS br, SUM( c ) / COUNT( c ) AS cr
FROM table1
LEFT JOIN table2 ON table2.id = table1.field
LEFT JOIN (

SELECT felder
FROM table3
WHERE .....................
ORDER BY time DESC
LIMIT 10

) AS NewTable ON NewTable.field = table1.field

WHERE search LIKE 'search'
AND ...
GROUP BY bla, bla2
ORDER BY bla3

Hier sollen Daten aus table1 ausgelesen werden. Hinzugezogen werden Inhalte aus table2.
Des weiteren sollen Werte aus table3 mit einbezogen werden, mit dessen Werte mySQL selbst rechnet (COUNT ganz oben).
Wichtig ist dabei, dass aus table3 nur maximal 10 Datensätze mit einbezogen werden, weswegen das Limit dort auch steht.

Und dort ist das Problem.
In dem Sub-Query müsste ich eine WHERE-Bedingung hinkriegen, die letztendlich der ON-Bedingung für den Sub-Query entspricht.
Der Sub-Query ist notwendig, damit ich den Limit verwenden kann, den ich unbedingt brauche :)

Wie bekomme ich die Where-Bedingung im Sub-Query hin, damit diese der  ON-Bedingung dahinter entspricht?
Leider kann ich innerhalb des SQ ja nicht auf die Werte des Queries drum herum zugreifen.

Gruss
Speedy

[Thilo Fester]

Hey Speedy,

kann sein, dass es an der Uhrzeit liegt und ich nichtmehr so fit bin, aber so ganz steige ich gerade nicht hinter die Struktur Deiner DB. Kannst Du bitte mal ganz grob Deine Tabellenstrukturen beschreiben und mit Worten sagen, was Du brauchst? Ich glaube, das ist einfacher!

Einfach
  describe TABELLENNAME;
für die wichtigen Tabellen. Das reicht schon.

Beste Grüße
  ~ Thilo

[Speedy]

Also :)

Ich habe eine Tabelle, in der stehen bspw. Personen drin.
Dann gibt es eine zweite Tabelle, in der stehen bestimmte Eigenschaften/Fähigkeiten der Personen drin. Diese Tabelle hat dementsprechend ein Feld, dessen Wert ich der ersten Personen-Tabelle zuordnen kann.
Nun gibt es noch eine dritte Tabelle, in der Bewertungen der Personen drin stehen.

Ich möchte nun alle 3 Tabellen kombinieren, was an sich kein Problem ist (Joins).
Nun möchte ich aber von den Bewertungen pro Person nur 10 auslesen. Dies bereitet mir Schwierigkeiten -.-

Das Problem meines geposteten Querys:
Wenn ich im Subquery ein "Limit 10" setze, dann ist nicht sichergestellt, dass ich 10 Bewertungen zum entsprechenden Benutzer erhalte, da die ON-Anweisung, welche mir die Bewertungen auf den "einen" User beschränkt, erst später beachtet wird.

Eine Möglichkeit mein Problem zu lösen wäre also irgendwie innerhalb des Subquerys nur Bewertungen des Users einer bestimmten ID auszulesen, doch wie komme ich innerhalb des Subquerys an die Personen-ID heran?

Mein zweiter Lösungsansatz wäre das Ergebnis LEFT JOIN für die Bewertungen einzuschränken, sodass ich aus dem Ergebnis nur 10 Einträge erhalte. Allerdings weiss ich nicht, wie das zu realisieren geht.

Leider komm ich nicht weiter :'(

[Thilo Fester]

Hey Speedy!

Ich habe eine Tabelle, in der stehen bspw. Personen drin.
Dann gibt es eine zweite Tabelle, in der stehen bestimmte Eigenschaften/Fähigkeiten der Personen drin. Diese Tabelle hat dementsprechend ein Feld, dessen Wert ich der ersten Personen-Tabelle zuordnen kann.
Nun gibt es noch eine dritte Tabelle, in der Bewertungen der Personen drin stehen.

Also ich denke, die Aufteilung, so wie Du sie jetzt machst, ist vielleicht nicht ganz vernünftig.

Also ich halte einmal grob fest:

table1 ( benutzer (b)):
+-------------------+---- ...
| benutzer_id (key) | name  ...
+-------------------+-------- ...
|                   |
|       ...         |  ...
|                   |

table2 ( eigenschaften (e)):
+-----------------------+-------------+------
| eigenschafts_id (key) | benutzer_id |  ...
+-----------------------+-------------+----------
|                       |             |
|        ...            |    ...      |  ...
|                       |             |

table3 ( ratings (r)):
+-----------------------+-------------+------------+---
| bewertungs_id (key)   | benutzer_id |  bewertung | ...
+-----------------------+-------------+------------+-------
|                       |             |            |
|        ...            |    ...      |     ...    |  ...
|                       |             |            |

  
SELECT *  
FROM table1 b,  
     table2 e,  
     ( SELECT * FROM table1 sub_b, table3 sub_r WHERE sub_b.benutzer_id = sub_r.benutzer_id ORDER BY sub_r.bewertung LIMIT 10 ) r  
WHERE b.benutzer_id = e.benutzer_id  
AND b._benutzer_id = r.benutzer_id  

Das sollte Dir sauber alle Felder liefern, die auf die 3 Tabellen passen und bei denen nur die besten 10 Bewertungen je benutzer beachtet werden.

Mit den Feldern kannst Du dann ja noch arbeiten, wie es nötig ist.

Hilft Dir das?

Beste Grüße
  ~ Thilo

[Thilo Fester]

Sorry, hab was vergessen ^^ Doppelpost

Also ich denke, die Aufteilung, so wie Du sie jetzt machst, ist vielleicht nicht ganz vernünftig.

--> Damit meine ich, dass Du die Tabellen wahrscheinlich anders gestalten könntest und Dir damit Arbeit und Deiner Anwendung Zeit ersparrst!

[Speedy]

SELECT *
FROM table1 b,
     table2 e,
     ( SELECT * FROM table1 sub_b, table3 sub_r WHERE sub_b.benutzer_id = sub_r.benutzer_id ORDER BY sub_r.bewertung LIMIT 10 ) r
WHERE b.benutzer_id = e.benutzer_id
AND b._benutzer_id = r.benutzer_id

>   
> Das sollte Dir sauber alle Felder liefern, die auf die 3 Tabellen passen und bei denen nur die besten 10 Bewertungen je benutzer beachtet werden.  
>   
  
Dem ist leider nicht ganz so, denn ich möchte ja nicht jeden User haben, sondern die Auswahl einschränken :)  
In dem SubQuery werden aber alle Bewertungen ausgelesen und dann sofort auf 10 beschränkt, ohne dass ich sagen kann, welche Personen ich haben möchte.  
  
Betrachten wir nur den Subquery. Ich möchte meine Benutzerwahl einschränken können, zB anhand des Namen:  
~~~sql
  
SELECT p.id  
FROM personen AS p  
WHERE name LIKE '%Mustermann%'  

Nun sollen ja innerhalb des Subquerys noch die Bewertungen ausgelesen werden, allerdings nur 10. Nach deinem Beispiel müsste der Subquery dann so aussehen (ohne 10er-Begrenzung):

  
SELECT p.id  
FROM personen AS p, bewertungen AS b  
WHERE name LIKE '%Mustermann%'  
AND p.id = bewertungen.person

Hier wird klar, dass ich mit dem Subquery nicht weit komme.
Denn es muss nicht unbedingt eine Bewertung existieren (habe ich vergessen zu sagen :X). Damit würden nur Personen ausgelesen werden, die bewertet worden sind.
Also muss ich es mittels einen Left Joins lösen.

Aber da komme ich nicht weiter.
Wenn ich alle Bewertungen einbeziehe ist das kein Problem. Einfach ein paar Joins und fertig. Aber wichtig ist mir, dass ich das auf 10 Bewertungen beschränke.
Wie oben bereits genannt sollen dabei auch User ausgelesen werden, die noch gar nicht bewertet worden sind.

Gruss
Speedy

[Thilo Fester]

Heyho nochmal!

SELECT *
FROM table1 b,
     table2 e,
     ( SELECT * FROM table1 sub_b, table3 sub_r WHERE sub_b.benutzer_id = sub_r.benutzer_id ORDER BY sub_r.bewertung LIMIT 10 ) r
WHERE b.benutzer_id = e.benutzer_id
AND b._benutzer_id = r.benutzer_id

  

> Dem ist leider nicht ganz so, denn ich möchte ja nicht jeden User haben, sondern die Auswahl einschränken :)  

Ja meine Anfrage trifft in der Tat keine Auswahl. Ich bin davon ausgegangen, dass Du die Dir dann einfach selbst einbaust ^^  
  
Wenn Du in die WHERE-Klausel "AND b.name LIKE '%Mustermann'" ergänzt, sollte es Dein Ergebnis liefern.  
Jetzt brauchen wir offensichtlich noch die User, die unbewertet sind:  
  
~~~sql
  
( SELECT b.feldX, e.feldY  
FROM table1 b,  
     table2 e,  
     ( SELECT * FROM table1 sub_b, table3 sub_r WHERE sub_b.benutzer_id = sub_r.benutzer_id ORDER BY sub_r.bewertung LIMIT 10 ) r  
WHERE b.benutzer_id = e.benutzer_id  
AND b.benutzer_id = r.benutzer_id  
AND b.name LIKE '%MEIER' )  
UNION  
( SELECT b.feldX, e.feldY  
FROM table1 b,  
     table2 e,  
WHERE b.benutzer_id = e.benutzer_id  
AND b.benutzer_id <> ALL ( SELECT benutzer_id FROM table3  )  
AND b.name LIKE '%MEIER' )  

Einfach durch ein UNION realisiert. Achte darauf, dass die Felder der beiden zu vereinigenden Ergebnisse mit ihren Feldern stimmig sind und bau je nachdem wie Du mit denen, die unbewertet sind, noch verfahren willst, Bedingungen in die zweite Abfrage ein.

Wäre schön, wenn Du die auftretenden Probleme genauer beschreiben könntest. Wusste ja keiner, dass die Abfrage schon fast so läuft, wie Du möchtest, und es fehlten nur noch die Leute ohne Bewertung ( oder hab ich das jetzt missverstanden?)

Am Besten wär wie gesagt immernoch, die konkrete Problemstellung ( sinngemäßg, nicht syntaktisch und nicht anfragespezifisch) und der konkrete Aufbei Deiner Tabellen.
Damit wär Dein Problem schon gestern Abend gelöst ; )

Beste Grüße
  ~ Thilo : )

[Speedy]

( SELECT b.feldX, e.feldY
FROM table1 b,
     table2 e,
     ( SELECT * FROM table1 sub_b, table3 sub_r WHERE sub_b.benutzer_id = sub_r.benutzer_id ORDER BY sub_r.bewertung LIMIT 10 ) r
WHERE b.benutzer_id = e.benutzer_id
AND b.benutzer_id = r.benutzer_id
AND b.name LIKE '%MEIER' )
UNION
( SELECT b.feldX, e.feldY
FROM table1 b,
     table2 e,
WHERE b.benutzer_id = e.benutzer_id
AND b.benutzer_id <> ALL ( SELECT benutzer_id FROM table3  )
AND b.name LIKE '%MEIER' )

>   
>   

Das sieht eigentlich shcon ganz gut aus.  
Problem ist aber das LIMIT 10 im Subquery.  
Ich habe den Subquery so abgeändert, dass ich da auch meine Personensuche drin habe. Er findet dadurch alle entsprechenden Personen und deren Bewertungen, soweit ganz gut. Aber das LIMIT 10 beschränkt nun alle Bewertungen, und nicht für jeden Benutzer einzeln.  
  
PS: Ein paar Postings weiter oben habe ich das genaue Beispiel gepostet.  
  
Gruss  
Speedy

[ChrisB]

Hi,

Wie bekomme ich die Where-Bedingung im Sub-Query hin, damit diese der  ON-Bedingung dahinter entspricht?
Leider kann ich innerhalb des SQ ja nicht auf die Werte des Queries drum herum zugreifen.

Why not?

Correlated Subqueries machen doch genau das - "[a] correlated subquery is a subquery that contains a reference to a table that also appears in the outer query."

MfG ChrisB

[Speedy]

Correlated Subqueries machen doch genau das - "[a] correlated subquery is a subquery that contains a reference to a table that also appears in the outer query."

Das hatte ich auch gefunden, allerdings funktioniert das bei mir nicht.
Scheint daran zu liegen, dass die Querys irgendwie nicht direkt miteinander zusammenhängen?
Also weil der Subquery teil eines Left-Joins ist und somit nicht direkt mit dem Main-Query zusammenhängt?

Auf jeden Fall funktionierts nicht :'(

[dedlfix]

echo $begrüßung;

Correlated Subqueries machen doch genau das - "[a] correlated subquery is a subquery that contains a reference to a table that also appears in the outer query."
Das hatte ich auch gefunden, allerdings funktioniert das bei mir nicht.

Ein Kapitel weiter Subqueries in the FROM clause steht auch die Begründung dazu: "Subqueries in the FROM clause cannot be correlated subqueries"

Scheint daran zu liegen, dass die Querys irgendwie nicht direkt miteinander zusammenhängen?

Das liegt wohl eher daran, dass zuerst die Ergebnismenge der Subquery ermittelt wird, und dann erst die Verknüpfung mit der Hauptquery vorgenommen wird. Du müsstest also als Ergebnis der Subquery die 10 gewünschten Datensätze aller Personen haben. Und da weiß ich nicht, wie das abzufrage wäre. GROUP BY ginge unter Umständen, aber nur ohne die LIMIT-Einschränkung pro Nase.

Was brauchst du denn aus dieser Tabelle? Am Ende nur eine Punktzahl pro Teilnehmer, also ein skalares Ergebnis (ein einzelner Wert)? Dann kannst du sie in der SELECT-Klausel unterbringen. Die Ausdrücke in der SELECT-Klausel werden erst dann berechnet, wenn die Ergebnismenge aus den FROM-/JOIN-, WHERE- und GROUP BY-Klauseln ermittelt wurde. Die Subquery wird dann also zu jedem Datensatz der Ergebnismenge einzeln berechnet und kann dabei auch auf die Werte der anderen Felder Bezug nehmen.

Auf jeden Fall funktionierts nicht :'(

Auf jeden Fall ist "funktioniert nicht" immer noch keine für andere nachvollziehbare Fehlerbeschreibung. Aus einer konkreten Beschreibung inklusive Fehlermeldung kann man im günstigen Fall schon die Ursache erkennen, ohne das Problem erst nachzustellen. In deinem Fall wäre wohl die Aussage, dass eine Fehlermeldung "unknown column table1.field in where clause" kommt, hilfreicher gewesen. Daraus habe zumindest ich erkennen können, dass bei der Auswertung der Subquery die Haupt-Query nicht berücksichtigt wird, und habe daraus schließen können, dass MySQL die Ergebnismengen der FROM-Klausel zunächst unabhängig voneinander ermitteln können muss. Du hast jedoch vor, dass die Ergebnismenge der Subquery für jeden Datensatz der Hauptquery eine andere sein soll.

echo "$verabschiedung $name";

[Speedy]

Was brauchst du denn aus dieser Tabelle? Am Ende nur eine Punktzahl pro Teilnehmer, also ein skalares Ergebnis (ein einzelner Wert)? Dann kannst du sie in der SELECT-Klausel unterbringen. Die Ausdrücke in der SELECT-Klausel werden erst dann berechnet, wenn die Ergebnismenge aus den FROM-/JOIN-, WHERE- und GROUP BY-Klauseln ermittelt wurde. Die Subquery wird dann also zu jedem Datensatz der Ergebnismenge einzeln berechnet und kann dabei auch auf die Werte der anderen Felder Bezug nehmen.

Das stimmt leider nicht ganz, so wie es aussieht.
Ich habe meine Bewertungen jetzt mittels Join eingebunden.
Im Haupt-Query steht jetzt sowas (verkürzt):

SELECT felder..., ( SELECT COUNT(*) FROM jointabelle LIMIT 10)
FROM ...
LEFT JOIN table AS jointabelle
...

Das war nur mal ein Test, um innerhalb des Selects in einem SubQuery auf die Tabelle des Joins zuzugreifen.
Doch sagt mySQL mir, dass ein db.jointabelle nicht existiert.

Scheinbar kann ich in dem Subquery im SELECT nicht auf die Tabelle drum herum zugreifen.

PS:
Um deine Frage zu beantworten: Theoretisch brauche ich bestimmte Personen einer Tabelle und deren Eigenschaften aus einer anderen Tabelle, sowie Bewertungen zu diesen aus einer dritten Tabelle. Von den Bewertungen allerdings maximal 10.
Die Bewertungen teilen sich in 3 Kategorien auf (also eine extra Tabelle). Trotzdem wäre dein Ansatz ein Lösungsweg gewesen, da ich die Ergebnisse auch mit den CONCAT Funktionen hätte zusammenführen können (nur eine Spalte).
Doch leider funktioniert die o.g. Möglichkeit ja nicht.

Gruss
Speedy

[Vinzenz Mai]

Hallo speedy,

Ich habe meine Bewertungen jetzt mittels Join eingebunden.
Im Haupt-Query steht jetzt sowas (verkürzt):

das ist Unsinn. Vielleicht kürzt Du ja gerade das heraus, was interessant
oder wichtig ist. Viel besser wäre es ein paar Beispieltabellen mit genügend
Beispieldatensätzen zu zeigen, dazu das gewünschte Ergebnis, mit der Begründung,
warum Du dieses Ergebnis wünschst.

SELECT felder..., ( SELECT COUNT(*) FROM jointabelle LIMIT 10)
FROM ...
LEFT JOIN table AS jointabelle

vielleicht schaust Du Dir mal folgenden Archivthread an, vielleicht hilft Dir das dort vermittelte weiter.

Freundliche Grüße

Vinzenz

[Speedy]

OK, ich habe ein paar Postings weiter oben nun ein komplettes Beispiel gepostet.

[dedlfix]

echo $begrüßung;

Im Haupt-Query steht jetzt sowas (verkürzt):
SELECT felder..., ( SELECT COUNT(*) FROM jointabelle LIMIT 10)
FROM ...
LEFT JOIN table AS jointabelle

Du lässt schon wieder zu viel aus, oder zeigst zu wenig, was du konkret vorhast. Aus der Beschreibung ist das nämlich auch nicht eindeutig zu erkennen. Ich sehe beispielsweise nicht, welche konkreten Daten du aus der Bewertungstabelle benötigst. Sind es verschiedene (max. 10) Datensätze für jeden Benutzer, oder willst du am Ende nur eine Summe pro Nase? Zeige doch bitte ein auf das eigentliche Problem gekürztes und vor allem nachvollziehbares Beispiel, inklusive ein paar Beispieldaten und des gewünschten Ergebnisses. (Damit es nicht zu viele werden, kannst du ja aus der 10 eine 2 machen.)

Bei der Abfrage oben kann ich nicht, ob es überhaupt sinnvoll ist, die Tabelle zu joinen, oder sie einfach nur als Subselect in der SELECT-Klausel zu verwenden. Und in dem Subselect fehlt mir der Bezug zu einer Benutzer-ID.

echo "$verabschiedung $name";

[Speedy]

OK, hier mal die Tabellen als Beispiel:

--- Die Personen

CREATE TABLE personen (
  id int(11) NOT NULL auto_increment,
  name varchar(100) collate latin1_general_ci NOT NULL,
  activated int(11) NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;

INSERT INTO personen (id, name, activated) VALUES
(1, 'Peter Schmidt', 1),
(2, 'Peter Mustermann', 1),
(3, 'Peter Nachname', 0);

--- Und die entsprechenden Bewertungen

CREATE TABLE bewertung (
  id int(11) NOT NULL auto_increment,
  person int(11) NOT NULL,
  kommentar varchar(100) collate latin1_general_ci NOT NULL,
  b\_aussehen tinyint(4) NOT NULL,
  b\_charakter tinyint(4) NOT NULL,
  b\_sonstiges tinyint(4) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

INSERT INTO bewertung (id, person, kommentar, b\_aussehen, b\_charakter, b\_sonstiges) VALUES
(1, 1, 'bla', 3, 1, 2),
(2, 1, 'bla2', 1, 2, 1),
(3, 1, 'bla3', 3, 3, 1),
(4, 3, 'wow', 3, 3, 3);

Jetzt nochmal ganz ausführlich, was ich möchte :-)
1. Personen auslesen, wobei ich anch namen suche. Außerdem möchte ich in diesem Fall nur aktiviert Personen. Der erste Ansatz sieht also so aus:

SELECT * FROM personen WHERE name LIKE '%peter%' AND activated='1'

2. Nun möchte ich die Bewertungen dazunehmen. In diesesm Fall jetzt aber maximal 2 Bewertungen (für Person zB 1 gibts ja 3).
Die Spalten b_aussehen, b_charakter und b_sonstiges enthalten die Bewertung (Punkte von 1 bis 3).
Die Bewertung wollte ich folgendermaßen ausrechnen lassen:
SELECT SUM(b_aussehen)/COUNT(b_aussehen) AS AussehenBewertung, SUM(b_charakter)/COUNT(b_charakter) AS CharakterBewertung, SUM(b_sonstiges)/COUNT(b_sonstiges) AS SonstigeBewertung FROM bewertung WHERE person='1' ORDER BY id DESC LIMIT 2
Hier merke ich jedoch gerade, dass den COUNT()s und SUM()s scheinbar das LIMIT egal ist. Die beziehen alle Bewertungen, statt nur 2, mit ein.

3. 1. und 2. möchte ich nun in einer Abfrage kombinieren, wobei auch Personen ausgelesen werden sollen, die noch nicht bewertet worden sind. Desweiteren sollten wie gesagt nur 2. Bewertungen PRO PERSON mit einbezogen werden.

Danke,
Speedy

[Vinzenz Mai]

Hallo Speedy,

*seufz*, warum ist es so schwer, genau das zu liefern, was dedlfix und ich
nachgefragt haben. Um es klar zu stellen, SQL-Statements waren eher
unerwünscht.

INSERT INTO personen (id, name, activated) VALUES
(1, 'Peter Schmidt', 1),
(2, 'Peter Mustermann', 1),
(3, 'Peter Nachname', 0);

Tabelle "personen"

id | name             | activated
----------------------------------
 1 | Peter Schmidt    | 1
 2 | Peter Mustermann | 1
 3 | Peter Nachname   | 0

--- Und die entsprechenden Bewertungen

INSERT INTO bewertung (id, person, kommentar, b\_aussehen, b\_charakter, b\_sonstiges) VALUES
(1, 1, 'bla', 3, 1, 2),
(2, 1, 'bla2', 1, 2, 1),
(3, 1, 'bla3', 3, 3, 1),
(4, 3, 'wow', 3, 3, 3);

Tabelle "bewertung"

id | person | kommentar | b_aussehen | b_charakter | b_sonstiges
----------------------------------------------------------------
 1 |     1  | bla       |        3   |         1   |  2
 2 |     1  | bla2      |        1   |         2   |  1
 3 |     1  | bla3      |        3   |         3   |  1
 4 |     1  | wow       |        3   |         3   |  3

Jetzt nochmal ganz ausführlich, was ich möchte :-)

1. Personen auslesen, wobei ich anch namen suche. Außerdem möchte ich in diesem Fall nur aktiviert Personen.

2. Nun möchte ich die Bewertungen dazunehmen. In diesesm Fall jetzt aber maximal 2 Bewertungen (für Person zB 1 gibts ja 3).

3. 1. und 2. möchte ich nun in einer Abfrage kombinieren, wobei auch Personen ausgelesen werden sollen, die noch nicht bewertet worden sind. Desweiteren sollten wie gesagt nur 2. Bewertungen PRO PERSON mit einbezogen werden.

und welche Bewertungen bitte?
Für alle Bereiche die gleichen Bewertungen?
Für jeden Bereich nur die besten zwei Bewertungen?

Grundsätzlich ist Dein Tabellenmodell ungünstig gewählt, Du solltest diese
Tabelle in mindestens drei Tabellen aufteilen:

a) kriterien mit den Spalten (id, kriterium)
b) kommentare (geeignet auslagern :-))
c) bewertungen (id, person_id, kriterium_id, bewertung)

Mehr zur Normalisierung findest Du in SELFHTML aktuell, Artikel Einführung in Joins.

Freundliche Grüße

Vinzenz

[dedlfix]

echo $begrüßung;

Jetzt nochmal ganz ausführlich, was ich möchte :-)

1. Personen auslesen, wobei ich anch namen suche. Außerdem möchte ich in diesem Fall nur aktiviert Personen.

Die beiden Bedingungen sind trivial, weswegen ich nicht weiter auf sie eingehe.

2. Nun möchte ich die Bewertungen dazunehmen.

Wie sollen diese in die Ergebnismenge des ersten Punktes eingebunden werden? Das unterschlägst du jedes Mal.

Die Bewertung wollte ich folgendermaßen ausrechnen lassen:
SELECT SUM(b_aussehen)/COUNT(b_aussehen) AS AussehenBewertung, SUM(b_charakter)/COUNT(b_charakter) AS CharakterBewertung, SUM(b_sonstiges)/COUNT(b_sonstiges) AS SonstigeBewertung FROM bewertung WHERE person='1' ORDER BY id DESC LIMIT 2

Du hättest schon längst mal die konkrete Ergebnismenge benennen sollen. Diese Berechnung hast du zwar schon im Ausgangsposting angedeutet, aber es fehlten da die konkreten Beziehungen zu den beteiligten Tabellen. Aus obiger Abfrage schließe ich nun, dass das Ergebnis so aussehen soll:

1, Peter Schmidt', bewertung
2, Peter Mustermann', bewertung
3, Peter Nachname', bewertung

Es wäre nämlich auch möglich gewesen, dass du so etwas haben möchtest:

1, Peter Schmidt', bewertung_1
1, Peter Schmidt', bewertung_2
...
1, Peter Schmidt', bewertung_10
2, Peter Mustermann', bewertung_1
2, Peter Mustermann', bewertung_2
...
2, Peter Mustermann', bewertung_10
3, Peter Nachname', bewertung_1
3, Peter Nachname', bewertung_2
...
3, Peter Nachname', bewertung_10

Für diesen Fall wäre nämlich die Lösung anders ausgefallen.

Hier merke ich jedoch gerade, dass den COUNT()s und SUM()s scheinbar das LIMIT egal ist. Die beziehen alle Bewertungen, statt nur 2, mit ein.

LIMIT wirkt erst ganz zum Schluss, wenn bereits alles berechnet ist und die Ergebnismenge feststeht, auf ebendiese ein. Wenn du die zu verwendenden Datensätze damit einschränken willst, brauchst du zunächst eine Ergebnismenge aus allen Datensätzen (sortiert natürlich). In deinem Fall brauchst du also pro Nase eine solche limitierte Ergebnismenge. Erst dann willst du damit Berechnungen anstellen.

Es ist wichtig, die Abarbeitungsreihenfolge der Klauseln eines SELECT-Statements zu kennen, damit man nicht beispielsweise das LIMIT zum falschen Zeitpunkt erwartet. Abgesehen von internen Optimierungen (die für das Verständnis der Arbeitsweise eines SELECT-Statements nicht weiter interessieren), ist die Reihenfolge so:

FROM(inkl.JOIN)->WHERE->SELECT/GROUP->HAVING->ORDER->LIMIT

Bis auf das SELECT (sprich: die Ergebnisspalten) ist das die gleiche Reihenfolge, in der die Klauseln notiert werden müssen. GROUP BY hängt quasi mit SELECT zusammen. Wenn eine GROUP BY-Klausel vorhanden ist, hat das massiven Einfluss auf die verwendbaren Ergebnisspalten-Ausdrücke, doch das ist ein anderes Thema. Aus dieser Reihenfolge geht auch hervor, warum beispielsweise im WHERE keine Aliasnamen aus dem SELECT genommen werden können. Sie sind zu dem Zeitpunkt schlicht noch nicht berechnet.

Ich kenne keine Möglichkeit, Datensätze anders als mit LIMIT auf eine definierte Anzahl hin einzuschränken. Somit muss für jede Person diese Abfrage einzeln durchgeführt werden. In der FROM-Klausel geht es nicht, weil diese eine komplette Datenmenge verlangt. Dafür müsstest du jede einzelne deiner limitierten Datenmengen wieder zu einer großen zusammenfügen. Dann wäre es möglich, sie mit den Personen zu JOINen, zu GROUPieren und die Aggregatfunktionen darauf anzuwenden.

Mir fällt da nur als möglicher Platz die SELECT-Klausel ein. Zu dem Zeitpunkt hast du pro Nase einen Datensatz und weißt auch die ID, die du in einem korrelierten Subquery als Bedingung verwenden kannst. Ein Subquery als Ergebnisspaltenausdruck (sprich: in der SELECT-Klausel) darf aber nur einen einzelnen skalaren Wert liefern (keine Zeile, keine Spalte, keine Matrix). Du willst 3 Werte haben, also brauchst du 3 Subquerys. Und wegen der LIMIT-Problematik musst du die Subquerys nochmal "subqueryen", damit du ein Aggragatergebnis bekommst. So sähe das insgesamt nur mit der Aussehen-Subquery aus:

SELECT
    id,   # AS id_test
    name,
    (
      SELECT SUM(b_aussehen)/COUNT(b_aussehen)
      FROM (
        SELECT b_aussehen
        FROM bewertung
        WHERE person = personen.id
        ORDER BY id DESC
        LIMIT 2
      ) AS aussehen
    ) aussehen
  FROM personen

Doch leider spielt hier MySQL nicht mit, da personen.id sich über zwei Verschachtelungen hinweg auf die äußere Query bezieht. Auch eindeutig benannt als id_test oder als userdefinierte Variable will es nicht. Als Workaround fällt mir da nur ein:

SELECT
    id,
    name,
    (
       SELECT SUBSTRING_INDEX(GROUP_CONCAT(b_aussehen ORDER BY id DESC), ',', 2)
       FROM bewertung
       WHERE person = personen.id
    ) aussehen
  FROM personen

Das ergibt als Ergebnismenge:

id name              aussehen
  1  Peter Schmidt     3,1
  2  Peter Mustermann  NULL
  3  Peter Nachname    3

GROUP_CONCAT() erzeugt eine kommaseparierte Liste in String-Form. SUBSTRING_INDEX() bildet das LIMIT nach, indem es alles ab dem (hier) zweiten Komma abschneidet.

Die eigentliche Berechnung muss dann im auswertenden Programm erfolgen. Unter PHP kann man den Wert in der Spalte "aussehen" am Komma explodieren, und auf das entstandene Array array_sum() und count() anwenden.

echo "$verabschiedung $name";

[Speedy]

Hi,

den Workaround fand ich sehr interessant und nachvollziehbar.
Doch leider gibt mir mySQL statt der komma-separierten Liste der Bewertungen immer ein "[BLOB - 11 Bytes]" aus.

Woran kann das liegen? Ich habe testweise mal den "ORDER BY" und alles entfernt --> ohne Erfolg.

Gruss
Speedy

PS: mySQL v5.0.44

[Speedy]

Okay, dazu habe ich folgendes gefunden:

Beginning with MySQL 5.0.19, the type returned by GROUP_CONCAT() is always VARCHAR unless group_concat_max_len is greater than 512, in which case, it returns a BLOB. (Previously, it returned a BLOB with group_concat_max_len greater than 512 only if the query included an ORDER BY  clause.)

Gibt es dafür auch ein Workaraound? :)

[dedlfix]

echo $begrüßung;

Doch leider gibt mir mySQL statt der komma-separierten Liste der Bewertungen immer ein "[BLOB - 11 Bytes]" aus.

Das ist die Ausgabe vom phpMyAdmin. Die ist doch wohl für deine Anwendung uninteressant, oder? Der PMA denkt sich: "Mist, BLOB, das sind Binärdaten, die kann ich doch nicht darstellen." Doch da irrt er sich in dem Fall.

Unter PHP beispielsweise bekommt man den Unterschied zwischen Binärdaten und einem String nicht mit, wenn man nicht explizit in den Metadaten der Ergebnisspalten nachschaut. Für PHP ist ein BLOB auch nur ein String.

Ein expliziter (CAST()) oder impliziter (CONCAT('', BLOB)) Typecast nach String korrigiert auch noch die Metadaten.

echo "$verabschiedung $name";