[SQL]公交新路问题

浏览数：27 / 时间：2015年06月12日
--解答一
CREATE TABLE T_Line(
ID      nvarchar(10),  --公交线路号
Station nvarchar(10),  --站点名称
Orders  int)           --行车方向(通过它反应每个站的上一个、下一个站)
INSERT T_Line 
SELECT N‘8路‘  ,N‘站A‘,1 UNION ALL
SELECT N‘8路‘  ,N‘站B‘,2 UNION ALL
SELECT N‘8路‘  ,N‘站C‘,3 UNION ALL
SELECT N‘8路‘  ,N‘站D‘,4 UNION ALL
SELECT N‘8路‘  ,N‘站J‘,5 UNION ALL
SELECT N‘8路‘  ,N‘站L‘,6 UNION ALL
SELECT N‘8路‘  ,N‘站M‘,7 UNION ALL
SELECT N‘20路‘ ,N‘站G‘,1 UNION ALL
SELECT N‘20路‘ ,N‘站H‘,2 UNION ALL
SELECT N‘20路‘ ,N‘站I‘,3 UNION ALL
SELECT N‘20路‘ ,N‘站J‘,4 UNION ALL
SELECT N‘20路‘ ,N‘站L‘,5 UNION ALL
SELECT N‘20路‘ ,N‘站M‘,6 UNION ALL
SELECT N‘255路‘,N‘站N‘,1 UNION ALL
SELECT N‘255路‘,N‘站O‘,2 UNION ALL
SELECT N‘255路‘,N‘站P‘,3 UNION ALL
SELECT N‘255路‘,N‘站Q‘,4 UNION ALL
SELECT N‘255路‘,N‘站J‘,5 UNION ALL
SELECT N‘255路‘,N‘站D‘,6 UNION ALL
SELECT N‘255路‘,N‘站E‘,7 UNION ALL
SELECT N‘255路‘,N‘站F‘,8
GO

--乘车线路查询存储过程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop  nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT ID,Station,
Line=CAST(‘(‘+RTRIM(ID)+‘: ‘+RTRIM(Station) as nvarchar(4000)),
Orders=Orders,
[Level]=@l
INTO # FROM T_Line
WHERE Station=@Station_Start
WHILE @@ROWCOUNT>0 
AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop)
BEGIN
SET @l=@l+1
INSERT #(Line,ID,Station,Orders,[Level])
SELECT 
Line=a.Line+CASE
WHEN a.ID=b.ID THEN N‘->‘+RTRIM(b.Station)
ELSE N‘) ∝ (‘+RTRIM(b.ID)
+N‘: ‘+RTRIM(b.Station) END,
b.ID,b.Station,b.Orders,@l
FROM # a,T_Line b
WHERE a.[Level]=@l-1
AND(a.Station=b.Station AND a.ID<>b.ID
OR a.ID=b.ID AND(
a.Orders=b.Orders+1
OR
a.Orders=b.Orders-1))
AND LEN(a.Line)<4000
AND PATINDEX(‘%[ >]‘+b.Station+‘[-)]%‘,a.Line)=0
END
SELECT N‘起点站‘=@Station_Start
,N‘终点站‘=@Station_Stop
,N‘乘车线路‘=Line+N‘)‘ 
FROM # 
WHERE [Level]=@l 
AND Station=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
SELECT * FROM #
GO

--调用
EXEC p_qry N‘站A‘,N‘站L‘
drop table T_Line
drop procedure p_qry
/*--结果
起点站  终点站  乘车线路
---------- ------------ -----------------------------------------------------------
站A    站L    (8路: 站A->站B->站C->站D->站J->站L)
--*/

--解答二
CREATE TABLE T_Line(busID nvarchar(20),stationID nvarchar(20),staTionName nvarchar(50),stationSort  int)
INSERT T_Line 
SELECT N‘B0280017‘,N‘S0280030‘,N‘荷花池‘,1 UNION ALL
SELECT N‘B0280017‘,N‘S0280028‘,N‘火车北站‘,2 UNION ALL
SELECT N‘B0280017‘,N‘S0280289‘,N‘二环北二段‘,3 UNION ALL
SELECT N‘B0280018‘,N‘S0280011‘,N‘人名北路‘,1 UNION ALL
SELECT N‘B0280018‘,N‘S0280028‘,N‘火车北站‘,2 UNION ALL
SELECT N‘B0280018‘,N‘S0280213‘,N‘五块石‘,3 UNION ALL
SELECT N‘B0280019‘,N‘S0280013‘,N‘五块石‘,1 UNION ALL
SELECT N‘B0280019‘,N‘S0280014‘,N‘二环路东二段‘,2 UNION ALL
SELECT N‘B0280019‘,N‘S0280215‘,N‘二环东三段‘,3 
GO

