Consultes recursives: bikeshop
La revisió el 08:12, 7 feb 2022 per Joan (discussió | contribucions)
Introducció
(TBD)
Base de dades bikeshop
Mirem la tala CATEGORIA:
select id_cat, categoria, id_parent from CATEGORIA +--------+-----------------------------------------------+-----------+ | id_cat | categoria | id_parent | +--------+-----------------------------------------------+-----------+ | 1 | Carretera | NULL | | 2 | MTB | NULL | | 3 | E-BIKE | NULL | | 4 | BMX | NULL | | 5 | CIUDAD | NULL | | 6 | Bicicletas | 1 | | 7 | Bicicletas de Carrera | 6 | | 8 | Bicicletas de Gravel | 6 | | 9 | Bicicletas de Ciclocross | 6 | | 10 | Bicicletas Contrarreloj | 6 | | 11 | Bicicletas de pista | 6 | ... 173 files
Volem visualitzar 2 nivells:
select C1.id_cat, C1.categoria, C1.id_parent, C2.id_cat, C2.categoria from CATEGORIA C1 INNER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat; +--------+-----------------------------------------------+-----------+--------+-----------------------------------+ | id_cat | categoria | id_parent | id_cat | categoria | +--------+-----------------------------------------------+-----------+--------+-----------------------------------+ | 6 | Bicicletas | 1 | 1 | Carretera | | 7 | Bicicletas de Carrera | 6 | 6 | Bicicletas | | 8 | Bicicletas de Gravel | 6 | 6 | Bicicletas | | 9 | Bicicletas de Ciclocross | 6 | 6 | Bicicletas | | 10 | Bicicletas Contrarreloj | 6 | 6 | Bicicletas | | 11 | Bicicletas de pista | 6 | 6 | Bicicletas | | 12 | Bicicletas para mujeres | 6 | 6 | Bicicletas | | 13 | Bicicletas Niños | 6 | 6 | Bicicletas | | 14 | Bicicletas eléctricas | 6 | 6 | Bicicletas | | 15 | Cuadros | 1 | 1 | Carretera | | 16 | Cuadros de carretera | 15 | 15 | Cuadros | | 17 | Cuadros de Gravel | 15 | 15 | Cuadros | | 18 | Horquillas | 15 | 15 | Cuadros | ... 168 files
Hem perdut 5 categories. Amb un LEFT OUTER JOIN tornem a recuperar els 173 valors (les categories que no tenen pare)
select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat; +-----------------------------------+-----------------------------------------------+ | cat_pare | cat_fill | +-----------------------------------+-----------------------------------------------+ | NULL | Carretera | | NULL | MTB | | NULL | E-BIKE | | NULL | BMX | | NULL | CIUDAD | | Carretera | Bicicletas | | Bicicletas | Bicicletas de Carrera | | Bicicletas | Bicicletas de Gravel | | Bicicletas | Bicicletas de Ciclocross | | Bicicletas | Bicicletas Contrarreloj ... 173 files
Volem filtrar el que penja de Bicicletas > CIUDAD > transmisión filtrem:
select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat WHERE C2.categoria='Transmisión'; +--------------+---------------------------------------+ | cat_pare | cat_fill | +--------------+---------------------------------------+ | Transmisión | Cables y fundas | | Transmisión | Cassettes y cadenas | | Transmisión | Cambios y desviadores | | Transmisión | Manetas | | Transmisión | Pedaliers | | Transmisión | Pedales | | Transmisión | Transmisión eléctrica | ... 33 files
Ho fem malament perquè la categoria Transmisión no és única.
33 files perquè hem trobat:
- Bicicletas > carretera > Transmisión
- Bicicletas > MTB > Transmisión
- Bicicletas > BMX > BMX FreeStyle > Transmisión
- Bicicletas > CIUDAD > Transmisión
Volem doncs ara visualitzar els 3 nivells
select C1.id_cat, C1.categoria, C1.id_parent, C2.id_cat, C2.categoria, C3.id_cat, C3.categoria from ((CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat) LEFT OUTER JOIN CATEGORIA C3 ON C2.id_parent=C3.id_cat); +--------+-----------------------------------------------+-----------+--------+-----------------------------------+--------+---------------+ | id_cat | categoria | id_parent | id_cat | categoria | id_cat | categoria | +--------+-----------------------------------------------+-----------+--------+-----------------------------------+--------+---------------+ | 1 | Carretera | NULL | NULL | NULL | NULL | NULL | | 2 | MTB | NULL | NULL | NULL | NULL | NULL | | 3 | E-BIKE | NULL | NULL | NULL | NULL | NULL | | 4 | BMX | NULL | NULL | NULL | NULL | NULL | | 5 | CIUDAD | NULL | NULL | NULL | NULL | NULL | | 6 | Bicicletas | 1 | 1 | Carretera | NULL | NULL | | 7 | Bicicletas de Carrera | 6 | 6 | Bicicletas | 1 | Carretera | | 8 | Bicicletas de Gravel | 6 | 6 | Bicicletas | 1 | Carretera | | 9 | Bicicletas de Ciclocross | 6 | 6 | Bicicletas | 1 | Carretera | | 10 | Bicicletas Contrarreloj | 6 | 6 | Bicicletas | 1 | Carretera | | 11 | Bicicletas de pista | 6 | 6 | Bicicletas | 1 | Carretera | | 12 | Bicicletas para mujeres | 6 | 6 | Bicicletas | 1 | Carretera | | 13 | Bicicletas Niños | 6 | 6 | Bicicletas | 1 | Carretera | | 14 | Bicicletas eléctricas | 6 | 6 | Bicicletas | 1 | Carretera | ... 173 files
select C3.categoria cat_avi, C2.categoria cat_pare, C1.categoria cat_fill from ((CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat) LEFT OUTER JOIN CATEGORIA C3 ON C2.id_parent=C3.id_cat) WHERE C3.categoria='CIUDAD' AND C2.categoria='Transmisión'; +---------+--------------+---------------------------------------+ | cat_avi | cat_pare | cat_fill | +---------+--------------+---------------------------------------+ | CIUDAD | Transmisión | Cables y fundas | | CIUDAD | Transmisión | Cubrecadenas | | CIUDAD | Transmisión | Cassettes | | CIUDAD | Transmisión | Cadenas | | CIUDAD | Transmisión | Mandos de cambio | | CIUDAD | Transmisión | Cambios | | CIUDAD | Transmisión | Pedaliers, Cajas del pedalier, Platos | +---------+--------------+---------------------------------------+
Base de dades HR. Taula employees
En la base de dades HR' tenim la taula employees on també tenim una informació recursiva: d'un empleat sabem qui és el seu jefe, que a l'hora pot ser empleat d'un altre jefe.
mysql> DESC employees; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | employee_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(25) | NO | | NULL | | ... | manager_id | int | YES | MUL | NULL | | +---------------+--------------+------+-----+---------+---------------
select employee_id, CONCAT(first_name,' ',last_name), manager_id from employees; +-------------+----------------------------------+------------+ | employee_id | CONCAT(first_name,' ',last_name) | manager_id | +-------------+----------------------------------+------------+ | 100 | Steven King | NULL | | 101 | Neena Kochhar | 100 | | 102 | Lex De Haan | 100 | | 103 | Alexander Hunold | 102 | | 104 | Bruce Ernst | 103 | | 105 | David Austin | 103 | | 106 | Valli Pataballa | 103 | | 107 | Diana Lorentz | 103 | ... 40 files
Igual que abans, podem mirar la llista d'empleats i jefes:
select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat, e1.manager_id, e2.employee_id, CONCAT(e2.first_name,' ',e2.last_name) jefe from employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id; +-------------+-------------------+------------+-------------+-------------------+ | employee_id | empleat | manager_id | employee_id | jefe | +-------------+-------------------+------------+-------------+-------------------+ | 100 | Steven King | NULL | NULL | NULL | | 101 | Neena Kochhar | 100 | 100 | Steven King | | 102 | Lex De Haan | 100 | 100 | Steven King | | 103 | Alexander Hunold | 102 | 102 | Lex De Haan | | 104 | Bruce Ernst | 103 | 103 | Alexander Hunold | | 105 | David Austin | 103 | 103 | Alexander Hunold | | 106 | Valli Pataballa | 103 | 103 | Alexander Hunold | | 107 | Diana Lorentz | 103 | 103 | Alexander Hunold | | 108 | Nancy Greenberg | 101 | 101 | Neena Kochhar | ... 40 files
Quins són els subordinats del Alexander Hunold?
select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat from employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id WHERE CONCAT(e2.first_name,' ',e2.last_name)='Alexander Hunold'; +-------------+-----------------+ | employee_id | empleat | +-------------+-----------------+ | 104 | Bruce Ernst | | 105 | David Austin | | 106 | Valli Pataballa | | 107 | Diana Lorentz | +-------------+-----------------+ 4 rows in set (0,00 sec)
creat per Joan Quintana Compte, febrer 2022
