Zazwyczaj tworząc zapytania w SQL unikamy jak ognia (albo jak mrówek) sytuacji, aby rekordy się duplikowały, triplikowały czy też...no właśnie, może ktoś z Was wie jak się nazywa czterokrotne lub pięciokrotne zwielokrotnianie rekordów?
Jednak dziś zajmiemy się sytuacją odwrotną. Chcemy z jakiegoś powodu, aby dany rekord pojawił się dwukrotnie, inny dziesięciokrotnie, a jeszcze inny pozostał pojedynczy.
Zacznijmy od końca:
SELECT t1.imie FROM tabela AS t1
JOIN
(SELECT a.a + (10 * b.a) AS ilosc FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
) AS ilosci ON t1.ilosc>ilosci.ilosc
Przy naszej wejściowej tabeli:
| imie | ilosc |
|---|---|
| Jan | 1 |
| Marek | 10 |
| Stefan | 5 |
| Romek | 44 |
w wyniku tego zapytania otrzymamy 60 rekordów (1 x Jan, 10 x Marek, 5 x Stefan, 44 x Romek).
Jeśli łapiesz się za głowę widząc to zapytanie, to spróbujmy je rozbić na mniejsze, aby zrozumieć krok po kroku co tu się dzieje.
Krok 1:
SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
To zapytanie zwróci 10 rekordów (0, 1, 2, ..., 9).
Krok 2:
SELECT a.a,b.a FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
Mamy tu zapytanie wybierające dwie kolumny z podzapytania a oraz podzapytania b. Łączenie następuje poprzez CROSS JOIN (połączenie krzyżowe). Zapytanie zwróci 100 rekordów:
| a.a | b.a |
|---|---|
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| ... | ... |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 0 | 9 |
| 1 | 9 |
| 2 | 9 |
| 3 | 9 |
| 4 | 9 |
| 5 | 9 |
| 6 | 9 |
| 7 | 9 |
| 8 | 9 |
| 9 | 9 |
Otrzymaliśmy wynik potocznie zwany "każdy z każdym" (nieźle!), a fachowo iloczynem kartezjańskim.
Krok 3:
SELECT a.a+b.a*10 FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
Teraz gdy zsumujemy wartość kolumny z jednego podzapytania (jedności) z pomnożoną przez 10 wartością z kolumny drugiego podzapytania, otrzymamy podobnie jak wyżej 100 rekordów, ale o wartościach:
| a.a+b.a*10 |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| ... |
| 90 |
| 91 |
| 92 |
| 93 |
| 94 |
| 95 |
| 96 |
| 97 |
| 98 |
| 99 |
To zapytanie można trochę uprościć umieszczając w drugim podzapytaniu od razu wartości pomnożone przez 10 (0, 10, 20, itd.), wtedy obliczenie w głównym zapytaniu ma postać a.a+b.a.
Krok 4:
Teraz możemy wrócić do naszego głównego zapytania. Łączymy tabelę wspomnianą na początku z "tabelą liczb" od 0 do 99. Ponieważ łączenie oparte jest na warunku t1.ilosc>ilosci.ilosc, w przypadku każdego imienia rekord będzie multiplikowany tyle razy dopóki ten warunek będzie spełniony. I tak dla imienia Jan i odpowiadającej mu ilości=1, zostanie on zwielokrotniony jeden raz (1>0,1>1), a Stefan pojawi się w wynikach 5 razy (5>0, 5>1, 5>2, 5>3, 5>4, 5>5).
Dalej łapiesz się za głowę?:)
Krok 5:
Być może już zauważyłeś, że w przypadku ilości większej niż 100 przypisanej do danego imienia, nie otrzymamy w wyniku więcej niż 100 rekordów. Dlaczego tak się dzieje? To już zagadka dla Ciebie, Czytelniku.
Przydatne linki:
Do pełna poproszę! Czyli array_fill w MySQL(?)