上海地铁查询脚本

EXEC p_SelectStation
@StartStation=N'巨峰路',
@EndStation=N'四平路'

CREATE PROCEDURE p_SelectStation
@StartStation nvarchar(50),
@EndStation nvarchar(50)
AS
SET NOCOUNT ON

--DECLARE @StartStation nvarchar(50)
--DECLARE @EndStation nvarchar(50)
DECLARE @Strategy nvarchar(50)

--SET @StartStation=N'巨峰路'
--SET @EndStation=N'四平路'

--SET @StartStation=N'世纪大道'
--SET @EndStation=N'四平路'

--SET @StartStation=N'巨峰路'
--SET @EndStation=N'共康路'

DECLARE @Line1 int
DECLARE @Line2 int
DECLARE @StartLine TABLE(Line varchar(50),Seque int)
DECLARE @EndLine TABLE(Line varchar(50),Seque int)
DECLARE @currencyLine TABLE(Line varchar(50),Seque int)
DECLARE @OutputT TABLE(ExchangeNum int IDENTITY(1,1), [From] nvarchar(50),[To] nvarchar(50),Line int,StationNum int)

INSERT INTO @StartLine
SELECT [LineNo],StationSequence
FROM tbl_StationInfo
WHERE StationNAME=@StartStation

INSERT INTO @EndLine
SELECT [LineNo],StationSequence
FROM tbl_StationInfo
WHERE StationNAME=@EndStation

SELECT @Line1=Line from @StartLine
SELECT @Line2=Line from @EndLine

INSERT INTO @currencyLine
SELECT start.Line,ABS(start.Seque-endline.Seque)
FROM @StartLine start
INNER JOIN @EndLine endline
ON start.Line=endline.Line

IF(EXISTS(SELECT * FROM @currencyLine))
BEGIN
-- no Need exchange
SELECT [From]=@StartStation ,[To]=@EndStation,Line=Line,StationNum=Seque
FROM @currencyLine
END
ELSE
BEGIN
DECLARE @AllStartStationLine TABLE(StationName nvarchar(50),Line int,Seque int)
DECLARE @AllEndStationLine TABLE(StationName nvarchar(50),Line int,Seque int)
DECLARE @ExchangeLine TABLE(ExchangeNum int IDENTITY(1,1), StationName nvarchar(50),Line int,Seque int)

INSERT INTO @AllStartStationLine
SELECT allInfo.StationName,allInfo.[LineNo],ABS(allInfo.StationSequence-start.Seque)
FROM tbl_StationInfo allInfo
INNER JOIN @StartLine start
ON allInfo.[LineNo] =start.Line

INSERT INTO @AllEndStationLine
SELECT allInfo.StationName,allInfo.[LineNo],ABS(allInfo.StationSequence-endline.Seque)
FROM tbl_StationInfo allInfo
INNER JOIN @EndLine endline
ON allInfo.[LineNo] =endline.Line

--exchange once
INSERT INTO @ExchangeLine
SELECT ALLStart.*
FROM @AllStartStationLine ALLStart
INNER JOIN @AllEndStationLine ALLEnd
ON ALLStart.StationName=ALLEnd.StationName

IF(NOT EXISTS(SELECT * FROM @ExchangeLine))
BEGIN
-- EXCHANGE multi
DECLARE @MultiStartStationLine TABLE(StationName nvarchar(50),Line int,Seque int)
DECLARE @MultiEndStationLine TABLE(StationName nvarchar(50),Line int,Seque int)

--起点可以换乘 路线 地铁站
INSERT INTO @MultiStartStationLine
SELECT tblInfo.StationNAME,tblInfo.[LineNo],Seque
FROM tbl_StationInfo tblInfo
INNER JOIN @AllStartStationLine startInfo
ON startInfo.StationNAME=tblInfo.StationNAME
AND startInfo.Line<>tblInfo.[LineNo]

--终点可以换乘路线的地铁站
INSERT INTO @MultiEndStationLine
SELECT tblInfo.StationNAME,tblInfo.[LineNo],Seque
FROM tbl_StationInfo tblInfo
INNER JOIN @AllEndStationLine endInfo
ON endInfo.StationNAME=tblInfo.StationNAME
AND endInfo.Line<>tblInfo.[LineNo]

IF(EXISTS(SELECT 1 FROM @MultiStartStationLine multiStart
INNER JOIN @MultiEndStationLine multiEnd
ON multiStart.Line=multiEnd.Line AND multiStart.StationName=multiEnd.StationName))
BEGIN
SELECT [From]=@StartStation, Line=multiStart.Line,[To]=multiStart.StationName,StationNum=multiStart.Seque
FROM @MultiStartStationLine multiStart
INNER JOIN @MultiEndStationLine multiEnd
ON multiStart.Line=multiEnd.Line AND multiStart.StationName=multiEnd.StationName

