MySQL - Abfrage über drei Tabellen mit GROUPBY?


tfact129

Grünschnabel
Guten Abend,
im nachfolgenden Bild seht ihr drei Tabellen.
sqlquery.JPG

Bei der Abfrage soll als Ergebnis jede Maschine (die ja mehrfach in tbl_protocol vorkommt) nur einmal mit dem jüngsten Datum und der condition angezeigt werden. Ich brings einfach nicht hin, dieses Ergebnis anzuzeigen. Kann da jemand helfen?
alfa
11.11.2019​
excellent
beta
27.09.2018​
bad
gamma
23.08.2019​
bad
 

Zvoni

Erfahrenes Mitglied
getestet in SQLite:
SQL:
SELECT t2.machine_name, t1.machine_id, min(t1.date) as datum, t3.condition from tbl_protocol as t1
inner join tbl_machine as t2
on t2.id=t1.machine_id
inner join tbl_condition as t3
on
t3.id=t1.condition
group by t1.machine_id
Keine Ahnung inwieweit man das für MySQL abändern muss
 

Yaslaw

n/a
Moderator
@Zvoni:
1) Unsauber. Da hat es viele Spalten die nicht im GROUP BY sind.
2) Zudem solle t3.condition ja passend zum Datum sein
3) wenn, dann max() für das Jüngste Datum und nicht min() (Also da neueste..)

@tfact129
Mans sollte nie ein Feld date nennen. Das ist ein Name der von SQL selber verwendet wird

Eine kleine Anleitung um den Jüngsten Datensatz pro Guppe auszuwählen: [SQL] Aktuelle Einträge pro Gruppe auslesen [Yaslaw.Info]

Ich habe mich für Einfaches Subquery mit IN() entschieden.
SQL:
select m.machine_name, p.`date`, c.condition
from
    tbl_machine m,
    tbl_protocol p,
    tbl_condition c
where
    m.machine_id = p.machine_id
    and p.condition = c.id
    and (p.machine_id, p.`date`) in (
        select machine_id, max(`date`) as max_date
        from tbl_machine
        group by machine_id
    )
 
Zuletzt bearbeitet:

Zvoni

Erfahrenes Mitglied
Yaslaw,
@ 1) weiss ich. Ist unsauber, aber SQLite lässt das eben zu.
@ 2) Ist es doch. "... ON t3.id=t1.condition" --> t1 ist das Protocol ?!?!?
@ 3) min vs. max. Yo, es ist noch mitten in der Nacht für mich..... :cool:
 

Yaslaw

n/a
Moderator
@ 2) Ja. Aber da auf die Kondition keine Aggregationsfunktion (sum, min, max etc.) angewendet wird, wird wegen 1) darauf gruppiert. Gemäss Beispiel soll jedoch die condition genommen werden, die dem aktuellen Thema pro Maschine entspricht

- Gruppieren nach Maschine
- Pro Gruppe das neuste Datum
- und die entsprechende Condition
 

Zvoni

Erfahrenes Mitglied
Ach so.
Ja, ist richtig, aber wie gesagt: SQLite lässt es zu, zu gruppieren, ohne alle nicht aggregierten Felder in einem Group By zu haben.
Da ich exklusiv nur auf t1 (protocol), und dort auf die id für min(date) (bzw. müsste das max(date) sein) gruppiere, holt sich der inner join nur den jeweiligen Satz aus t2 und t3
 

tfact129

Grünschnabel
Hallo ihr beiden, danke schonmal für eure Mühe
Ich hab beide Abfragen mal ausprobiert mit folgendem Ergebnis
1. Abfrage von Zvoni -> Ergebnis korrekt

sql_result_2.JPG


2. Abfrage von Yaslaw

sql_result.JPG
 

Zvoni

Erfahrenes Mitglied
Muss ich widersprechen:
Für Maschine Beta müsste aber gemäss deinen Beispieldaten aber "bad" rauskommen.
Du bekommst aber ein good
 

Yaslaw

n/a
Moderator
Ok,bei mir ist ein kleiner Fehler drin. Das Subquery muss natürlich auf die protocol-Tabelle zugreifen.

Ich rate allgemein davon ab, diese Schwachstelle in MySQL zu nutzen. Man sollte jedes Feld definieren was damit gemacht werden soll. 1) ist dann klar, was man genau will, 2) ist es kompatibler zu anständigen SQL-Datenbanken

Test: SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.
SQL:
select m.machine_name, p.`date`, c.condition
from tbl_machine m, tbl_protocol p, tbl_condition c
where
    m.machine_id = p.machine_id
    and p.condition = c.id
    and (p.machine_id, p.`date`) in (
        select machine_id, max(`date`) as max_date
        from tbl_protocol
        group by machine_id
    );
Code:
| machine_name |       date | condition |
|--------------|------------|-----------|
|         alfa | 2019-11-11 | excellent |
|        gamma | 2019-08-23 |       bad |
|         beta | 2018-09-27 |       bad |
 

Zvoni

Erfahrenes Mitglied
Jepp, sieht definitiv besser aus, und Yaslaw hat natürlich recht mit seiner Aussage bzgl. den Schwachstellen
 

Neue Beiträge