sqlzoo-day7

1. 复杂的计算：NSS Tutorial - SQLZOO

由于对nss数据没有详细说明，这部分题目的含义理解上经常出现问题，比如有的值本身就是百分比的形式。

T8: Show the institution, the total sample size and the number of computing students for institutions in Manchester for 'Q01'.

展示Q01且institution包含‘Manchester’的institution， 总的sample，以及subject=计算机的总人数；本来想使用子查询，奈何一直报错，看了书中可以在FROM中加入两个表，那么将另一个子查询作为表p2，两张表通过institution进行匹配，也可以做出来。【关注一下：GROUP BY ,子查询只能一列？，sql的运行顺序】，看答案有人用case语句。

-- case语句
SELECT institution,SUM(sample), 
SUM(
     CASE WHEN subject = '(8) Computer Science' THEN sample
          ELSE 0
     END) FROM nss
WHERE question='Q01'AND (institution LIKE '%Manchester%')
GROUP BY institution
-- 使用子查询
SELECT DISTINCT(p1.institution), SUM(p1.sample), p2.comp 
FROM nss AS p1,
       (SELECT institution,SUM(sample) AS comp FROM nss
               WHERE question='Q01'AND (institution LIKE '%Manchester%')
               AND subject = '(8) Computer Science'
               GROUP BY institution) AS p2
       WHERE p1.question='Q01'AND (p1.institution LIKE '%Manchester%')
       AND p1.institution = p2.institution
       GROUP BY p1.institution, p2.comp

2. 自连接

T5: 執行自我合拼來，留意b.stop代表由Craiglockhart出發不用轉車可前住的地方。 修改它來顯示由Craiglockhart(stop=53)到 London Road(stop=149)的服務資料。

条件语句要重复用到某些内容，而这两次内容的含义是不同的。

-- T5:用站台标号 
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
    (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop = 149
-- T6: 用站台名
SELECT a.company, a.num, stopa.name, stopb.name
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' AND stopb.name = 'London Road'

-- T7: 站台115-137之间的公司和路线
SELECT DISTINCT(a.company), a.num FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
WHERE (a.stop = 115 AND b.stop = 137)
OR (a.stop = 137 AND b.stop = 115)

-- T8:車站stops 'Craiglockhart' 到 'Tollcross' 的公司名和路線號碼。
SELECT DISTINCT(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' AND stopb.name =  'Tollcross' )
OR (stopa.name =  'Tollcross'  AND stopb.name = 'Craiglockhart')

T9: 不重覆列出可以由 'Craiglockhart' 乘一程車到達的站stops，包括'Craiglockhart'本身。 列出站名，公司名和路線號碼。

以为是写出坐一站站名和公司名，结果是找出不用转车的？

SELECT DISTINCT(stopb.name),b.company,b.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' 
-- AND b.pos IN(a.pos +1,a.pos, a.pos-1)

T10: 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.

这题的思路：

找到起点为Craiglockhart的bus和其可到达的站stop1——bus1

找到终点为Sighthill的bus和其来自的站start2——bus2

bus1和bus2作为两张表，找到bus1的终点stop1=bus2的起点start2的换乘站

刚好用上本文第一部分的T8的子查询的用法。(*^▽^*)

SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company FROM 

(SELECT r12.num, r12.company, r12.stop AS stop1 FROM route r11
JOIN route r12 ON (r11.company=r12.company AND r11.num=r12.num)
WHERE r11.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')) AS bus1

JOIN 

(SELECT r21.num, r21.company, r21.stop AS start2 FROM route r21
JOIN route r22 ON (r21.company=r22.company AND r21.num=r22.num)
WHERE r22.stop = (SELECT id FROM stops WHERE name = 'Sighthill')) AS bus2

ON (bus1.stop1 = bus2.start2)
JOIN stops ON (stops.id = bus1.stop1)