公交查询-获得一次中转车次

最新推荐文章于 2024-04-23 08:45:00 发布

原创最新推荐文章于 2024-04-23 08:45:00 发布 · 688 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #join #go #table

数据库专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一个用于查询中转公交车次的存储过程实现方法。该过程通过输入起点站和终点站名称，检索出所有可能经过这两点的公交线路，并找出共同的中转站点。

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

--获取一次中转车次
drop proc bus_m_qry
Go
create procedure bus_m_qry
@station_start varchar(20),
@station_stop varchar(20)
--@station_transit varchar(20) --中转
as
set nocount on
select s.stationName,s.stationNum into #a from station s
    inner join bus b on b.stationNum=s.stationNum
    where checi in
(select b.checi from Bus b
inner join station s
on s.stationNum=b.stationNum
where s.stationName=@Station_Start)
    group by s.stationName,s.stationNum order by stationName asc

select stationName into #b from #a a
inner join bus b on b.stationNum=a.stationNum
where checi in
(select b.checi from Bus b
inner join station s
on s.stationNum=b.stationNum
where s.stationName=@Station_Stop)
group by stationName order by stationName asc

select * from #b
GO
exec bus_m_qry '通利公交公司','鞋城'
Go

表如下

CREATE TABLE [dbo].[Bus] (
[checi] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[stationID] [int] NOT NULL ,
[stationNum] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BusNum] (
[checi] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[站点1] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点3] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点4] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点5] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点6] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点7] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点8] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点9] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点10] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点11] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点12] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点13] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点14] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点15] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点16] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点17] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点18] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点19] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点20] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点21] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点22] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点23] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点24] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点25] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点26] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点27] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点28] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点29] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[站点30] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO