有一批货要从A港运到B港，寻找合适的航线，求SQL

Question

sql

Answer 1

这个涉及到算法问题，典型的迪杰斯特拉算法
用应用实现就已经不简单了，用sql也行，你百度一下“sql 迪杰斯特拉算法”
王垠早就实现了，不过不是一般的复杂

Answer 2

复制了一下核心代码，真心不是一般的复杂，我是没能够弄懂use TestDBgo--Procedure:if object_id('up_GetPath') Is not null    Drop proc up_GetPathgocreate proc up_GetPath(    @Node nvarchar(50),    @RelatedNode nvarchar(50))Asset nocount on declare    @level smallint =1, --当前搜索的深度    @MaxLevel smallint=100, --最大可搜索深度    @Node_WhileFlag bit=1, --以@Node作为中心进行搜索时候，作为能否循环搜索的标记    @RelatedNode_WhileFlag bit=1 --以@RelatedNode作为中心进行搜索时候，作为能否循环搜索的标记 --如果直接找到两个Node存在直接关系就直接返回if Exists(select 1 from RelationGraph where (Node=@Node And RelatedNode=@RelatedNode) or (Node=@RelatedNode And RelatedNode=@Node) ) or @Node=@RelatedNodebegin    select convert(nvarchar(2000),@Node + ' --> '+ @RelatedNode) As RelationGraphPath,convert(smallint,0) As StopCount    returnend --  if object_id('tempdb..#1') Is not null Drop Table #1 --临时表#1，存储的是以@Node作为中心向外扩展的各节点数据if object_id('tempdb..#2') Is not null Drop Table #2 --临时表#2，存储的是以@RelatedNode作为中心向外扩展的各节点数据 create table #1(    Node nvarchar(50),--相对源点    RelatedNode nvarchar(50), --相对目标    Level smallint --深度    )    create table #2(Node nvarchar(50),RelatedNode nvarchar(50),Level smallint) insert into #1 ( Node, RelatedNode, Level )    select Node, RelatedNode, @level from RelationGraph a where a.Node =@Node union --正向：以@Node作为源查询    select RelatedNode, Node, @level from RelationGraph a where a.RelatedNode = @Node --反向：以@Node作为目标进行查询set @Node_WhileFlag=sign(@@rowcount)    insert into #2 ( Node, RelatedNode, Level )    select Node, RelatedNode, @level from RelationGraph a where a.Node =@RelatedNode union --正向：以@RelatedNode作为源查询    select RelatedNode, Node, @level from RelationGraph a where a.RelatedNode = @RelatedNode --反向：以@RelatedNode作为目标进行查询set @RelatedNode_WhileFlag=sign(@@rowcount) --如果在表RelationGraph中找不到@Node 或 @RelatedNode 数据，就直接跳过后面的While过程if not exists(select 1 from #1) or not exists(select 1 from #2)begin    goto While_Outend  while not exists(select 1 from #1 a inner join #2 b on b.RelatedNode=a.RelatedNode) --判断是否出现切点     and (@Node_WhileFlag|@RelatedNode_WhileFlag)>0 --判断是否能搜索     And @level<@MaxLevel --控制深度begin    if @Node_WhileFlag >0    begin            insert into #1 ( Node, RelatedNode, Level )            --正向            select a.Node,a.RelatedNode,@level+1                From RelationGraph a                where exists(select 1 from #1 where RelatedNode=a.Node And Level=@level) And                    Not exists(select 1 from #1 where Node=a.Node)                        union            --反向            select a.RelatedNode,a.Node,@level+1                From RelationGraph a                where exists(select 1 from #1 where RelatedNode=a.RelatedNode And Level=@level) And                    Not exists(select 1 from #1 where Node=a.RelatedNode)                set @Node_WhileFlag=sign(@@rowcount)     end              if @RelatedNode_WhileFlag >0    begin                insert into #2 ( Node, RelatedNode, Level )            --正向            select a.Node,a.RelatedNode,@level+1                From RelationGraph a                where exists(select 1 from #2 where RelatedNode=a.Node And Level=@level) And                    Not exists(select 1 from #2 where Node=a.Node)            union            --反向            select a.RelatedNode,a.Node,@level+1                From RelationGraph a                where exists(select 1 from #2 where RelatedNode=a.RelatedNode And Level=@level) And                    Not exists(select 1 from #2 where Node=a.RelatedNode)        set @RelatedNode_WhileFlag=sign(@@rowcount)    end        select @level+=1end While_Out: --下面是构造返回的结果路径if object_id('tempdb..#Path1') Is not null Drop Table #Path1if object_id('tempdb..#Path2') Is not null Drop Table #Path2 ;with cte_path1 As(select a.Node,a.RelatedNode,Level,convert(nvarchar(2000),a.Node+' -> '+a.RelatedNode) As RelationGraphPath,Convert(smallint,1) As PathLevel From #1 a where exists(select 1 from #2 where RelatedNode=a.RelatedNode)union allselect b.Node,a.RelatedNode,b.Level,convert(nvarchar(2000),b.Node+' -> '+a.RelationGraphPath) As RelationGraphPath ,Convert(smallint,a.PathLevel+1) As PathLevel    from cte_path1 a        inner join #1 b on b.RelatedNode=a.Node            and b.Level=a.Level-1)select * Into #Path1 from cte_path1 ;with cte_path2 As(select a.Node,a.RelatedNode,Level,convert(nvarchar(2000),a.Node) As RelationGraphPath,Convert(smallint,1) As PathLevel From #2 a where exists(select 1 from #1 where RelatedNode=a.RelatedNode)union allselect b.Node,a.RelatedNode,b.Level,convert(nvarchar(2000),a.RelationGraphPath+' -> '+b.Node) As RelationGraphPath ,Convert(smallint,a.PathLevel+1)    from cte_path2 a        inner join #2 b on b.RelatedNode=a.Node            and b.Level=a.Level-1)select * Into #Path2 from cte_path2 ;with cte_result As(select a.RelationGraphPath+' -> '+b.RelationGraphPath As RelationGraphPath,a.PathLevel+b.PathLevel -1 As StopCount,rank() over(order by a.PathLevel+b.PathLevel) As Result_row    From #Path1 a        inner join #Path2 b on b.RelatedNode=a.RelatedNode            and b.Level=1    where a.Level=1)    select distinct RelationGraphPath,StopCount From cte_result where Result_row=1go

