博客

记录一下钱升钱的面试题和第一小题答案

IMG20160626101617

1
2
3
4
5
6
7
CREATE TABLE `metro` (
  `line` INT(11) UNSIGNED NOT NULL,
  `stop` VARCHAR(100) DEFAULT NULL,
  `sequence` INT(11) UNSIGNED DEFAULT NULL,
  KEY `idx_line` (`line`),
  KEY `idx_station` (`stop`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
INSERT INTO metro(line,`stop`,sequence)
VALUES (2,'徐泾东',1)
, (2,'虹桥2号航站楼',2)
, (2,'淞虹路',3)
, (2,'北新泾',4)
, (2,'威宁路',5)
, (2,'娄山关路',6)
, (2,'中山公园',7)
, (2,'江苏路',8)
, (2,'静安寺',9)
, (2,'南京西路',10)
, (2,'人民广场',11)
, (2,'南京东路',12)
, (2,'陆家嘴',13)
, (2,'东昌路',14)
, (2,'世纪大道',15)
, (2,'上海科技馆',16)
, (2,'世纪公园',17)
, (2,'龙阳路',18)
, (2,'张江高科',19)
, (2,'金科路',20)
, (9,'杨高中路',1)
, (9,'世纪大道',2)
, (9,'商城路',3)
, (9,'小南门',4)
, (9,'陆家滨路',5)
, (9,'马当路',6)
, (9,'打浦路',7)
, (9,'嘉善路',8)
, (9,'肇嘉浜路',9)
, (9,'徐家汇',10)
, (9,'宜山路',11)
, (9,'桂林路',12)
, (9,'漕河泾开发区',13)
, (9,'合川路',14)
, (9,'星中路',16)
, (9,'七宝',17)
, (9,'中春路',18)
, (9,'九亭',19)
, (9,'泗泾',20)
, (9,'佘山',21)
, (9,'洞泾',22)
, (9,'松江大学城',23)
, (9,'松江新城',24)
, (9,'松江体育中心',25)
, (9,'醉白池',26)
, (9,'松江南站',27);
1
2
3
4
5
6
/* 是否存在一次换成的路线 */
SELECT count(1)
    FROM metro AS t,metro AS t1 
    WHERE t.`stop`=t1.`stop` /* 一次换乘 */
        AND t.line=(SELECT line FROM metro WHERE `stop`='淞虹路') /* 出发点 */
        AND t1.line=(SELECT line FROM metro WHERE `stop`='漕河泾开发区') /* 终点 */;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/* 明细换乘路线 -- 可能还需要改进 */
SELECT t2.* 
FROM metro AS t2, 
    (SELECT t.line,t.sequence
    FROM metro AS t,metro AS t1 
    WHERE t.`stop`=t1.`stop` 
        AND t.line=(SELECT line FROM metro WHERE `stop`='淞虹路')
        AND t1.line=(SELECT line FROM metro WHERE `stop`='漕河泾开发区')) AS tt
    WHERE t2.line=tt.line 
        AND t2.sequence<=tt.sequence 
        AND t2.sequence>=(SELECT sequence FROM metro WHERE `stop`='淞虹路')
UNION   
SELECT t2.* 
FROM metro AS t2, 
    (SELECT t1.line,t1.sequence
    FROM metro AS t,metro AS t1 
    WHERE t.`stop`=t1.`stop` 
        AND t.line=(SELECT line FROM metro WHERE `stop`='淞虹路')
        AND t1.line=(SELECT line FROM metro WHERE `stop`='漕河泾开发区')) AS tt
    WHERE t2.line=tt.line 
        AND t2.sequence>=tt.sequence
        AND t2.sequence<=(SELECT sequence FROM metro WHERE `stop`='漕河泾开发区');

另求各位大神看看后面的题目，谢谢
全部答案已放出 https://github.com/jshensh/phpcon2016-qsq

Web 开发