max(date) über mehrere Tabellen

[vic]

HI zusammen,

ich hab folgende Aufgabenstellung, die ich nicht gelöst bekomme.

2 Tabellen in meiner Mysql ...

Tabelle A  
Id Name  
1 aa  
2 bb  
3 cc

Tabelle B  
Id Datum  
1  2010-02-21 03:25:31  
1  2010-02-22 04:25:35  
2  2010-02-21 01:23:31  
2  2010-02-23 04:24:31

Nun will ich eine Tabelle die mir von jeder ID in Tabelle A des neueste Datum aus Tabelle B ausgibt und dazu noch die Differenz an Tagen zu heute.
Ergebnis sollte sein:

ID Name Datum Datediff  
1 aa 2010-02-22 04:25:3 3  
2 bb 2010-02-23 04:24:3 2

Meine Versuch:

SELECT A.id,DATEDIFF("2010-02-25",max(B.date)) FROM A, B  
WHERE A.id = B.id  
AND B.date = (SELECT max(B.date) FROM B)

Das kann nicht funktionieren, da max(B.date) im subquery ja nicht bei allen gleich ist.

Jemand eine Idee?

Danke
vic

[EKKi]

Mahlzeit vic,

Nun will ich eine Tabelle die mir von jeder ID in Tabelle A des neueste Datum aus Tabelle B ausgibt und dazu noch die Differenz an Tagen zu heute.

Nur mal so ins Blaue hinein:

SELECT A.Id  
,      A.Name  
,      X.Datum  
,      DATEDIFF(NOW(), X.Datum) AS Datediff  
  FROM A  
  JOIN (  
        SELECT Id  
        ,      MAX(Datum) AS Datum  
           FROM B  
         GROUP BY Id  
) AS X ON A.Id = X.Id

Probier mal ein wenig damit herum ...

MfG,
EKKi

[Vinzenz Mai]

Hallo,

Tabelle A

Id Name
1 aa
2 bb
3 cc

  

> ~~~perl

Tabelle B  

> Id Datum  
> 1  2010-02-21 03:25:31  
> 1  2010-02-22 04:25:35  
> 2  2010-02-21 01:23:31  
> 2  2010-02-23 04:24:31

Nun will ich eine Tabelle die mir von jeder ID in Tabelle A des neueste Datum aus Tabelle B ausgibt und dazu noch die Differenz an Tagen zu heute.
Ergebnis sollte sein:

ID Name Datum Datediff

1 aa 2010-02-22 04:25:3 3
2 bb 2010-02-23 04:24:3 2

  
schön. Selten beschreibt einer so schön, was er hat und was er bekommen möchte und warum.  
  

> Meine Versuch:  
> ~~~sql

SELECT A.id,DATEDIFF("2010-02-25",max(B.date)) FROM A, B  
  
-- Warum keine wundervollen, schönen und verständlichen INNER JOINS?  
  

> WHERE A.id = B.id  
> AND B.date = (SELECT max(B.date) FROM B)

Easy. Du suchst eine korrelierte Unterabfrage.

Das kann nicht funktionieren, da max(B.date) im subquery ja nicht bei allen gleich ist.

ja eben. Korreliere sie. Die id von innen muss mit der von außen übereinstimmen.

Ergibt:

SELECT  
    A.id,  
    -- Du möchtest die Query nicht jeden Tag neu schreiben wollen  
    DATEDIFF([link:http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_current-date@title=CURRENT_DATE(]), MAX(B1.date))  
FROM  
    A  
INNER JOIN  
    B AS B1 -- wir müssen zweimal auf B zugreifen, daher der Aliasname  
ON  
    A.id = B1.id  
-- Nun schränken wir die Datensätze ein  
WHERE  
    B1.date = (  
        SELECT  
            MAX(B2.date)  
        FROM  
            B B2    -- um den inneren Zugriff vom äußeren unterscheiden zu  
                    -- können,  
        WHERE  
                    -- und wir das Maximaldatum zu jeder ID benötigen  
                    -- Das ist die Korrelation.  
            B1.id = B2.id  
    )  

Freundliche Grüße

Vinzenz

[vic]

Ergibt:

SELECT

A.id,
    -- Du möchtest die Query nicht jeden Tag neu schreiben wollen
    DATEDIFF([link:http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_current-date@title=CURRENT_DATE(]), MAX(B1.date))
FROM
    A
INNER JOIN
    B AS B1 -- wir müssen zweimal auf B zugreifen, daher der Aliasname
ON
    A.id = B1.id
-- Nun schränken wir die Datensätze ein
WHERE
    B1.date = (
        SELECT
            MAX(B2.date)
        FROM
            B B2    -- um den inneren Zugriff vom äußeren unterscheiden zu
                    -- können,
        WHERE
                    -- und wir das Maximaldatum zu jeder ID benötigen
                    -- Das ist die Korrelation.
            B1.id = B2.id
    )

>   
  
  
Danke für den Tipp. Leider dauert das Statement ewig (bis jetzt ca 30 min), sprich es hat bis jetzt noch keine Daten ausgespuckt. :(  
A hat 400 rows, B ca. 1,2 Mio rows. Indices sind drauf. Hm. Ich muss den select jetzt leider abbrechen, da gleich einige batchjobs anfangen. Ich versuch es morgen früh nochmal und geb feedback, ob da noch was kommt.  
  
ciao,  
vic

[Ilja]

moin,

Indices sind drauf.

fragt sich nur welche, das wäre hilfreich zu wissen. auch würde ich die abfrage anders gestallten. meine devise ist immer, JOINS sind "böse". das sind sie nicht wirklich, aber ich vermeide sie, wenn ich es kann, damit die anzahl der datensätze noch stimmt, die ich zurück haben will. deine basis wäre also im diesem falle die tabelle a.

SELECT a.id, a.name
FROM a
;

nun mit der korrelierten unterabfrage noch das datum einbringen. dafür wäre ein index in der b tabelle auf der spalte id sehr, sehr hilfreich.

SELECT a.id, a.name,
      (SELECT MAX(Datum) FROM tabelle b WHERE b.id = a.id) datum
FROM a
;

und nun noch die zeitdifferenz einbringen.

SELECT t.id, t.name, t.datum, DATEDIFF(CURRENT_DATE(), t.datum)
FROM (SELECT a.id, a.name,
      (SELECT MAX(Datum) FROM tabelle b WHERE b.id = a.id) datum
     FROM a
    ) t
;

das sollte entsprechend schnell sein. vorraussetzung ist, dass id in der tabelle a primary key oder zumindest eindeutig ist.

Ilja

[vic]

SELECT t.id, t.name, t.datum, DATEDIFF(CURRENT_DATE(), t.datum)
FROM (SELECT a.id, a.name,
      (SELECT MAX(Datum) FROM tabelle b WHERE b.id = a.id) datum
     FROM a
    ) t
;

Die Abfrage dauerte 6.5139 sek. ... perfekt! DANKE!