Problem z nietypową numeracją wierszy w SQL

Question

Hej. Dawno już pracowałem z bazami danych a teraz mam drobny problem z wymyśleniem odpowiedniego zapytania SQL. Być może istnieje prostsze rozwiązanie, jednak w głowie pozostały same podstawy, dlatego mój problem wydaje mi się trochę nietypowy - w każdym bądź razie - może ktoś tutaj pomoże?

Załóżmy, że mamy jedną prostą tabelę "services" z kilkoma różnymi rekordami:

CREATE TABLE services (
  service_id INT AUTO_INCREMENT KEY,
  number_order INT,
  name VARCHAR(100)
);

INSERT INTO services(number_order, name) VALUES (3, 'Trzecia usługa');
INSERT INTO services(number_order, name) VALUES (1, 'Pierwsza usługa');
INSERT INTO services(number_order, name) VALUES (2, 'Druga usługa');
INSERT INTO services(number_order, name) VALUES (6, 'Szósta usługa');
INSERT INTO services(number_order, name) VALUES (7, 'Siódma usługa');
INSERT INTO services(number_order, name) VALUES (13, 'Trzynasta usługa');

Teraz możemy wyświetlić zawartość tej tabeli, ale warunkiem jest została uporządkowana według kolejności pola "number_order" rosnąco oraz jego wartości do 10 włącznie:

SELECT service_id, number_order, name
FROM services
WHERE number_order <= 10
ORDER BY number_order ASC;

Wynik jest oczywisty:

service_id	number_order	name
2	1	Pierwsza usługa
3	2	Druga usługa
1	3	Trzecia usługa
4	6	Szósta usługa
5	7	Siódma usługa

OK. Jednak chciałbym uzyskać wynikową tabelę zwracającą zawsze 10 rekordów. Czyli w przypadku powyższej bazy danych szukam odpowiedniej kwerendy zwracającej dokładnie poniższy wynik:

number	name
1	Pierwsza usługa
2	Druga usługa
3	Trzecia usługa
4	NULL
5	NULL
6	Szósta usługa
7	Siódma usługa
8	NULL
9	NULL
10	NULL

To tak jakby ta tabela "sercives" w bazie danych była połączona z abstrakcyjną tabelą z rekordami od 1 do 10. Znalazłem takie coś:

WITH RECURSIVE cte (number)
AS (
SELECT 1
UNION ALL
SELECT number + 1
FROM cte
WHERE number < 10
)

To generuje wiersze od 1 do 10:

number
1
2
3
4
5
6
7
8
9
10

Może trzeba iść w tym kierunku, jak poniżej:

SELECT * FROM cte;
WITH RECURSIVE cte AS (
  SELECT 1 AS number
  UNION ALL
  SELECT number + 1
  FROM cte, services
  WHERE cte.number = services.number_order
  AND number < 10
)
SELECT * FROM cte;

Tyle, że w tym przypadku dostanę jedną kolumnę i co dziwne - tylko 4 rekordy:

number
1
2
3
4

Ma ktoś jakieś pomysły? Dodam, że technologia musi być kompatybilna z MySQL. Z góry dzięki. Pozdrawiam.

Powyższy przykład jest dostępny na tym linku SQLFiddle.

Comments

Czy na 1000% to _musi_ być napisane w SQLu? Bo jestem przekonany że obsłużenie tego w aplikacji po odebraniu wyników z bazy będzie prostsze (i wydajniejsze) niż myki z CTE.

---

Wiem, ja też nie chciałbym utrudniać sobie sprawy i wspomóc się np. językiem PHP, jednak moja aplikacja jest tak skonstruowana, że najwygodniej byłoby odebrać gotowe dane z BD. Poza tym to ostatni z problemów w moim zapytaniu SQL, który na chwilę obecną przekroczył już 300 linii kodu. Myślę, żeby wszystkie dane wygenerować za pomocą jednej długiej kwerendy, a z wydajnością wcale nie jest aż tak najgorzej.

Answer 1

Jeśli dobrze zrozumiałem, to moim zdaniem koncepcja z with jest dobra, ale jeżeli masz możliwość (bo nie wiem co masz dalej) to ja bym sobie to odwrócił. Najpierw w with "wygenerował" te 10 rekordów z kolejnymi numerkami - tu nie do końca miałem pomysł, ale patrząc na to co podałeś wyżej i szukając wyszło, że faktycznie można wymyślić konstrukcję tego typu:

SELECT 1 AS n
UNION ALL
SELECT n+1 FROM numbers WHERE n < 10

co może i dziwnie wygląda, ale faktycznie powinno działać. Następnie bym po prostu zrobił selecta z tego co zwróci CTE (nie w ramach niego, tylko już poza nim), a do tego już zwykłego left joina do services.number_order. W ten sposób zawsze w odpowiedzi będzie tyle rekordów, ile wygeneruje CTE, a tylko do istniejących doklei dane z drugiej tabeli z joina.

Chociaż zgodzę się z komentarzem Adriana, że to wygląda jak robione trochę na siłę. Różne dziwne rzeczy się już jednak widziało, więc jeśli faktycznie taka jest potrzeba, to w ten sposób bym próbował.

Comments

Wielkie dzięki za podpowiedź. Dopiero teraz wieczorem mogłem zrobić eksperyment i jak się okazało ostateczne zapytanie SQL wcale nie wygląda skomplikowanie . Prawidłowa konstrukcja przedstawia się następująco:

WITH RECURSIVE cte AS (
  SELECT 1 AS number
  UNION ALL
  SELECT number + 1
  FROM cte
  WHERE number < 10
) SELECT number, name FROM cte
LEFT JOIN services z
ON cte.number = z.number_order

Kto chce, może się przekonać na własne oczy: SQLFiddle. Pozdrawiam.