MySQL - laufende Nummer im / pro Jahr

[k3nguruh]

Hallo,

Ich weiss nicht genau wie ich es beschreiben soll, deshalb erstmal der Code. Vll. kann man anhand der Abfrage bereits erkennen was ich möchte. Der SELECT funktioniert jedenfalls wie gewünscht.

id = int, Primary, AUTO_INCREMENT
code = varchar (irgendein Zufallscode)
fahrzeugname = varchar (zum Bsp.: VW Golf)
nutzung = varchar (zum Bsp.: Privat oder Firma oder ....)
vondatum = datetime (von wann wurde benutzt)

Der Query etwas abgespeckt:

SELECT
    ed.id,
    ed.fahrzeugname,
    ....
    CONCAT(
        LPAD(
            (
                SELECT
                    COUNT(*)
                FROM
                    tbl_fahrzeuge AS sub
                WHERE
                    sub.vondatumzeit <= ed.vondatumzeit
                    AND YEAR(sub.vondatumzeit) = YEAR(ed.vondatumzeit)
            ),
            3,
            0
        ),
        '/',
        YEAR(ed.vondatumzeit),
        ' ',
        '(',
        UPPER(
            LEFT(ed.nutzung, 1)
        ),
        ')'
    ) AS lfdnr
FROM
    tbl_fahrzeuge AS ed
WHERE
    ed.id = '1'
    AND ed.code = '....'

Als Ergebnis soll dann sowas rauskommen bzw. kommt auch raus:

id "1" hat die lfdnr "004/2011 (P)"

Wenn ich aber die WHERE Bedingung im äusseren SELECT weg lasse, um mir alle Einträge anzuschauen, dauert die Abfrage ewig.

Gibt es vll eine bessere Lösung?

 

[Yaslaw]

Sehr unschön. Ein Subselect im FROM-Teil ist nicht wirklich nett.
Je anchdem was für ein Explain-Plan MySQL zusdammenstellt, geht er für jede Zeile extra auf die Tabelle die Anzahl auslesen Besser man lösst das mit einem INNER JOIN

SELECT
    ed.id,
    ed.fahrzeugname,
    CONCAT LPAD(sub.cnt, '/', YEAR(ed.vondatumzeit), ' (', UPPER(LEFT(ed.nutzung, 1)), ')') AS lfdnr
FROM
    tbl_fahrzeuge AS ed
    INNER JOIN (
        SELECT
            s.vondatumzeit,
            COUNT(s.*) AS cnt
        FROM tbl_fahrzeuge AS s
        GROUP BY s.vondatumzeit
    ) AS sub
    ON sub.vondatumzeit <= ed.vondatumzeit
    AND YEAR(sub.vondatumzeit) = YEAR(ed.vondatumzeit)

Ich gebe aber keine Garantie, dass es schneller ist.
Achja, setz ein Index auf vondatumzeit.

 

[k3nguruh]

Hallo,

erstmal danke für deine Antwort.
Einige Klammern haben gefehlt .... habe sie dann erstmal gesetzt.... COUNT(s.*) hatte ebenfalls einen Fehler erzeugt ... geändert.

SELECT
    ed.id,
    ed.fahrzeugname,
    CONCAT(
        LPAD(sub.cnt, 3, 0),
        '/',
        YEAR(ed.vondatumzeit),
        ' (',
        UPPER(
            LEFT(ed.nutzung, 1)
        ),
        ')'
    ) AS lfdnr
FROM
    tbl_fahrzeuge AS ed
    INNER JOIN (
        SELECT
            s.vondatumzeit,
            COUNT(*) AS cnt
        FROM
            tbl_fahrzeuge AS s
        GROUP BY
            s.vondatumzeit
    ) AS sub ON sub.vondatumzeit <= ed.vondatumzeit
    AND YEAR(sub.vondatumzeit) = YEAR(ed.vondatumzeit)

Leider kommt aber nicht das gewünschte Ergebnis.... Wird immer nur lfdnr "001/...." angezeigt. Es wird auch nicht unterschieden nach der Nutzung und Fahrzeugname.

Nochmal kurz zur Verständigung, wie das Ergebnis aussehen sollte (Beispiele):

- das 4. "vondatum" im Jahr 2011 bei "nutzung" Privat von "fahrzeugname" XX = lfdnr = 004/2011 (P)
- das 4. "vondatum" im Jahr 2011 bei "nutzung" Firma von "fahrzeugname" XX = lfdnr = 004/2011 (F)
- das 4. "vondatum" im Jahr 2011 bei "nutzung" Privat von "fahrzeugname" YY = lfdnr = 004/2011 (P)
usw.
- das 2. "vondatum" im Jahr 2012 bei "nutzung" Firma von "fahrzeugname" XX = lfdnr = 002/2012 (F)

Also die "lfdnr" bezieht sich auf "fahrzeugname" "nutzung" "YEAR(vondatum)"

 

[k3nguruh]

Gerade gesehen, dass der CODE im 1. Thread falsch war.... Kann den nich bearbeiten, daher hier nochmal neu.

SELECT
    ed.id,
    ed.fahrzeugname,
    ....
    CONCAT(
        LPAD(
            (
                SELECT
                    COUNT(*)
                FROM
                    tbl_fahrzeuge AS sub
                WHERE
                    sub.vondatumzeit <= ed.vondatumzeit
                    AND YEAR(sub.vondatumzeit) = YEAR(ed.vondatumzeit)
                    AND sub.fahrzeugname = ed.fahrzeugname
                    AND sub.nutzung = ed.nutzung
            ),
            3,
            0
        ),
        '/',
        YEAR(ed.vondatumzeit),
        ' ',
        '(',
        UPPER(
            LEFT(ed.nutzung, 1)
        ),
        ')'
    ) AS lfdnr
FROM
    tbl_fahrzeuge AS ed
WHERE
    ed.id = '1'
    AND ed.code = '....'

 

[Yaslaw]

Das Zusammensetzen des Strings war nicht die Frage. Die Frage war die Performance. Wurde die mit dem JOIN besser?

Den String kannst du so zusammensetzen wie du willst. Ich hatte im Beispiel nur gezeigt, wie du auf den Count zugreifen kannst.