Answer 3

DECLARE @line table(LineId int,Code nvarchar(10));
INSERT INTO @line SELECT 1,'L01' UNION ALL SELECT 2,'L02' UNION ALL SELECT 3,'L03';DECLARE @port table(PortId int,Name nvarchar(10));
INSERT INTO @port SELECT 1,'P01' UNION ALL SELECT 2,'P02' UNION ALL SELECT 3,'P03';DECLARE @route TABLE(PortId INT ,LineId INT,sn INT ,et DATETIME,st DATETIME);INSERT INTO @route
        ( PortId ,
          LineId ,
          sn ,
          et ,
          st
        )VALUES  
( 1,1,1,'','2014-09-10 07:06:32')
,( 2,1,2,'2014-09-10 08:06:32','2014-09-10 09:06:32')
,( 3,1,3,'2014-09-10 10:06:32',''),( 2,2,1,'','2014-09-10 07:06:32')
,( 1,2,2,'2014-09-10 08:06:32','2014-09-10 09:06:32')
,( 3,2,3,'2014-09-10 10:06:32',''),( 2,3,1,'','2014-09-10 07:06:32')
,( 3,3,2,'2014-09-10 10:06:32','')
--DECLARE @ps INT=1,@pe INT=3
--DECLARE @ps INT=2,@pe INT=1
DECLARE @ps INT=3,@pe INT=1;WITH cte AS (
SELECT 
MAX(CASE WHEN PortId=@ps THEN PortId END) AS ps,MAX(CASE WHEN PortId=@ps THEN sn END) AS ss
,MAX(CASE WHEN PortId=@pe THEN PortId END) AS pe,MAX(CASE WHEN PortId=@pe THEN sn END) AS se
,LineId
FROM @route a WHERE PortId=@ps OR PortId=@pe
GROUP BY LineId
)
SELECT LineId AS [可以的行线],* FROM cte WHERE ps IS NOT NULL AND pe IS NOT NULL AND se>ss;/*
--结果DECLARE @ps INT=1,@pe INT=3可以的行线       ps          ss          pe          se          LineId
----------- ----------- ----------- ----------- ----------- -----------
1           1           1           3           3           1
2           1           2           3           3           2--DECLARE @ps INT=2,@pe INT=1
可以的行线       ps          ss          pe          se          LineId
----------- ----------- ----------- ----------- ----------- -----------
2           2           1           1           2           2--DECLARE @ps INT=3,@pe INT=1
可以的行线       ps          ss          pe          se          LineId
----------- ----------- ----------- ----------- ----------- -----------
*/

