DB2 递归SQL写法

来源：互联网发布：成都巴西柔术学院知乎编辑：程序博客网时间：2024/05/17 06:20

WITH temptab(pro_komcode,

pro_komcode_o,

pro_sup_code) AS
( SELECT root.pro_komcode,

root.pro_komcode_o,

               root.pro_sup_code

        FROM product root

        WHERE pro_komcode = :as_pro_komcode
     UNION ALL
        SELECT sub.pro_komcode,

sub.pro_komcode_o,

sub.pro_sup_code
FROM product sub,

                  temptab super
            WHERE sub.pro_komcode = super.pro_komcode_o AND sub.pro_komcode_O<>:as_pro_komcode
   ),

temptab1(pro_komcode,

pro_komcode_o,

pro_sup_code) AS
( SELECT root.pro_komcode,

root.pro_komcode_o,

       root.pro_sup_code
      FROM product root
      WHERE pro_komcode_o = :as_pro_komcode
     UNION ALL
      SELECT sub.pro_komcode,

sub.pro_komcode_o,

sub.pro_sup_code
FROM product sub,

         temptab1 super
      WHERE sub.pro_komcode_O = super.pro_komcode AND sub.pro_komcode<>:as_pro_komcode
   )
  select pro_komcode,

pro_komcode_o,

     pro_sup_code
  from temptab
  UNION
  select pro_komcode,

pro_komcode_o,

pro_sup_code
from temptab1