地铁采用一次换乘到达sql_地铁sql语句-优快云博客

本文链接：https://blog.youkuaiyun.com/y_h_t/article/details/6215464

本文介绍了一个用于查询地铁线路及站点信息的数据库设计，并提供了一次换乘查询的存储过程实现。通过两个表(subway_line 和 subway_station)来管理线路与站点数据，包括站点出口周边设施等详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实现效果如下：

--subway_line 表:

CREATE TABLE [dbo].[SUBWAY_LINE](
[SL_ID] [int] IDENTITY(1,1) NOT NULL,
[SL_LINE_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SL_IMG] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[SC_ID] [int] NOT NULL,
[SC_CITY_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SL_START_END] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SL_END_START] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SL_CREATE_TIME] [datetime] NULL
)

--subway_station表

CREATE TABLE [dbo].[SUBWAY_STATION](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SL_ID] [int] NOT NULL,
[SS_SEQUENCE] [int] NOT NULL,
[SS_STATION_NAME] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SS_UP_STARTTIME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_UP_ENDTIME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_DOWN_STARTTIME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_DOWN_ENDTIME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_IMG] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[SS_1_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_1_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_1_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_1_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_2_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_2_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_2_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_2_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_3_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_3_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_3_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_3_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_4_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_4_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_4_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_4_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_5_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_5_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_5_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_5_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_6_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_6_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_6_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_6_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_7_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_7_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_7_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_7_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_8_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_8_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_8_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_8_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_9_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_9_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_9_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_9_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_10_EXIT_NAME] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[SS_10_EXIT_BUS_STATION_NAME] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[SS_10_EXIT_BUS] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[SS_10_EXIT_BUILDING] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[SS_CREATE_TIME] [datetime] NULL
)

存储过程，实现地铁一次换乘查询：

alter proc proc_subone
(
@start varchar(50),
@end varchar(50)
)
as
select tb.ss_station_name,tb.sl_id as startid,td.sl_id as endid,tb.sl_line_name as startname,td.sl_line_name as endname from (
select ss_station_name,tb.sl_id,sl_line_name from(
select * from subway_station where sl_id in (select sl_id from (select * from subway_station where ss_station_name=@start) as ta))as tb
inner join subway_line as tc on tb.sl_id = tc.sl_id
) as tb
inner join
(
select ss_station_name,te.sl_id,sl_line_name from(
select * from subway_station where sl_id in (select sl_id from (select * from subway_station where ss_station_name=@end) as tc))as te
inner join subway_line as tf on te.sl_id = tf.sl_id
) as td
on tb.ss_station_name = td.ss_station_name group by td.sl_line_name,tb.sl_line_name,tb.sl_id,td.sl_id,tb.ss_station_name

2011-3-1 特此记录。