Autoinscritura tutorial n. ° 10 en sqlzoo

He intentado http://sqlzoo.net/wiki/Self_join

Sesión uno mismo para el # 10

# 10: Encuentra las routes que involucran dos autobuses que pueden ir desde Craiglockhart a Sighthill. Muestra el número de autobús. y compañía para el primer autobús, el nombre de la parada para la transferencia y el autobús no. y compañía para el segundo autobús.

Aquí está mi código:

SELECT a.num, a.company, trans1.name , c.num, c.company FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num) JOIN ( route c JOIN route d ON (c.company = d.company AND c.num= d.num)) JOIN stops start ON (a.stop = start.id) JOIN stops trans1 ON (b.stop = trans1.id) JOIN stops trans2 ON (c.stop = trans2.id) JOIN stops end ON (d.stop = end.id) WHERE start.name = 'Craiglockhart' AND end.name = 'Sighthill' AND trans1.name = trans2.name ORDER BY a.num ASC , trans1.name 

Sé que la salida le daría varias filas como:

  4 LRT London Road 35 LRT 4 LRT London Road 34 LRT 4 LRT London Road 35 LRT 4 LRT London Road 34 LRT 4 LRT London Road C5 SMT 

Donde quieras:

  4 LRT London Road 34 LRT 4 LRT London Road 35 LRT 4 LRT London Road 65 LRT 4 LRT London Road C5 SMT 

También hay un error que el order de a.num cuando bash ASC no funciona. También cuando pongo DISTINCT antes de c.num muestra error. no se puede usar agrupar ya que le da muy pocas filas.

¿Alguien puede ayudarme?

RE: el 'error' de sorting, esto se debe a la forma en que la aplicación ordera. Ordena alfabéticamente; entonces 10 viene antes que 2, etc. Este artículo muestra una manera de hacer "sorting natural" usando LONGITUD ().

Para este problema en particular, pude get la respuesta correcta usando:

 ORDER BY LENGTH(a.num), b.num, trans1.id, LENGTH(c.num), d.num; 

Si solo desea filas distintas, use la palabra key DISTINCT :

 SELECT DISTINCT a.num, a.company, trans1.name , c.num, c.company FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num) JOIN ( route c JOIN route d ON (c.company = d.company AND c.num= d.num)) JOIN stops start ON (a.stop = start.id) JOIN stops trans1 ON (b.stop = trans1.id) JOIN stops trans2 ON (c.stop = trans2.id) JOIN stops end ON (d.stop = end.id) WHERE start.name = 'Craiglockhart' AND end.name = 'Sighthill' AND trans1.name = trans2.name ORDER BY a.num ASC , trans1.name 

El manual dice :

Las opciones ALL y DISTINCT especifican si se deben devolver las filas duplicadas. ALL (valor pnetworkingeterminado) especifica que se deben devolver todas las filas coincidentes, incluidos los duplicates. DISTINCT especifica la eliminación de filas duplicadas del set de resultados. Es un error especificar ambas opciones. DISTINCTROW es un sinónimo de DISTINCT.

¡Prueba esto, funciona!

 SELECT DISTINCT a.num, a.company, trans1.name, d.num, d.company FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num) JOIN route c ON (b.stop=c.stop AND b.num!=c.num) JOIN route d on (c.company = d.company AND c.num = d.num) JOIN stops start ON (a.stop=start.id) JOIN stops trans1 ON (b.stop = trans1.id) JOIN stops trans2 ON (c.stop = trans2.id) JOIN stops end ON (d.stop = end.id) WHERE start.name = 'Craiglockhart' AND end.name = 'Sighthill' AND trans1.name = trans2.name order by length(a.num), a.num 

Mi solución a este problema: dividí el problema en dos.

La primera subconsulta será la tabla S (Inicio), que obtendrá todas las routes que comienzan desde 'Craiglockhart'. La segunda subconsulta será la tabla E (Fin), que obtendrá todas las routes que comienzan desde 'Sighthill'.

Ahora tanto la tabla S como la E tendrán routes comunes, y obtengo todas estas routes comunes al unirme a las subconsultas, usando los identificadores de cada tabla. Como hay routes duplicadas (lo mismo: S.num, S.company, stops.name, E.num, E.company) utilicé DISTINCT.

 SELECT DISTINCT S.num, S.company, stops.name, E.num, E.company FROM (SELECT a.company, a.num, b.stop FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num) WHERE a.stop=(SELECT id FROM stops WHERE name= 'Craiglockhart') )S JOIN (SELECT a.company, a.num, b.stop FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num) WHERE a.stop=(SELECT id FROM stops WHERE name= 'Sighthill') )E ON (S.stop = E.stop) JOIN stops ON(stops.id = S.stop) 
 SELECT DISTINCT sub1.num, sub1.company, name, sub2.num, sub2.company FROM (SELECT r1.num, r1.company, r1.stop AS first, r2.stop AS mid FROM route r1 JOIN route r2 ON r1.num = r2.num AND r1.company = r2.company WHERE r1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart'))sub1 JOIN (SELECT r3.num, r3.company, r3.stop AS mid2, r4.stop AS dest FROM route r3 JOIN route r4 ON r3.num = r4.num AND r3.company = r4.company WHERE r4.stop = (SELECT id FROM stops WHERE name = 'Sighthill'))sub2 ON sub1.mid = sub2.mid2 JOIN stops ON id = sub1.mid 

Por favor verifique una posible solución:

 SELECT distinct StartOfR1.num, StartOfR1.company, Xfer.name xfer_name, EndOfR2.num, EndOfR2.company FROM stops Start, stops Xfer, stops Finish, route StartOfR1, route EndOfR1, route StartOfR2, route EndOfR2 WHERE Start.name='Craiglockhart' AND Finish.name='Sighthill' AND StartOfR1.stop= Start.id -- R1 actually visits Start AND EndOfR1.num = StartOfR1.num -- no transfer on the same route AND EndOfR1.stop= StartOfR2.stop -- R2 starts where R1 ends AND EndOfR1.num != StartOfR2.num -- R1 and R2 are not the same route AND EndOfR1.stop = Xfer.id-- R1 changes to R2 AND EndOfR2.company = StartOfR2.company -- R1 changes bus to R2 AND EndOfR2.num = StartOfR2.num -- two stops on the same route AND EndOfR2.stop = Finish.id -- R2 actually visits Finish; 

Fuente