Przeglądając różne fora widzę, iż użytkownicy często pytają o te same rzeczy. Podawać tu będę rozwiązania na większość z nich. Artykuł stopniowo będzie uzupełniany o kolejne rzeczy.

• 1) Jedna tabela wskazuje kilka razy na inną ale tą samą
• 2) Zliczanie wystąpień z tabeli powiązanej
• 3) Zliczanie warunkowych wystąpień z tabeli powiązanej
• 4) Sąsiedzi - wyznaczanie rekordu przed i po danym rekordzie
• 5) Własne sortowanie
• 6) Numerowanie wierszy
• 7) Pobieranie co n-tego wiersza
• 8) Numer danego wiersza

1) Jedna tabela wskazuje kilka razy na inną ale tą samą

Mamy dwie tabele. Pierwsza ma swoje pola plus dwa pola wskazujące na inną tą samą tabelę. Chcemy pobrać dane z drugiej tabeli dla odpowiadających id z pierwszej tabeli.
W tym celu należy zrobic dwa razy left join oraz użyć aliasów dla joinowanych tabel, gdyż obie będą miały tę samą nazwę

select tabela1.pole1, tabela1.id1, t21.name, t22.name from tabela1
left join tabela2 t21 on t21.id=tabela1.id1
left join tabela2 t22 on t22.id=tabela1.id2

• tabela1.id1 wskazuje na tabela2
• tabela1.id2 wskazuje na tabela2
• tabela2.name jakieś pole z tabeli 2

2) Zliczanie wystąpień z tabeli powiązanej

Mamy dwie tabele. Jedną powiązana z drugą (1-N). Chcemy policzyć ile jest powiązań rekordów z drugiej tabeli z danym rekordem z pierwszej tabeli.

select tabela1.pole1, count(tabela2.id) ilosc from tabela1 
left join tabela2 on tabela2.fk_tabela1=tabela1.id
group by tabela1.id

Pod ilosc znajdować się będzie szukana liczba dla danego rekordu z pierwszej tabeli.
Gdybyśmy chcieli, zamiast zliczania rekordów, zsumować dane pole, wystarczy użyć sum na danym polu

select tabela1.pole1, sum(tabela2.pole) suma from tabela1 
left join tabela2 on tabela2.fk_tabela1=tabela1.id
group by tabela1.id

3) Zliczanie warunkowych wystąpień z tabeli powiązanej

Sytuacja podobna jak wyżej, z tym że chcemy policzyć dodatkowo rekordy z zadaną wartością

select tabela1.pole1, count(tabela2.id) ilosc,
count(if(tabela2.pole1=0,1,null)) ilosc0,count(if(tabela2.pole1=1,1,null)) ilosc1 
from tabela1 
left join tabela2 on tabela2.fk_tabela1=tabela1.id
group by tabela1.id

Pod ilosc znajdować się będzie liczba wszystkich rekordów z tabeli2 powiązanych z danym rekordem z tabeli1. Pod ilosc0 będą rekordy z tabeli2, których pole pole1 ma wartość 0. Pod ilosc1 będą rekordy z tabeli2, których pole pole1 ma wartość 1.

4) Sąsiedzi - wyznaczanie rekordu przed i po danym rekordzie

Chcemy znaleźć sąsiedztwo danego rekordu. Przydatne może to być gdy chcemy robić przyciski poprzedni następny.

(SELECT id, 'prev' kol FROM tabela1 WHERE id < 5 order by id desc LIMIT 0, 1)
UNION
(SELECT id, 'next' kol FROM tabela1 WHERE id > 5 order by id asc LIMIT 0, 1)

• 5 - id rekordu, dla którego szukamy sąsiedztwa
• kol - zawierać będzie tekst prev lub next w zależności od tego czy to będzie rekord poprzedni czy następny. Dodałem to na wypadek sytuacji, gdy będziemy szukać sąsiedztwa np. pierwszego rekordu. Wówczas rekordu poprzedniego nie będzie i zostanie nam zwrócony tylko następny. Dzięki kol łatwo się zorientujemy, który rekord nam zwrócono.

Sql ten zakłada, że kolejne rekordy mają id numerowane rosnąco i sortowanie odbywa się właśnie po tym id. Gdybyśmy jednak mieli jakieś swoje sortowanie, nie koniecznie po id ale po innym polu, sprawa się nieco komplikuje

set @nr = 0, @id=5, @idprev = 0,@idprevfound=0, @idnext=0,@idnextfound=0;
SELECT @nr:=@nr+1,
if(id<>@id and not @idprevfound, @idprev := id,if(@idprevfound,null,@idprevfound:=1)),
if(id=@id, @idnextfound := 1,if(@idnextfound and not @idnext,@idnext := id, null)) FROM tabela1 order by pole;
SELECT @idprev prev, @idnext next;

Ostanie zapytanie zwróci nam pod kolumną prev id poprzedniego rekordu, a pod kolumną next id następnego rekordu.
Zmienna @id trzyma id rekordu, którego sąsiadów szukamy. Jeśli dany sąsiad nie zostanie znaleziony, zostanie zwrócone 0.

5) Własne sortowanie

Sortowanie prosta rzecz:

select * from tabela order by pole

Sprawa komplikuje się, gdy próbujemy posortować wg. naszego klucza, np. chcemy posortować rekordy tak, by pojawiały się tak dla zadanego pola: 2, 4, 3, 1.
Można to zrobić na dwa sposoby

SELECT *, CASE pole 
WHEN 2 THEN 1
WHEN 4 THEN 2
WHEN 3 THEN 3
WHEN 1 THEN 4
ELSE NULL
END as sortowanie
FROM tabela
ORDER BY sortowanie

lub

SELECT * FROM tabela
ORDER BY pole=2 desc, pole=4 desc, pole=3 desc,pole=1 desc

Kolejny przykład: chcemy posortować pola tak, by najpierw pojawił się rekord o zadanej wartości pola (np. 1), a potem rekordy posortowane rosnąco według innego pola

select * from tabela order by pole=1 desc, pole2 asc

6) Numerowanie wierszy

Wyciągamy rekordy z tabeli w jakiejś tam kolejności, z jakimiś tam warunkami. Chcemy dodatkowo dla każdego rekordu przypisać numer porządkowy dla tego "wyciągania".

set @i=0;
select @i:=@i+1 nr, jakiesinnepola from tabela;

Kolumna nr zawierać będzie numer porządkowy danego wiersza.

7) Pobieranie co n-tego wiersza

Wyciągamy rekordy z tabeli w jakiejś tam kolejności, z jakimiś tam warunkami. Chcemy pobrać tylko co n-ty rekord z tego zbioru

SET @nr=-1, @coile=5;
select * from (
select @nr:=@nr+1 _nr,tabela.* from tabela
) jakisalias where _nr % @coile = 0;

8) Numer danego wiersza

Aby sprawdzić jaki dany wiersz (o np. danym id) ma numer w danym select z konkretnymi warunkami i z konkretnym sortowaniem należy wykonać następujące zapytania

set @id=5,@nr = 0,  @idnr = 0;
select @idnr as nr from 
(SELECT @nr:=@nr+1,if(ID=@id, @idnr := @nr,@idnr=@idnr),ID FROM tabela where jakiespole ='jakiswarunek' order by jakiespole jakiessortoanieASClubDESC) podsel where podsel.ID = @id;

gdzie @id to id rekordu, którego numeru szukamy. Ostatnie zapytanie zwróci nam pod indexem nr numer naszego rekordu (numerowane od 1).