✔ MYSQL - zeige nur bestimmte Einträge (nicht LIMIT)

[k3nguruh]

Hallo,

ich weiss nicht mal genau nach welchen "Begriff" ich suchen sollte. Deshalb stelle ich meine Frage mal hier und hoffe dass mir einer auf die Sprünge hilft.

Gegeben eine Tabelle mit zig Datensätze:

id   |  name    | datetime
-------------------------------------
1    |  name... | 2018-01-01 20:00:00
2    |  name... | 2018-01-02 07:00:00
....
1234 |  name... | 2020-01-01 12:12:12
1235 |  name... | 2020-01-01 14:14:14
....
X    |  name... | XXXX-XX-XX XX:XX:XX

Ich möchte jetzt gerne, dass mir der 100, 112, 200, 300, xxx Eintrag aus dem Jahr XXXX ausgegeben wird

count_id | name    | datetime
----------------------------------------
100      | name... | 2020-07-15 15:00:00
112      | name... | 2020-07-20 17:00:00
200      | name... | 2020-09-09 09:00:00
....

Danke.

 

[ComFreek]

Ich wurde auf Google fündig mit Stichwörtern sql select even rows (als Spezialfall deines Problems) und sql select with line number:

How to Number Rows in SQL

Ich habe hier mal ein reproduzierbares Beispiel für deinen Anwendungsfall erstellt: MySQL 8.0 | db<>fiddle.

Wir starten mit dieser Tabelle und ihrem Inhalt:

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int AUTO_INCREMENT,
  `year` int,
  `msg` varchar(20),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO mytable (year, msg) VALUES
  (2021, "C"), (2020, "B"), (2020, "A"), (2020, "D"), (2021, "E");

Angenommen wir wollen den ersten und dritten Eintrag aus dem Jahr 2020 erhalten. Und mit "ersten" und "dritten" meinen wir, dass wir innerhalb des Jahres 2020 anhand id sortieren. Dann geht das so:

SELECT
  t.id,
  year,
  msg
FROM
  (SELECT
     ROW_NUMBER() OVER(ORDER BY id ASC) AS num_row,
     id
   FROM mytable
   WHERE year = 2020
  ) t
JOIN mytable ON mytable.id = t.id
WHERE num_row IN (1, 3)

(Wenn du MySQL nutzt, dann brauchst du mindestens Version 8 dafür.)

Erklärung:

Wir erstellen uns zuerst eine Tabelle (inneres SELECT), welche nur Einträge aus dem Jahr 2020 enthält und welche neben der id-Spalte noch eine spezielle Spalte zum Durchnummerieren enthält. Insbesondere erfolgt die Durchnummeration anhand einer aufsteigenden Sortierung von id (ROW_NUMBER() OVER(ORDER BY id ASC)).

Das einzige, was uns zu dem gewünschten Endergebnis noch fehlt, ist, dass wir nur diejenigen Einträge aus dieser inneren Tabelle rauspicken, deren Durchnummerierungswert 1 oder 3 ist. Zusätzlich können wir noch die ursprünglichen anderen Spalten (year, msg) dazujoinen.

 

[k3nguruh]

Hallo,

erstmal Danke für deinen Beitrag.

Leider kann ich mit MySQL 8.0 nicht dienen (MySQL 5) und die 2. Sache ist noch, dass nicht nach ID sortiert werden kann, sondern es muss nach DATETIME sortiert werden. Es kann sein, dass eine höhere ID ein kleineres Datum hat und somit voher kommt.

Ich habe mich heute nochmal auf die Suche begeben und habe da was gefunden und mit meinen Anpassungen versehen:

SELECT
    *
FROM
    (
    SELECT
        id,
        name,
        datetime,
        @rownum := @rownum + 1 AS rowNum
    FROM
        (
        SELECT
            @rownum := 0
        ) AS r,
        myTable
    WHERE
        YEAR(datetime) = '2020'
    ORDER BY
        datetime ASC
    ) AS ds
WHERE
    rowNum % 100 = 0 OR
    rowNum = 112

