公共交通的多次查询

w025888 2008-01-10 09:49:58

公交多次转换查询

...全文

157 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

octverve 2008-01-10

打赏
举报

看看邹建的《sqlserver2000开发与管理应用实例》

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'
/*--结果
起点站终点站乘车线路
---------- ------------ -----------------------------------------------------------
站A 站L (8路: 站A->站B->站C->站D->站J->站L)
--*/

SeerMi 2008-01-10

打赏
举报

帮你顶吧

jingshisj 2008-01-10

打赏
举报

看了一半，抗不住了

MinToMin 2008-01-10

打赏
举报

哥德巴克猜想。。。

balenoww 2008-01-10

打赏
举报

关注mark

flashlove2008 2008-01-10

打赏
举报

太多了看的晕

草原可可 2008-01-10

打赏
举报

我做个这个这里有些查询代码看看
Dim startTag,endTag,rs,sql,i,j,str,q,r,k,a(),b(),m,aa(),aaa(),aaaa(),aaaaa(),bb(),bbb(),bbbb(),bbbbb(),Onlynum,oneDirect,ii,jj,ww
i=0
j=0
startTag=request("start")
EndTag=request("end")
sql="SELECT * FROM Bus025 WHERE (CHARINDEX('"&startTag&"', BusLine) > 0)"
set rs=server.CreateObject("Adodb.recordset")
rs.open sql,conn,1,1
'取得所有经过这站的线路
redim a(rs.recordcount)
redim aa(rs.recordcount)
redim aaa(rs.recordcount)
redim aaaa(rs.recordcount)
redim aaaaa(rs.recordcount)
do while not rs.eof
a(i)=rs("BusLine")
aa(i)=rs("BusNo")
aaa(i)=rs("stime")
aaaa(i)=rs("etime")
aaaaa(i)=rs("dtime")
i=i+1
rs.movenext
loop
rs.close

sql="SELECT * FROM Bus025 WHERE (CHARINDEX('"&EndTag&"', BusLine) > 0)"
rs.open sql,conn,1,1
redim b(rs.recordcount)
redim bb(rs.recordcount)
redim bbb(rs.recordcount)
redim bbbb(rs.recordcount)
redim bbbbb(rs.recordcount)
'取得所有经过这站的线路
do while not rs.eof
b(j)=rs("BusLine")
bb(j)=rs("BusNo")
j=j+1
rs.movenext
loop
rs.close

sql="SELECT * FROM Bus025 WHERE (CHARINDEX('"&EndTag&"', BusLine) > 0) and (CHARINDEX('"&startTag&"', BusLine) > 0)"
rs.open sql,conn,1,1
if not rs.eof then
str="有线路如下: "
m=0
do while not rs.eof
m=m+1
q=rs("BusLine")
r=split(q,"-")
str=str&" 第"&m&"种方案--直达线路 "&rs("BusNo")&"("&r(0)&rs("stime")&r(ubound(r))&rs("etime")&")"&"最后更新时间"&" "&rs("Dtime")&" "
if Instr(q,startTag) <Instr(q,EndTag) then
for ii=0 to ubound(r) step 1
if r(ii)=starttag or r(ii)=endtag then
str=str&" "&r(ii)&" "&"-"
else
str=str&r(ii)&"-"
end if
next
else
for ii=ubound(r) to 0 step -1
if r(ii)=endtag or r(ii)=starttag then
str=str&" "&r(ii)&" "&"-"
else
str=str&r(ii)&"-"
end if
next
end if
str=left(str,len(str)-1)
str=str&" "
rs.movenext
loop
else
str=str+"没有直达车"
end if
rs.close
'取得转达一次的所有车
'response.Write str
'response.End()
if str="没有直达车" then
ww=oneline(a,aa,b,bb,startTag,EndTag,m)

str=str+" 需要转车方案 "+ww
else
str=str
end if

function oneline(a,aa,b,bb,startTag,EndTag,m)
dim p,n,c,o,f,ano,bno,statnum,statnum1,str1,Lcount,sline,sCount,exst,str2,qishi1,zhongdian1,qishi2,zhongdian2,www()
'取得所有经过起始点的线路
for p=0 to ubound(a)-1
'取得其中的某条线路分割出各个站点
ano=aa(p)
r=split(a(p),"-")
qishi1=r(0)
zhongdian1=r(ubound(r))
'取得所有经过b点的所有线路
for n=0 to ubound(b)-1
bno=bb(n)
if cstr(bno) <> cstr(ano) then
'取得经过某条信路上所有站点
c=split(b(n),"-")
qishi2=c(0)
zhongdian2=c(ubound(c))
'遍例两个数组中看是否有相同的站点，有就读出来，没有就算了
statnum=0
Lcount=0
sCount=0
exst=false
for f=0 to ubound(r)-1
for o=0 to ubound(c)-1
if trim(r(f))=trim(c(o)) then
If trim(startTag) <> trim(r(f)) then
statnum1=getLineCount(startTag,trim(r(f)),a(p))+getLineCount(trim(r(f)),EndTag,b(n))
if Lcount=0 then
sCount=statnum1
Lcount=Lcount+1
exst=true
str1="(共"&sCount&"站) "+aa(p)&"("&qishi1&aaa(p)&zhongdian1&aaaa(p)&")"&"最新更新时间:"&aaaaa(p)&" "&getMidLine(startTag,trim(r(f)),a(p))&" "&"转"&bb(n)&"("&qishi2&aaaa(p)&zhongdian2&aaa(p)&")"&"最新更新时间"&aaaaa(p)&" "&getMidLine(trim(r(f)),EndTag,b(n))&" "
end if
if Cint(sCount)> Cint(statnum1) then
exst=true
sCount=statnum1
str1="(共"&sCount&"站) "+aa(p)&"("&qishi1&aaa(p)&zhongdian1&aaaa(p)&")"&"最新更新时间"&aaaaa(p)&" "&getMidLine(startTag,trim(r(f)),a(p))&" "&"转"&bb(n)&"("&qishi2&aaaa(p)&zhongdian2&aaa(p)&")"&"最新更新时间"&aaaaa(p)&" "&getMidLine(trim(r(f)),EndTag,b(n))&" "
end if
end if
end if
next
next

if exst=true then
m=m+1
str2=str2&" 第"&m&"种方案---转达一般车"
str2=str2+str1
end if

end if
next
next
oneline=str2
end function
response.write str
'取得一线路上两点间的所有站点
function getMidLine(startT,sameT,lineT)
dim statN,ka,Knum
statN=split(lineT,"-")
if instr(lineT,startT) <instr(lineT,sameT) then
Knum=ubound(statN)-1
for ka=0 to ubound(statN)-1
if trim(statN(ka))=startT then
kum=ka
getMidLine=" "&startT&" "
end if
if trim(statN(ka))=sameT then
kum=ka
getMidLine=getMidLine&" - "&" "&sameT&" "
exit for
end if
if ka> kum then
getMidLine=getMidLine&" - "&statN(ka)
end if
next
else
Knum=ubound(statN)-1
for ka=0 to ubound(statN)-1
if trim(statN(ka))=sameT then
kum=ka
getMidLine=" - "&sameT&" "
end if
if trim(statN(ka))=startT then
kum=ka
getMidLine=" "&startT&" "&getMidLine
exit for
end if
if ka> kum then
getMidLine=" - "&statN(ka)&getMidLine
end if
next
end if
end function

'取得一条张上两点间所有的站数
function getLineCount(startT,sameT,lineT)
dim statN,ka,Knum
statN=split(lineT,"-")
if instr(lineT,startT) <instr(lineT,sameT) then
Knum=ubound(statN)-1
for ka=0 to ubound(statN)-1
if trim(statN(ka))=startT then
kum=ka
getLineCount=0
end if
if trim(statN(ka))=sameT then
kum=ka
getLineCount=getLineCount+1
exit for
end if
if ka> kum then
getLineCount=getLineCount+1
end if
next
else
Knum=ubound(statN)-1
for ka=0 to ubound(statN)-1
if trim(statN(ka))=sameT then
kum=ka
getLineCount=0
end if
if ka> kum then
getLineCount=getLineCount+1
end if
if trim(statN(ka))=startT then
kum=ka
getLineCount=getLineCount+1
exit for
end if
next
end if
end function

w025888 2008-01-10