j******g
发帖数: 63 | 1 【 以下文字转载自 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 the second bus.
给的提示：
Self-join twice to find buses that visit Craiglockhart and Sighthill, then
join those on matching stops.
我的sql code
SELECT a.num, a.company, stopb.name, d.num, d.company
FROM
route a
JOIN route b ON
(a.company=b.company AND a.num=b.num and a.stop<>b.stop)
JOIN route c ON
(b.stop=c.stop)
JOIN route d on
(c.company=d.company AND c.num=d.num and c.stop<>d.stop)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
JOIN stops stopc ON (c.stop=stopc.id)
JOIN stops stopd ON (d.stop=stopd.id)
WHERE stopa.name='Craiglockhart' and stopd.name='Sighthill' and (a.num<>d.
num or a.company<>d.company); | g****u
发帖数: 25 | 2 这道题我今天刚好也做到，弄了好久也是死活弄不出来，看你的sql也觉得很对啊，你
后来知道怎么做了吗？谢谢～
【在 j******g 的大作中提到】
: 【 以下文字转载自 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。
| j******g
发帖数: 63 | 3
系统就是死活不认。
不管了，咱们就算互相认证了。
【在 g****u 的大作中提到】
: 这道题我今天刚好也做到，弄了好久也是死活弄不出来，看你的sql也觉得很对啊，你
: 后来知道怎么做了吗？谢谢～
| t*o
发帖数: 2 | 4 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.stop
) b1
INNER JOIN
(
SELECT route.num num, route.company company, route.stop stop FROM
(SELECT num, company, MAX(pos) pos, stop FROM (SELECT id, name FROM
stops WHERE name = 'Sighthill') s2 INNER JOIN route ON s2.id = route.stop
GROUP BY num, company, stop) r2
INNER JOIN route
ON r2.num = route.num AND r2.company = route.company
WHERE route.stop <> r2.stop
) b2
ON b1.stop = b2.stop
WHERE b1.num <> b2.num OR b1.company <> b2.company
) bus
INNER JOIN stops
ON bus.stop = stops.id
ORDER BY bus.n1 + 0, bus.c1, stops.name, bus.n2 + 0, bus.c2
【在 j******g 的大作中提到】
:
: 系统就是死活不认。
: 不管了，咱们就算互相认证了。
| t*o
发帖数: 2 | 5 1. 结果有重复。
2. 没排序...
【在 j******g 的大作中提到】
:
: 系统就是死活不认。
: 不管了，咱们就算互相认证了。
| s********w
发帖数: 487 | 6 谢谢你的排序，我觉得我的sql query结果是对的，就是排序不对
一用你的排序，全都搞定了
【在 t*o 的大作中提到】
: 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
| s********w
发帖数: 487 | 7 我的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)
on tmp1.stop=stops.id
ORDER BY tmp1.num+0,tmp1.company,stops.name,tmp2.num+0,tmp2.company
【在 j******g 的大作中提到】
:
: 系统就是死活不认。
: 不管了，咱们就算互相认证了。
| a*****s
发帖数: 838 | 8 借楼问一下，你们sql用什么软件。我本来想上edx的微软的一个sql课程的，他们要求
使用他们自己的软件，我就没有上。有什么sql课程和软件推荐一下吗？
谢谢 |
|
