sql - Oracle SQL 中的 Sum() 连接方式

Question

我正在尝试解决 oracle SQL 场景。我必须使用 SQL 查询找到两个地方之间的所有可能路径并计算它们之间的总距离、两个地方之间的停靠站数和路径。源表包含 3 列 即 Departure_city、Arrival_City 和 Distance。

我写了一个 SQL 查询并确定了停靠点数和路径。但是我找不到总距离。我的查询是:

 select * from(select arrive , level-1 , sys_connect_by_path (depart ,',')   
    from travel  
    start with depart = 'Mexico'  
    connect by nocycle prior arrive=depart)  
    where arrive = 'New York';  

这里我把Delhi作为Departure_city，Bangalore作为Arrival city。

Answer 1

这有点棘手，因为您想要获取递归查询所采用路径的 sum。这个解决方案有点不正统，但应该可行:

CREATE OR REPLACE FUNCTION prod.eval (p_equation VARCHAR2)
   RETURN NUMBER IS
   v_result NUMBER;
BEGIN
   IF LENGTH (TRIM (TRANSLATE (p_equation, '1234567890+-()*/', ' ')))
         IS NOT NULL THEN
      raise_application_error (
         -20000,
         'EVAL: Parameter contains non mathematical values');
   END IF;

   EXECUTE IMMEDIATE ' begin :1 := ' || p_equation || '; end;'
      USING OUT v_result;

   RETURN v_result;
END;
/

select * from(select arrive , 
                     level-1 , 
                     sys_connect_by_path (depart ,',') as hops
                     eval('0' || sys_connect_by_path (distance ,'+')) as distance
              from travel  
              start with depart = 'Delhi'  
              connect by nocycle prior arrive=depart)  
where arrive = 'Bangalore';