Mysql - FAQ

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ą

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
,gdzie:
  • 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)
, gdzie:
  • 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).

Komentarze

 

2010-08-29 09:13 gość_Kevin

Świetny artykuł, ciekawe prace można tutaj znaleźć

2011-04-17 02:28 gość_Krystian

Fajny post, alle mam jedno pytanie co do punktu numer 7.

Odpalając skrypt na wampie, za pierwszym razem działa OK, natomiast przy kolejnych próbach już nie. Czym to możę być spowodowane.

2011-04-17 14:05 nospor

Na wampie czyli na czym? Bo wamp składa się z kilku rzeczy.
I pokaż jak odpalasz.

2011-08-04 10:25 gość_Slawek

Widze, ze wpis juz ma swoje lata, pozwole sobie jednak skomentowac

Odnosnie punktu 5, w niektorych przypadkach moze sie przydac funkcja FIELD, ktora zwraca polozenie wartosci na liscie

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

SELECT id, name, color FROM tableName ORDER BY FIELD(color, 'green', 'yellow', 'blue')

2011-08-04 11:08 nospor

Mądry komentarz zawsze mile widziany, nie ważne po ilu latach

Dzięki za sugestię

2011-12-05 13:48 gość_Robert

8) numer danego wiersza
przy ilości tabel >1 zapytanie nie działa dla klauzuli
order by jakiespole jakiessortoanieASClubDESC

2011-12-05 13:58 nospor

Widocznie coś źle napisałeś. Zapytanie to zapytanie, nie ma znaczenia ile w nim jest tabel.

2011-12-05 17:18 gość_Robert

też mi się tak wydawało. Jednak po dodaniu klauzuli order by, zwracany jest zawsze nr=0. Bez order by, zwracany jest prawidłowy nr.

Z innej beczki, jak to wywołać z poziomu php? Po zapodaniu do mysql_query coś nie śmiga

2011-12-05 19:47 gość_Robert

wywołanie z PHP poszło. Załadowałem najpierw deklaracje zmiennych
set @id=5,@nr = 0,  @idnr = 0;
potem w odrębnym wywołaniu mysql_query, samo zapytanie. Jednak potwierdzam, że dla ustawionego "order by" zwracana jest zawsze wartość nr=0

2011-12-05 20:19 nospor

Ja to testowałem z order by i działało. Trudno mi się wypowiedzieć na temat twojego przykładu, skoro nie wiem jak wygląda, jak wygląda struktura tabel itp.

2018-06-25 20:08 gość_Piotr

Może się komuś przyda. Wyświetla w jednym wierszu dwie wartości. Pierwsza kolumna to wartość bieżąca rekordu, dla którego jest zdefioniowany warunek, druga kolumna to wartość poprzedniego rekordu.

select x.id biezacy ,( SELECT y.id from okres y WHERE y.okresp<x.okresp AND y.okresk<x.okresk ORDER BY y.okresp DESC ,y.okresk DESC LIMIT 1) prev from okres x WHERE id=39 ORDER BY okresp ASC ,okresk ASC
Tablica:
id - nie są wartościami po kolei
okresp - data początkowa okresu
okresk - data końca okresu

Dodaj komentarz

 

Dostępne bbcode: b, u, i, url, code, php, css, html, sql, js

Ostatnio komentowane

  1. Pager 2.5.1 oraz EPa... Na szybko2
  2. Pager 2.5.1 oraz EPa... Sławek
  3. Mysql - FAQ Piotr
  4. Liczba dni roboczych Na szybko2
  5. Liczba dni roboczych Naszybko
  6. Klasa widoku nospor
  7. Klasa widoku freebox

Ostatnio na forum

  1. programista php-webm... pracamatysart
  2. Programista PHP/ Mag... Create Magento 2 Marketplace
  3. Baza Danych gosc
  4. Baza Danych YankeS
  5. Baza Danych gosc
  6. Baza Danych YankeS
  7. Problem z bazą danyc... Baza Danych

Skrypty użytkowników

  1. Klasa obsługi szablo... Lirdoner
  2. Sekcje user76
  3. Klasa walidująca for... user76
  4. Licznik Gości online korey
  5. Form Builder Comandeer
  6. Dynamiczny licznik z... korey
  7. Captcha Comandeer