Abrufen von zwei nächsten und zwei vorherigen Einträgen in einer einzigen SQL-Abfrage

Question

Ich möchte eine Bildergalerie anzeigen, und auf der Ansichtsseite sollte man sich eine Reihe von Miniaturbildern ansehen können: das aktuelle Bild, zusammen mit den beiden vorherigen und den beiden nächsten Einträgen.

Das Problem des Abrufs von zwei next/prev ist, dass ich nicht (wenn ich mich nicht irre) etwas wie MAX(id) WHERE idxx auswählen kann.

Irgendeine Idee?

Hinweis: Die IDs folgen natürlich nicht, da sie das Ergebnis von mehreren WHERE-Instanzen sein sollten.

Danke Marshall

Answer 1

Sie müssen die Variablennamen im Stil von SQL Server entschuldigen, ich weiß nicht mehr, wie MySQL die Variablen benennt.

SELECT *
  FROM photos
 WHERE photo_id = @current_photo_id
 UNION ALL
SELECT *
  FROM photos
 WHERE photo_id > @current_photo_id
 ORDER BY photo_id ASC
 LIMIT 2
 UNION ALL
SELECT *
  FROM photos
 WHERE photo_id < @current_photo_id
 ORDER BY photo_id DESC
 LIMIT 2;

Diese Abfrage geht davon aus, dass Sie möglicherweise nicht zusammenhängende IDs haben. Langfristig könnte es jedoch problematisch werden, wenn Sie viele Fotos in Ihrer Tabelle haben, da TOP oft ausgewertet wird nach die gesamte Ergebnismenge aus der Datenbank abgerufen wurde. YMMV.

In einem Szenario mit hoher Last würde ich wahrscheinlich diese Abfragen verwenden, aber ich würde sie auch regelmäßig vormaterialisieren, so dass jedes Foto eine Spalte PreviousPhotoOne, PreviousPhotoTwo usw. hat. Das ist zwar etwas aufwändiger, aber es funktioniert gut, wenn man viele statische Daten hat und Leistung braucht.

Answer 2

Wenn Ihre IDs fortlaufend sind, können Sie Folgendes tun

where id >= @id-2 and id <= @id+2

Andernfalls müssten Sie meiner Meinung nach union 3 Abfragen, eine um den Datensatz mit der angegebenen ID zu erhalten und zwei weitere, die mit top y order by wie diese

select *
from table
where id = @id

union

select top 2 *
from table
where id < @id
order by id desc

union

select top 2 *
from table
where id > @id
order by id

Die Leistung wird nicht allzu schlecht sein, da Sie keine großen Datenmengen abrufen, aber sie wird auch nicht großartig sein, da Sie eine union .

Wenn Sie feststellen, dass die Leistung ein Problem ist, können Sie Spalten hinzufügen, um die IDs der vorherigen und nächsten Elemente zu speichern; die Berechnung der IDs erfolgt über einen Trigger oder einen Nachtprozess. Dies bedeutet, dass Sie die schwierige Abfrage nur einmal durchführen und nicht jedes Mal, wenn Sie sie benötigen.

Answer 3

Wenn Sie nur an den vorherigen und nächsten Datensätzen nach ID interessiert sind, könnten Sie nicht einfach eine Where-Klausel haben, die WHERE id=xx, xx-1, xx-1, xx+1, xx+2 einschränkt, indem Sie mehrere WHERE-Klauseln oder WHERE IN verwenden?