SELECT [From]=multiEnd.StationName, Line=multiEnd.Line,[To]=@EndStation,StationNum=multiEnd.Seque
FROM @MultiStartStationLine multiStart
INNER JOIN @MultiEndStationLine multiEnd
ON multiStart.Line=multiEnd.Line AND multiStart.StationName=multiEnd.StationName

END
ELSE IF(EXISTS(SELECT 1 FROM @MultiStartStationLine multiStart
INNER JOIN @MultiEndStationLine multiEnd
ON multiStart.Line=multiEnd.Line))
BEGIN

DECLARE @StartLineNum int
DECLARE @EndLineNum int
DECLARE @MultiStartLine int
DECLARE @MultiEndLine int
DECLARE @StartLineName NVARCHAR(50)
DECLARE @EndLineName NVARCHAR(50)
DECLARE @MultiChange TABLE(MultiLineID int IDENTITY(1,1),Line int,startName nvarchar(50),endName nvarchar(50))

INSERT INTO @MultiChange
SELECT multiStart.Line,multiStart.StationName,multiEnd.StationName
FROM @MultiStartStationLine multiStart
INNER JOIN @MultiEndStationLine multiEnd
ON multiStart.Line =multiEnd.Line

DECLARE @MultiMin int
DECLARE @MultiMax int
SELECT @MultiMin=ISNULL(MIN(MultiLineID),1),@MultiMax=ISNULL(MAX(MultiLineID),0)
FROM @MultiChange

DECLARE @StartSeque INT
DECLARE @EndSeque int

WHILE(@MultiMin<=@MultiMax)
BEGIN
DELETE FROM @OutputT
SELECT @StartSeque=tblInfo.StationSequence,@StartLineName=tblInfo.StationNAME,@MultiStartLine=multi.Line
FROM tbl_StationInfo tblInfo
INNER JOIN @MultiChange multi
ON multi.startName=tblInfo.StationNAME
WHERE MultiLineID=@MultiMin AND tblInfo.[LineNo]=multi.Line

SELECT @EndSeque=tblInfo.StationSequence,@EndLineName=tblInfo.StationNAME,@MultiEndLine=multi.Line
FROM tbl_StationInfo tblInfo
INNER JOIN @MultiChange multi
ON multi.endName=tblInfo.StationNAME
WHERE MultiLineID=@MultiMin AND tblInfo.[LineNo]=multi.Line

SELECT 'Choose '+Convert(char(2),@MultiMin)+':'

INSERT INTO @OutputT
SELECT [From]=@StartStation ,[To]=StationNAME,Line=@Line1,StationNum=Seque
FROM @MultiStartStationLine
WHERE Line=@MultiStartLine AND StationName =@StartLineName

INSERT INTO @OutputT
SELECT [From]=@StartLineName ,[To]=@EndLineName,Line=@MultiStartLine,StationNum=ABS(@StartSeque-@EndSeque)

INSERT INTO @OutputT
SELECT [From]=StationNAME ,[To]=@EndStation,Line=@Line2,StationNum=Seque
FROM @MultiEndStationLine
WHERE Line=@MultiEndLine AND StationName =@EndLineName

SELECT [From],[To],Line,StationNum FROM @OutputT ORDER BY ExchangeNum ASC
SET @MultiMin=@MultiMin+1
END
END
END

DECLARE @Min int
DECLARE @Max int
DECLARE @ExchangeName NVARCHAR(50)
DECLARE @ExchangeStartNum int
DECLARE @ExchangeEndNum int
DECLARE @ExchangeEndLine int
SELECT @ExchangeEndNum = Seque FROM @AllEndStationLine WHERE StationName=@EndStation
SELECT @Min=(ISNULL(MIN(ExchangeNum),1)),@Max=(ISNULL(MAX(ExchangeNum),0))
FROM @ExchangeLine

WHILE(@Min<=@Max)
BEGIN
SELECT @ExchangeName=StationName FROM @ExchangeLine WHERE ExchangeNum=@Min
SELECT @ExchangeStartNum=Seque,@ExchangeEndLine=Line FROM @AllEndStationLine WHERE StationName=@ExchangeName

INSERT INTO @OutputT
SELECT [From]=@StartStation ,[To]=@ExchangeName,Line=Line,StationNum=Seque
FROM @ExchangeLine
WHERE ExchangeNum=@Min

INSERT INTO @OutputT
SELECT [From]=@ExchangeName ,[To]=@EndStation,Line=@ExchangeEndLine,StationNum=ABS(@ExchangeStartNum-@ExchangeEndNum)

SELECT [From],[To],Line,StationNum FROM @OutputT ORDER BY ExchangeNum ASC

SET @Min=@Min+1
DELETE FROM @OutputT
END
END

SET NOCOUNT OFF

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7192349/viewspace-1009323/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7192349/viewspace-1009323/