Answer 4

这算法不能由应用程序来实现么？
非要用SQL去拼。。

Answer 5

有这么复杂吗?
港口ID   名称
1            A
2            B
3            C
4            D
5            E
---------------
航线ID    代号
1            MX50
2            ZD31
----------------
航线ID 港口ID  顺序 
1             1          1
1             3          2
1            2           3
2            5           1
2            4           2
2            3           3
2            2           4
2            1           5
================
如果从港口1到港口3，航线1（1→3→2）符合，航线2（5→4→3→2→1）不符合

Answer 6

这里又不是求最优解,为什么会用到迪杰斯特拉算法呢?先经过A再经过B，就是合适的航线。
航线（航线ID，代号）
港口（港口ID，名称）
路由（港口ID，航线ID，经过顺序，到达时间，出发时间）如果仅仅考虑“先经过A再经过B，就是合适的航线”
那么仅仅一张路由表就够了select * from 路由 where  where charindex('A','经过顺序')>0 and charindex('B','经过顺序')>charindex('A','经过顺序')

Answer 7

SELECT a.航线ID, 
       a.到达时间，a.出发时间,
       b.到达时间，b.出发时间
  FROM 路由 a
  JOIN 路由 b
    ON a.航线ID = b.航线ID
   AND a.经过顺序 < b.经过顺序
 WHERE a.港口ID = 2
   AND b.港口ID = 8
需要其他信息再把其他的表关联上去。

Answer 8

试试下面的逻辑
LineID PortID SeqNo
1 1 1
1 2 2
1 3 3
1 5 4
2 1 1
2 5 2
3 2 1
3 1 2
4 5 1
4 1 2
5 1 1
5 5 2 DECLARE @FromPort INT = 1
DECLARE @ToPort INT = 5
SELECT t.lineid
FROM   (
           SELECT lineid,portid,seqno,CASE WHEN portid = @FromPort THEN seqno ELSE 0 END  AS FromSeqNo,
                  CASE WHEN portid = @ToPort THEN seqno ELSE 0 END  AS EndSeqNo
           FROM   RouteList
           WHERE  portid IN (@FromPort, @ToPort)
       ) t
GROUP BY
       t.lineid
HAVING COUNT(0) > 1 AND SUM(t.FromSeqNo) < SUM(t.EndSeqNo)
返回航线1,2,5

Answer 9

思路：
将两个港口对应的序号转为列
然后根据航线对应两个港口的数据分组：
1.连个港口都在航线中，则航线的count（0）>1。应该是2
2.EndPort序号大于StartPort，则保证顺序要求。上面的逻辑没有考虑一个港口在航线中静过多次的情况

Answer 10

--港口表
create table port 
(
portid int,
name varchar(100)
)
insert into port values(1,'A')
insert into port values(2,'B')
insert into port values(3,'C')
insert into port values(4,'D')
insert into port values(5,'E')
--线路表
CREATE TABLE line
(
lineid int,
name varchar(100)
) 
insert into LINE values(1,'MX50')
insert into LINE values(2,'ZD31')
--线路明细表
create table linedetail
(
lineid int,
portid int,
orderid int
) 
insert into linedetail values(1,1,1)
insert into linedetail values(1,3,2) 
insert into linedetail values(1,2,3) 
insert into linedetail values(2,5,1)
insert into linedetail values(2,4,2) 
insert into linedetail values(2,3,3)
insert into linedetail values(2,2,4)
insert into linedetail values(2,1,5)  
 
 
 
declare @beginport varchar(100);
declare @endport varchar(100);
set @beginport='B';--起始港口名称
set @endport='A'; --目标港口名称
select t5.lineid,(select name from line where lineid=t5.lineid)name from
(
select lineid,min(orderid)orderid from port t1,linedetail t2
where t1.portid=t2.portid and name in(@beginport,@endport) group by lineid 
having count(1)=2) t5 ,--查出所有包含起始和目标港口的线路，并取出顺序靠前的那个
(
select orderid,lineid from port t3,linedetail t4 where t3.portid=t4.portid and t3.name=@beginport
)t6--如果取出的顺序靠前的那个值正好是起始的港口位置，那说明是可以到达的
where t5.orderid=t6.orderid and t5.lineid=t6.lineid