Das scheint auch genau so zu funktionieren, wie es auch soll. Nur habe ich absolut keine Ahnung was da genau passiert ;-( , und ob man das vielleicht noch optimieren kann oder so erst gar nicht verwenden sollte.

Vielleicht kannst du / jemand mir etwas dazu sagen.

Ansonsten bliebe ja noch eine ganz normale Abfrage über ALLE Einträge vom Jahr XXXX zu machen und dann mit PHP zu filtern. Dachte halt nur, dass es auch anders gehen könnte.

Edit: Kannte db-fiddle gar nicht, hoffe ich habe mein Beispiel (db<>fiddle) richtig eingetragen ;-)

 

[ComFreek]

Das scheint auch genau so zu funktionieren, wie es auch soll. Nur habe ich absolut keine Ahnung was da genau passiert ;-(

Das übersteigt auch meine SQL-Kenntnisse
Ich würde vermuten, dass @rownum eine Art Variable ist, müsste aber selbst auch in die Doku schauen.

Vielleicht kennt @Yaslaw die Syntax näher?

Edit: Kannte db-fiddle gar nicht, hoffe ich habe mein Beispiel (db<>fiddle) richtig eingetragen ;-)

Ja, hast du. Du hättest alle INSERTs auch in ein Query zusammenfassen können via INSERT INTO ...tablename... (...columns...) VALUES (...first row...), (...second row...), ....

 

[Yaslaw]

So, holt man mich also aus meinen Ferien - keine Angst, bin eh zuhause am PC.
Ich mach mir mal Testdaten und suche eine Lösung. Bi sicher es findet sich was.

Zusammenfassung der Anforderung:
Filtern nach Jahr, sortieren nach Datum und von dem Resultat bestimmte Positionen extrahieren - alles klar.

 

[Yaslaw]

Jepp, dein COde stimmt soweit. Ich filtere immer so tief unten wie möglich.

SELECT ordered.*
FROM (   
        SELECT
            @rownum := @rownum + 1 AS rowNum,
            dat.*
        from
            (SELECT * FROM myTable WHERE YEAR(datetime) = 2020 ORDER BY datetime ASC) dat,
            (SELECT @rownum := 0 ) sys
    ) ordered
WHERE (ordered.rowNum % 10 = 0 OR ordered.rowNum = 12)

Was passiert da. SQL kann man von innen nach aussen lesen. WIr haben ein select auf die Daten mit dem Filter und der Sortierung: [dat]. Dann haben wir eine temporäre Tabelle mit einem Feld mit dem Wert 0: [sys]. Dieser wird der Variable @rownum zugewiesen.
Anschliessend mischen wir diese 2 Tabellen und zählen auf jeder Zeile die Variable um eins hoch. Das Resultat [ordered].
Zu guter Letzt nehmen wir nur die Daten heraus, die einer entsprechenden Zeilennummer entsprechen.

Natürlich kann man das auch vereinfachen und alles in das WHERE am Ende stecken. Dabei ist zu beachten, dass die Varible nur einam hochgezählt werden darf

SELECT
    dat.*
from
    (SELECT * FROM myTable WHERE YEAR(datetime) = 2020 ORDER BY datetime ASC) dat,
    (SELECT @rownum := 0 ) sys
WHERE ((@rownum := @rownum + 1) % 10 = 0 OR @rownum = 12)

 

[ComFreek]

Super Antwort! Danke für die einleuchtende Erklärung.

Natürlich kann man das auch vereinfachen und alles in das WHERE am Ende stecken. Dabei ist zu beachten, dass die Varible nur einam hochgezählt werden darf

Dass das (garantiert) funktioniert, hat mich gerade stutzig gemacht. Laut MySQL :: MySQL 8.0 Reference Manual :: 9.4 User-Defined Variables sagt:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

 

[Yaslaw]

Hm hat was. Die ANleitung zu den Variablen habe ich galub vor 15 Jahren das letzte mal gelesen. Seither teste ich einfach.
Also ist meine Erste VAriante sicherer.

 

[k3nguruh]

Hallo,

besten Dank für deine Erklärung.... und ein Gesundes Neues Gewünscht.