求助复杂的SQL语句,有点难度哦!!!

Question

比如
stops表有以下记录
id     stop_name
1      a
2      b
3      c
4      d
5      e
6      f
7      g
8      h
9      i
10     j
11     k
12     l

Answer 1

线路表(lines
id      line_name
1       1路
2       2路
3       3路

Answer 2

站点－路线表(line_stop),只写出一条路线的记录，就是类似这样子，我不举那么多的例子了，
ID  line_id  stop_id  upordown  serial
1    1         1        up        1
2    1         2        up        2
3    1         3        up        3
4    1         4        up        4
5    1         5        up        5
6    1         6        up        6
7    1         7        up        7
8    1         7       down       1
9    1         6       down       2
10    1         5       down       3
11    1         4       down       4
12    1         3       down       5
13    1         1       down       6

Answer 3

some ideas from xpert:I don't like your third table, it will only cause pain in the long run.
Have a look at what happens if you introduce instead an entity called "line segment" which defines the piece of the line between two consecutive stops. (This, I think, breaks up a M-N into a 1-M and a 2-M). Have a look at "connect by prior" to walk along the line, segment by segment.

Answer 4

生成的查询表的记录应该为
start_id  end_id  changenum  change_1  change_2  line_id
1         2       0                              1
1         3       0                              1
1         4       0                              1
1         5       0                              1
1         6       0                              1
1         7       0                              1
2         3       0                              1
2         4       0                              1
2         5       0                              1
2         6       0                              1
2         7       0                              1
3         1       0                              1
3         4       0                              1
3         5       0                              1
3         6       0                              1
3         7       0                              1
4         1       0                              1
4         3       0                              1
4         5       0                              1
4         6       0                              1
4         7       0                              1
5         1       0                              1
5         3       0                              1
5         4       0                              1
5         6       0                              1
5         7       0                              1
6         1       0                              1
6         3       0                              1
6         4       0                              1
6         5       0                              1
6         7       0                              1
7         1       0                              1
7         3       0                              1
7         4       0                              1
7         5       0                              1
7         6       0                              1

Answer 5

another suggestion to you , if you could change your db design:-)create table line(
lineKey   integer,
lineName  varchar2(20)
);create table line_seg(
lineKey  integer,
fromStop varchar2(1),
toStop   varchar2(1)
);insert into line values(1,'First');
insert into line values(2,'Second');
insert into line values(3,'Circle');insert into line_seg values(1,'A','B');
insert into line_seg values(1,'B','C');
insert into line_seg values(1,'C','D');
insert into line_seg values(1,'D','E');
insert into line_seg values(1,'E','F');insert into line_seg values(2,'B','C');
insert into line_seg values(2,'C','E');insert into line_seg values(3,'A','B');
insert into line_seg values(3,'B','C');
insert into line_seg values(3,'C','D');
insert into line_seg values(3,'D','A');
select lineName from line l
where 'D' IN
(select s.toStop from line_seg s
 connect by prior s.toStop = s.fromStop
 and              s.lineKey = l.linekey
 and              s.toStop <> 'B'
 start with s.fromStop = 'B'
 and        s.lineKey = l.lineKey);