DB2 递归查询

506629361

　　表的结构是  
  origin　　　　　destination 　　distance  
  ---------------------------------------  
  Germany　　　　 New York　　　  8000  
  Germany　　　　 Chicago　　　　 8700  
  Chicago               Austin                1300  
  New York             Houston            2100  
  Houston              Austin                300  
  New York            Chicago              950  
  Italy                    New York           10000  
  Ireland               Chicago              10700  
  Chicago              Toronto              400  
  New York            Toronto              350  
  Mexico                Houston             770  
   
  SQL语句是  
  WITH   path   (origin,   destination,   distance,  stops)  
  AS(  
        SELECT   origin,   destination,   distance,  0    
        FROM     flights 
        WHERE    origin='Germany'  
  UNION   ALL  
        SELECT   p.origin, f.destination, p.distance+f.distance,   p.stops+1  
        FROM     flights   f,   path   p  
        WHERE    p.destination=f.origin)  
   
  SELECT   origin,   destination,   distance,   stops   FROM   path    
   
  结果是  
  origin　　　　　destinat　　　　distance             stops  
  -----------------------------------------------------------  
  Germany　　　　 New York　　　 8000                   0  
  Germany　　　　 Chicago　　　　8700                   0  
  Germany             Houston            10100                 1  
  Germany             Chicago             8950                   1  
  Germany             Toronto             8350                   1  
  Germany             Austin               10000                  1  
  Germany             Toronto             9100                   1  
  Germany             Austin               10400                  2  
  Germany             Austin               10250                  2  
  Germany            Toronto              9350                   2 
　　
第一次执行结果：  
          第一个select语句产生的结果为  
          path：        origin                destination                 distance               stops  
                           Germany               New   York                  8000                   0  
                           Germany               Chicago                     8700                   0  
          第二个select语句执行的结果为      
          path:         origin                destination                 distance               stops  
                        Germany               Hunston                     8000+2100             1  
                        Germany               Chicago                     8000+950               1  
                        Germany               Toronto                     8000+350               1  
                        Germany               Austin                      8700+1300              1  
                        Germany               Toronto                     8700+400               1  
          union   后的结果为：  
          path：        origin                destination                 distance               stops  
                        Germany               New   York                  8000                      0  
                        Germany               Chicago                     8700                       0  
                        Germany               Hunston                     8000+2100            1  
                        Germany               Chicago                     8000+950               1  
                        Germany               Toronto                     8000+350               1  
                        Germany               Austin                      8700+1300               1  
                        Germany               Toronto                     8700+400               1  
   
  第二次递归调用结果：（path发生了改变，重新执行第二个select子句）  
          path：        origin                destination                 distance               stops  
                        Germany               New   York                  8000                        0  
                        Germany               Chicago                     8700                         0  
                        Germany               Hunston                     8000+2100              1  
                        Germany               Chicago                     8000+950                 1  
                        Germany               Toronto                     8000+350                  1  
                        Germany               Austin                      8700+1300                 1  
                        Germany               Toronto                     8700+400                  1  
                        Germany               Austin                      8700+2100+300          2  
                        Germany               Austin                      8000+950+1300          2  
                        Germany               Toronto                     8000+950+400          2