--乘车线路查询存储过程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop  nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT busID,staTionName,
Line=CAST(‘(‘+RTRIM(busID)+‘: ‘+RTRIM(staTionName) as nvarchar(4000)),
stationSort=stationSort,
[Level]=@l
INTO # FROM T_Line
WHERE staTionName=@Station_Start
WHILE @@ROWCOUNT>0 
AND NOT EXISTS(SELECT * FROM # WHERE staTionName=@Station_Stop)
BEGIN
SET @l=@l+1
INSERT #(Line,busID,staTionName,stationSort,[Level])
SELECT 
Line=a.Line+CASE
WHEN a.busID=b.busID THEN N‘->‘+RTRIM(b.staTionName)
ELSE N‘) ∝ (‘+RTRIM(b.busID)
+N‘: ‘+RTRIM(b.staTionName) END,
b.busID,b.staTionName,b.stationSort,@l
FROM # a,T_Line b
WHERE a.[Level]=@l-1
AND(a.staTionName=b.staTionName AND a.busID<>b.busID
OR a.busID=b.busID AND(
a.stationSort=b.stationSort+1
OR
a.stationSort=b.stationSort-1))
AND LEN(a.Line)<4000
AND PATINDEX(‘%[ >]‘+b.staTionName+‘[-)]%‘,a.Line)=0
END
SELECT N‘起点站‘=@Station_Start
,N‘终点站‘=@Station_Stop
,N‘乘车线路‘=Line+N‘)‘ 
FROM # 
WHERE [Level]=@l 
AND staTionName=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
SELECT * FROM #
GO

--调用
EXEC p_qry N‘荷花池‘,N‘二环东三段‘


drop proc p_qry   ----删除存储过程
drop table T_Line ----删除表

/*    结果

起点站  终点站     乘车线路  
*/

--解答三
CREATE TABLE T_Line(busID nvarchar(20),stationID nvarchar(20),staTionName nvarchar(50),stationSort  int)
INSERT T_Line 
SELECT N‘B0280017‘,N‘S0280030‘,N‘荷花池‘,1 UNION ALL
SELECT N‘B0280017‘,N‘S0280028‘,N‘火车北站‘,2 UNION ALL
SELECT N‘B0280017‘,N‘S0280289‘,N‘二环北二段‘,3 UNION ALL
SELECT N‘B0280018‘,N‘S0280011‘,N‘人名北路‘,1 UNION ALL
SELECT N‘B0280018‘,N‘S0280028‘,N‘火车北站‘,2 UNION ALL
SELECT N‘B0280018‘,N‘S0280213‘,N‘五块石‘,3 UNION ALL
SELECT N‘B0280019‘,N‘S0280013‘,N‘五块石‘,1 UNION ALL
SELECT N‘B0280019‘,N‘S0280014‘,N‘二环路东二段‘,2 UNION ALL
SELECT N‘B0280019‘,N‘S0280215‘,N‘二环东三段‘,3 
GO

--乘车线路查询存储过程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop  nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT busID,staTionName,
Line=CAST(‘(‘+RTRIM(busID)+‘: ‘+RTRIM(staTionName) as nvarchar(4000)),
stationSort=stationSort,
[Level]=@l
INTO # FROM T_Line
WHERE staTionName=@Station_Start
WHILE @@ROWCOUNT>0 
AND NOT EXISTS(SELECT * FROM # WHERE staTionName=@Station_Stop)
BEGIN
SET @l=@l+1
INSERT #(Line,busID,staTionName,stationSort,[Level])
SELECT 
Line=a.Line+CASE
WHEN a.busID=b.busID THEN N‘->‘+RTRIM(b.staTionName)
ELSE N‘) ∝ (‘+RTRIM(b.busID)
+N‘: ‘+RTRIM(b.staTionName) END,
b.busID,b.staTionName,b.stationSort,@l
FROM # a,T_Line b
WHERE a.[Level]=@l-1
AND(a.staTionName=b.staTionName AND a.busID<>b.busID
OR a.busID=b.busID AND(
a.stationSort=b.stationSort+1
OR
a.stationSort=b.stationSort-1))
AND LEN(a.Line)<4000
AND PATINDEX(‘%[ >]‘+b.staTionName+‘[-)]%‘,a.Line)=0
END
SELECT N‘起点站‘=@Station_Start
,N‘终点站‘=@Station_Stop
,N‘乘车线路‘=Line+N‘)‘ 
FROM # 
WHERE [Level]=@l 
AND staTionName=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
SELECT * FROM #
GO

--调用
EXEC p_qry N‘荷花池‘,N‘二环东三段‘


drop proc p_qry
drop table T_Line

/*    结果

起点站        终点站        乘车线路                                                                                                                                                                                                                                                             
---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
荷花池        二环东三段      (B0280017: 荷花池->火车北站) ∝ (B0280018: 火车北站->五块石) ∝ (B0280019: 五块石->二环路东二段->二环东三段)


*/