我有基本描述公交路线的表格。该表包括特定路线的所有站点。
此SQLFiddle显示了一个示例。数据显示了一个方向上的流动,但一条路线也可以是另一个方向上的流动。
我需要获取此路线(或全部路线)的子集,具体取决于起点和终点站。因此,例如,我可能有以下这些:
A -> M (subset would be B -> L)
A -> K (subset would be B -> J)
C -> H (subset would be D -> G)
I -> M (subset would be J -> L)
但是,如果方向来自例如H-> B,则子集将需要为G->C。
序列列用于此目的。
是否有使用SQL的简单易行的方法?
假设我理解您的问题,这似乎很简单。
要获取子集,只需between...and
在该sequence
列上使用运算符:
SELECT stop_from, stop_to
FROM routes
WHERE sequence BETWEEN 2 AND 11
ORDER BY sequence
要获得相反方向的子集,只需按desc排序,然后选择stop_to
as stop_from
,stop_from
as stop_to
:
SELECT stop_to As stop_from, stop_from As stop_to
FROM routes
WHERE sequence BETWEEN 3 AND 7
ORDER BY sequence DESC
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句