在sqlzoo上自学加入＃10教程

I have tried http://sqlzoo.net/wiki/Self_join

我试过http://sqlzoo.net/wiki/Self_join

Self Join Session for the #10

#10的自我加入会议

# 10 : Find the routes involving two buses that can go from Craiglockhart to Sighthill.Show the bus no. and company for the first bus, the name of the stop for the transfer,and the bus no. and company for the second bus.

#10:找到可以从Craiglockhart到Sighthill的两条公共汽车的路线。显示公交车号码。和第一辆公共汽车的公司,转移的停止的名称,和公共汽车号码。和公司的第二辆公共汽车。

Here is my code:

这是我的代码:

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

I know the output would give you multiple rows like:

我知道输出会给你多行,如:

    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

Where you want:

你想在哪里:

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

There is also a bug that the order of a.num when I try ASC doesn't work. Also the when I put DISTINCT before c.num it shows error. can't use group by since it gives you too few rows.

还有一个错误,当我尝试ASC时,a.num的顺序不起作用。此外,当我在c.num之前放置DISTINCT时,它显示错误。不能使用group by,因为它给你的行太少了。

Can anyone experts help?

任何人都可以帮忙吗?

7 个解决方案

#1

RE: the sorting 'bug', this is due to the way the application sorts. It sorts alphabetically; so 10 comes before 2, etc. This article shows a way to do "natural sorting" using LENGTH().

RE:排序'bug',这是由于应用程序的排序方式。它按字母顺序排序;所以10来自2之前等。本文展示了一种使用LENGTH()进行“自然排序”的方法。

For this particular problem, I was able to get the correct answer using:

对于这个特殊问题,我能够使用以下方法获得正确的答案:

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

#2

My solution to this problem: I divided the problem into two.

我对这个问题的解决方案:我把问题分成了两个。

First subquery will be the table S(Start), which will get all the routes that start from 'Craiglockhart' Second subquery will be the table E(End), which will get all the routes that start from 'Sighthill'

第一个子查询将是表S(开始),它将获得从'Craiglockhart'开始的所有路径。第二个子查询将是表E(结束),它将获得从'Sighthill'开始的所有路线

Now both table S and E will have common routes, and i get all this common routes by joining the subqueries, using the ids of each table. As there are duplicates routes(same: S.num, S.company, stops.name, E.num, E.company) i used DISTINCT.

现在表S和E都有共同的路由,我通过使用每个表的id加入子查询来获得所有这些常见路由。因为有重复的路线(相同:S.num,S.company,stops.name,E.num,E.company)我使用了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)

#3

If you only want distinct rows, use the keyword DISTINCT:

如果您只想要不同的行,请使用关键字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

The manual states:

手册说明:

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.

ALL和DISTINCT选项指定是否应返回重复的行。 ALL(默认值)指定应返回所有匹配的行,包括重复行。 DISTINCT指定从结果集中删除重复的行。指定两个选项都是错误的。 DISTINCTROW是DISTINCT的同义词。

#4

Try this out, it works!

试试这个,它有效!

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

#5

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 

#6

SELECT DISTINCT x.num,x.company,x.name,y.num,y.company 
FROM 
(
SELECT a.num as num,a.company as company,sb.name as name
FROM route a
JOIN route b
    ON a.company = b.company AND a.num = b.num
JOIN stops sa
    ON sa.id = a.stop
JOIN stops sb
    ON sb.id = b.stop
WHERE 
    sa.name = 'Craiglockhart'
) x

JOIN 

(
SELECT a.num as num, a.company as company,sb.name as name
FROM route a
JOIN route b
    ON a.company = b.company AND a.num = b.num
JOIN stops sa
    ON sa.id = a.stop
JOIN stops sb
    ON sb.id = b.stop
WHERE sa.name = 'Sighthill'
) y

ON x.name = y.name

#7

Please check a possible solution:

请检查可能的解决方案:

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;

Source.