主要表结构:线路表
线路号 站名 序号(从起点数起的序号)
8 A 1
8 B 2
8 C 3
6 D 1
6 C 2
6 E 3坐车A-E
SELECT A.站名 FROM TABLENAME A,TABLENAME B
WHERE A.站名=B.站名
AND A.线路号<>B.线路号
AND EXISTS (
SELECT * FROM TABLENAME C
WHERE C.站名='A'
AND C.线路号=A.线路号
)
AND EXISTS (
SELECT * FROM TABLENAME D
WHERE D.站名='E'
AND D.线路号=B.线路号
)
线路号 站名 序号(从起点数起的序号)
8 A 1
8 B 2
8 C 3
6 D 1
6 C 2
6 E 3坐车A-E
SELECT A.站名 FROM TABLENAME A,TABLENAME B
WHERE A.站名=B.站名
AND A.线路号<>B.线路号
AND EXISTS (
SELECT * FROM TABLENAME C
WHERE C.站名='A'
AND C.线路号=A.线路号
)
AND EXISTS (
SELECT * FROM TABLENAME D
WHERE D.站名='E'
AND D.线路号=B.线路号
)
解决方案 »
- 请问SQL SERVER有什么可以用的中间件?
- 如何用更有效率的SQL实现游标功能?
- MS SQL在查询EXCEL时有没有列数限制?
- 小问题 在线等 !!!
- sql
- sql插入10万数据怎么写
- sql语句把结果横向累加的问题
- select * from tt for xml查询的xml结果怎么转换为字符串输出
- sql中出版过的database,所有的出版项都删除后,怎还不能删除database呢,怎样才能删除有过出版的database?
- 怎样将一个GIF文件,放入MS SQL Server内的binary字段????
- 这句SQL语句怎么写?
- 如何用ADOQUERY添加一条新的记录,时间类型的???(sql server,delphi 5)
有这种情况),加以个车站表车站表
车站ID 站名
1 A
2 B
3 C
4 D
5 E线路表
线路号 车站ID 序号(从起点数起的序号)
8 1 1
8 2 2
8 3 3
6 4 1
6 3 2
6 5 3
create table test(xl int,zh int,xh int)
insert test select 8,1,1
union all select 8,2,2
union all select 8,3,3
union all select 6,4,1
union all select 6,3,2
union all select 6,5,3
union all select 7,8,1
union all select 7,5,2
union all select 7,9,3
union all select 7,10,4
union all select 7,11,5
select xl 线路,zh 站号,xh 序号 from test线路 站号 序号
----------- ----------- -----------
8 1 1
8 2 2
8 3 3
6 4 1
6 3 2
6 5 3
7 8 1
7 5 2
7 9 3
7 10 4
7 11 5declare @st int,@end int
set @st=2--起始
set @end=10--到达
select a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en into #temp from test a,test b where a.zh=@st and a.xl=b.xl and a.zh<>b.zh while not exists(select 1 from #temp where en=@end)
insert #temp select a.st,b.en,a.st_en+','+b.st_en from #temp a,
(select a.xl,a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en from test a,test b where a.xl=b.xl and a.zh<>b.zh) b
where a.en=b.st and charindex(rtrim(b.xl)+'.',a.st_en)=0--and a.xh<b.xhselect * from #temp where st=@st and en=@enddrop table #tempst en st_en
----------- ----------- ---------------------------
2 10 8.2-8.3,6.3-6.5,7.5-7.10
declare @st int,@end int
set @st=2
set @end=10
select a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en,abs(a.xh-b.xh) as num into #temp from test a,test b where a.zh=@st and a.xl=b.xl and a.zh<>b.zh
while not exists(select 1 from #temp where en=@end)
insert #temp select a.st,b.en,a.st_en+','+b.st_en,a.num+b.num from #temp a,
(select a.xl,a.zh as st,b.zh as en,rtrim(a.xl)+'.'+rtrim(a.zh)+'-'+rtrim(a.xl)+'.'+rtrim(b.zh) st_en,abs(a.xh-b.xh) as num
from test a,test b where a.xl=b.xl and a.zh<>b.zh) b
where a.en=b.st and charindex(rtrim(b.xl)+'.',a.st_en)=0--and a.xh<b.xhselect top 1 * from #temp where st=@st and en=@end order by num
drop table #temp
st en st_en num
----------- ----------- --------------------------------------------------- -----------
2 10 8.2-8.3,6.3-6.5,7.5-7.10 4
做8路 2-3 转6路 3-5 转7路 5-10只是有个问题,你自已修改一下,结果要这样:
008.2-008.3,006.3-006.5,007.5-007.10