Ważne: Zastanów się nad aktualizacją do MySQL 8+ i użyj zdefiniowanej i udokumentowanej funkcji ROW_NUMBER () oraz porzuć stare hacki powiązane z ograniczoną funkcjonalnością starą wersją MySQL
Oto jeden z tych hacków:
Odpowiedzi tutaj, które wykorzystują zmienne w zapytaniu głównie / wszystkie wydają się ignorować fakt, że dokumentacja mówi (parafraza):
Nie polegaj na tym, że elementy na liście SELECT są oceniane w kolejności od góry do dołu. Nie przypisuj zmiennych do jednego elementu SELECT i nie używaj ich w innym
Jako takie istnieje ryzyko, że wyrzucą złą odpowiedź, ponieważ zazwyczaj robią to
select
(row number variable that uses partition variable),
(assign partition variable)
Jeśli zostaną one kiedykolwiek ocenione oddolnie, numer wiersza przestanie działać (brak partycji)
Musimy więc użyć czegoś z gwarantowaną kolejnością wykonania. Wpisz PRZYPADEK, GDY:
SELECT
t.*,
@r := CASE
WHEN col = @prevcol THEN @r + 1
WHEN (@prevcol := col) = null THEN null
ELSE 1 END AS rn
FROM
t,
(SELECT @r := 0, @prevcol := null) x
ORDER BY col
Jak zarys ld, kolejność przypisywania prevcol jest ważna - prevcol należy porównać z wartością bieżącego wiersza, zanim przypiszemy mu wartość z bieżącego wiersza (w przeciwnym razie byłaby to wartość aktualnego wiersza col, a nie wartość col poprzedniego wiersza) .
Oto jak to pasuje do siebie:
Pierwszy KIEDY jest oceniany. Jeśli kolumna tego wiersza jest taka sama jak kolumna poprzedniego wiersza, @r jest zwiększane i zwracane z CASE. Te zwracane wartości są przechowywane w @r. Cechą MySQL jest to, że przypisanie zwraca nową wartość tego, co jest przypisane do @r, w wierszach wyników.
Dla pierwszego wiersza w zestawie wyników @prevcol ma wartość null (jest inicjowane do null w podzapytaniu), więc ten predykat ma wartość false. Ten pierwszy predykat zwraca również wartość false przy każdej zmianie kolumny (bieżący wiersz różni się od poprzedniego). Powoduje to ocenę drugiego KIEDY.
Drugi predykat KIEDY jest zawsze fałszywy i istnieje wyłącznie w celu przypisania nowej wartości @prevcol. Ponieważ kolumna tego wiersza jest inna niż kolumna poprzedniego wiersza (wiemy o tym, ponieważ gdyby był taki sam, użyłby pierwszego KIEDY), musimy przypisać nową wartość, aby zachować ją do następnego testu. Ponieważ przypisanie jest wykonane, a następnie wynik przypisania jest porównywany z wartością NULL, a wszystko, co jest utożsamiane z wartością NULL, jest fałszywe, predykat ten jest zawsze fałszywy. Ale przynajmniej jego ocena polegała na zachowaniu wartości col z tego wiersza, dzięki czemu można ją oszacować na podstawie wartości col następnego rzędu
Ponieważ drugie KIEDY jest fałszem, oznacza to, że w sytuacjach, w których kolumna, którą dzielimy według (col) uległa zmianie, to ELSE daje nową wartość @r, restartując numerację od 1
Dochodzimy do sytuacji, w której:
SELECT
t.*,
ROW_NUMBER() OVER(PARTITION BY pcol1, pcol2, ... pcolX ORDER BY ocol1, ocol2, ... ocolX) rn
FROM
t
Ma ogólną formę:
SELECT
t.*,
@r := CASE
WHEN col1 = @pcol1 AND col2 = @pcol2 AND ... AND colX = @pcolX THEN @r + 1
WHEN (@pcol1 := pcol1) = null OR (@pcol2 := col2) = null OR ... OR (@pcolX := colX) = null THEN null
ELSE 1
END AS rn
FROM
t,
(SELECT @r := 0, @pcol1 := null, @pcol2 := null, ..., @pcolX := null) x
ORDER BY pcol1, pcol2, ..., pcolX, ocol1, ocol2, ..., ocolX
Przypisy:
P in pcol oznacza „partycja”, o in ocol oznacza „porządek” - w ogólnej formie usunąłem „poprzednią” z nazwy zmiennej, aby zmniejszyć bałagan wizualny
Nawiasy kwadratowe (@pcolX := colX) = nullsą ważne. Bez nich przypisz null do @pcolX i rzeczy przestaną działać
Kompromisem jest to, że zestaw wyników musi być także uporządkowany według kolumn partycji, aby porównanie z poprzednią kolumną się sprawdziło. Nie można w ten sposób uporządkować numeru początkowego według jednej kolumny, ale zestaw wyników uporządkować w innej. Być może uda się to rozwiązać za pomocą podzapytań, ale uważam, że dokumenty stwierdzają również, że kolejność podzapytań może zostać zignorowana, chyba że zostanie użyte LIMIT i może to mieć wpływ występ
Nie zagłębiłem się w to poza testowaniem, czy metoda działa, ale jeśli istnieje ryzyko, że predykaty w drugim KIEDY zostaną zoptymalizowane (wszystko w porównaniu do wartości null jest zerowe / fałszywe, więc po co zawracać sobie głowy przypisaniem) i nie jest wykonywane , również się zatrzymuje. Wydaje mi się, że tak się nie dzieje, ale chętnie przyjmę komentarze i zaproponuję rozwiązanie, jeśli mogłoby się to zdarzyć
Rozsądne może być rzutowanie wartości null tworzących @pcolX na rzeczywiste typy kolumn w podzapytaniu, które tworzy zmienne @pcolX, a mianowicie: select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)
greatest-n-per-groupaby poprowadzić Cię do podobnych pytań.