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

--获取一次中转车次
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

CREATE TABLE [dbo].[Station] (
 [stationNum] [int] NOT NULL ,
 [stationName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO