j******g
发帖数: 63 | 1 结果跟标准答案不一样。。。
http://sqlzoo.net/wiki/Self_join
表格基本信息：
stops(id, name)
route(num,company,pos, stop)
1) 大体上company+num合在一起算是一条线路的unique key。
2) route.stop跟stop.id相合
题目：
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.
给的提示：
Self-join twice to find buses that visit Craiglockhart and Sighthill, then
join those on matching stops.
我的sql... 阅读全帖 |
|
j******g
发帖数: 63 | 2 【 以下文字转载自 JobHunting 讨论区 】
发信人: jameslhg (James), 信区: JobHunting
标 题: 求助一道sql问题,谢谢
发信站: BBS 未名空间站 (Tue Apr 25 20:02:05 2017, 美东)
结果跟标准答案不一样。。。
http://sqlzoo.net/wiki/Self_join
表格基本信息：
stops(id, name)
route(num,company,pos, stop)
1) 大体上company+num合在一起算是一条线路的unique key。
2) route.stop跟stop.id相合
题目：
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 th... 阅读全帖 |
|
B*****g
发帖数: 34098 | 3 答案不对吧，下面这个答案就没有
45 LRT from Craiglockhart(53 pos at 7) To Brunstane(33 pos at 1)
then 32 LRT from Brunstane(33 pos at 2) to Sighthill(213 pos at 14)
num company pos stop
45 LRT 1 33 (Brunstane)
45 LRT 2 71
45 LRT 3 177
45 LRT 4 149
45 LRT 5 112
45 LRT 6 230
45 LRT 7 53 (Craiglockhart)
45 LRT 8 46
45 LRT 9 60
45 LRT 10 201
num company pos stop
32 LRT 1 173
32 LRT 2 33 (Bru... 阅读全帖 |
|
a*******y
发帖数: 105 | 4 我也不明白为啥不对, 难道还要排序？
select p1.num, p1.company, p1.name, p2.num, p2.company from
(SELECT distinct stopb.name, a.company, a.num
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart') as p1
join
(SELECT distinct stopa2.name, a2.company, a2.num
FROM route a2 JOIN route b2 ON
(a2.company=b2.company AND a2.num=b2.num)
JOIN stops stopa2 ON (a2.stop=stopa2.id)
JOIN stops... 阅读全帖 |
|
发帖数: 1 | 5 SELECT bus.n1, bus.c1, stops.name, bus.n2, bus.c2 FROM
(
SELECT b1.num n1, b1.company c1, b1.stop stop, b2.num n2, b2.company c2
FROM
(
SELECT route.num num, route.company company, route.stop stop FROM
(SELECT num, company, MIN(pos) pos, stop FROM (SELECT id, name FROM
stops WHERE name = 'Craiglockhart') s1 INNER JOIN route ON s1.id = route.
stop GROUP BY num, company, stop) r1
INNER JOIN route
ON r1.num = route.num AND r1.company = route.company
WHERE route.stop <> r1.... 阅读全帖 |
|
s********w
发帖数: 487 | 6 我的SQL
select tmp1.num,tmp1.company,stops.name,tmp2.num,tmp2.company from stops
right join
(
(select distinct R1.num, R1.company, R2.stop from route R1, route R2
where R1.num=R2.num and R1.company=R2.company and
R1.stop in (select id from stops where name='Craiglockhart')) tmp1 join
(select distinct R3.num, R3.company, R4.stop as stop1 from route R3, route
R4
where R3.num=R4.num and R3.company=R4.company and
R3.stop in (select id from stops where name = 'Sighthill')) tmp2
on tmp1.stop=tmp2.stop1)... 阅读全帖 |
|
