トップページに戻る    次のJavaアルゴリズムパズルへ    前のJavaアルゴリズムパズルへ    PL/SQL版

2-6 Oracleの階層問い合わせを模倣(nocycle対応)

Javaアルゴリズムパズル

OracleSQLパズル 10-287 木の高さ制限による枝切り

select substr(sys_connect_by_path(FromP,','),2) || ',' || ToP as PathList
  from RootListT
where connect_by_isleaf = 1
  and ToP = '07'
start with FromP = '01'
connect by nocycle prior ToP = FromP
               and prior ToP != '07'
               and Level <= 3;

上記の、Oracleの階層問い合わせを、スタックを使った深さ優先探索で実装する。


ソース

public final class JavaHire2{
    final static int LevelLimit = 2;

    //RowData
    class RowDataDef {
        String FromP;
        String ToP;
        RowDataDef(String FromP,String ToP){
            this.FromP = FromP;
            this.ToP   = ToP;
        }
    }

    //スタックで使うデータ
    class StackDataDef {
        RowDataDef RowData;
        RowDataDef RootData; //is_root用
        StackDataDef(RowDataDef RowData){
            this.RowData = RowData;
        }
        int LV = 1; //Level用
        int isLeaf=1; //isleaf用
        String Path; //sys_connect_by_path用
        String RowIDList; //RowIDList
        int isCycle=0; //isCycle用
    };

    //スタック
    class StackClass {
        int stackP;
        StackDataDef stackData[];

        StackClass(){
            stackP = 0;
            stackData = new StackDataDef[99999];
        }

        private void push(StackDataDef pushData){
            stackData[stackP++] = pushData;
        }
        private StackDataDef pop(){
            return stackData[--stackP];
        }
        boolean isEmpty(){ return stackP == 0; }
    }

    public static void main(String[] args) {
        JavaHire2 mi = new JavaHire2();
        mi.main();
    }

    void main() {
        RowDataDef RowData[] = new RowDataDef[148];
        RowData[0] = new RowDataDef("01","02");
        RowData[1] = new RowDataDef("01","03");
        RowData[2] = new RowDataDef("01","04");
        RowData[3] = new RowDataDef("01","05");
        RowData[4] = new RowDataDef("01","06");
        RowData[5] = new RowDataDef("01","07");
        RowData[6] = new RowDataDef("01","08");
        RowData[7] = new RowDataDef("01","09");
        RowData[8] = new RowDataDef("02","01");
        RowData[9] = new RowDataDef("02","03");
        RowData[10] = new RowDataDef("02","04");
        RowData[11] = new RowDataDef("02","05");
        RowData[12] = new RowDataDef("02","06");
        RowData[13] = new RowDataDef("02","08");
        RowData[14] = new RowDataDef("02","09");
        RowData[15] = new RowDataDef("03","01");
        RowData[16] = new RowDataDef("03","02");
        RowData[17] = new RowDataDef("03","04");
        RowData[18] = new RowDataDef("03","05");
        RowData[19] = new RowDataDef("03","06");
        RowData[20] = new RowDataDef("03","07");
        RowData[21] = new RowDataDef("03","08");
        RowData[22] = new RowDataDef("03","09");
        RowData[23] = new RowDataDef("04","01");
        RowData[24] = new RowDataDef("04","02");
        RowData[25] = new RowDataDef("04","03");
        RowData[26] = new RowDataDef("04","05");
        RowData[27] = new RowDataDef("04","06");
        RowData[28] = new RowDataDef("04","07");
        RowData[29] = new RowDataDef("04","08");
        RowData[30] = new RowDataDef("04","09");
        RowData[31] = new RowDataDef("04","0G");
        RowData[32] = new RowDataDef("05","01");
        RowData[33] = new RowDataDef("05","02");
        RowData[34] = new RowDataDef("05","03");
        RowData[35] = new RowDataDef("05","04");
        RowData[36] = new RowDataDef("05","07");
        RowData[37] = new RowDataDef("05","09");
        RowData[38] = new RowDataDef("05","0A");
        RowData[39] = new RowDataDef("05","0B");
        RowData[40] = new RowDataDef("05","0C");
        RowData[41] = new RowDataDef("05","0D");
        RowData[42] = new RowDataDef("05","0E");
        RowData[43] = new RowDataDef("05","0F");
        RowData[44] = new RowDataDef("06","01");
        RowData[45] = new RowDataDef("06","02");
        RowData[46] = new RowDataDef("06","03");
        RowData[47] = new RowDataDef("06","04");
        RowData[48] = new RowDataDef("06","0A");
        RowData[49] = new RowDataDef("06","0B");
        RowData[50] = new RowDataDef("06","0C");
        RowData[51] = new RowDataDef("06","0D");
        RowData[52] = new RowDataDef("06","0E");
        RowData[53] = new RowDataDef("06","0F");
        RowData[54] = new RowDataDef("07","01");
        RowData[55] = new RowDataDef("07","03");
        RowData[56] = new RowDataDef("07","04");
        RowData[57] = new RowDataDef("07","05");
        RowData[58] = new RowDataDef("07","09");
        RowData[59] = new RowDataDef("07","0A");
        RowData[60] = new RowDataDef("07","0B");
        RowData[61] = new RowDataDef("07","0D");
        RowData[62] = new RowDataDef("07","0E");
        RowData[63] = new RowDataDef("07","0F");
        RowData[64] = new RowDataDef("08","01");
        RowData[65] = new RowDataDef("08","02");
        RowData[66] = new RowDataDef("08","03");
        RowData[67] = new RowDataDef("08","04");
        RowData[68] = new RowDataDef("08","0A");
        RowData[69] = new RowDataDef("08","0B");
        RowData[70] = new RowDataDef("08","0C");
        RowData[71] = new RowDataDef("08","0D");
        RowData[72] = new RowDataDef("08","0E");
        RowData[73] = new RowDataDef("08","0F");
        RowData[74] = new RowDataDef("09","01");
        RowData[75] = new RowDataDef("09","02");
        RowData[76] = new RowDataDef("09","03");
        RowData[77] = new RowDataDef("09","04");
        RowData[78] = new RowDataDef("09","05");
        RowData[79] = new RowDataDef("09","07");
        RowData[80] = new RowDataDef("09","0A");
        RowData[81] = new RowDataDef("09","0B");
        RowData[82] = new RowDataDef("09","0C");
        RowData[83] = new RowDataDef("09","0D");
        RowData[84] = new RowDataDef("09","0E");
        RowData[85] = new RowDataDef("09","0F");
        RowData[86] = new RowDataDef("0A","05");
        RowData[87] = new RowDataDef("0A","06");
        RowData[88] = new RowDataDef("0A","07");
        RowData[89] = new RowDataDef("0A","08");
        RowData[90] = new RowDataDef("0A","09");
        RowData[91] = new RowDataDef("0A","0B");
        RowData[92] = new RowDataDef("0A","0C");
        RowData[93] = new RowDataDef("0A","0D");
        RowData[94] = new RowDataDef("0A","0E");
        RowData[95] = new RowDataDef("0A","0F");
        RowData[96] = new RowDataDef("0B","05");
        RowData[97] = new RowDataDef("0B","06");
        RowData[98] = new RowDataDef("0B","07");
        RowData[99] = new RowDataDef("0B","08");
        RowData[100] = new RowDataDef("0B","09");
        RowData[101] = new RowDataDef("0B","0A");
        RowData[102] = new RowDataDef("0B","0C");
        RowData[103] = new RowDataDef("0B","0D");
        RowData[104] = new RowDataDef("0B","0F");
        RowData[105] = new RowDataDef("0C","05");
        RowData[106] = new RowDataDef("0C","06");
        RowData[107] = new RowDataDef("0C","08");
        RowData[108] = new RowDataDef("0C","09");
        RowData[109] = new RowDataDef("0C","0A");
        RowData[110] = new RowDataDef("0C","0B");
        RowData[111] = new RowDataDef("0C","0D");
        RowData[112] = new RowDataDef("0C","0E");
        RowData[113] = new RowDataDef("0C","0F");
        RowData[114] = new RowDataDef("0D","05");
        RowData[115] = new RowDataDef("0D","06");
        RowData[116] = new RowDataDef("0D","07");
        RowData[117] = new RowDataDef("0D","08");
        RowData[118] = new RowDataDef("0D","09");
        RowData[119] = new RowDataDef("0D","0A");
        RowData[120] = new RowDataDef("0D","0B");
        RowData[121] = new RowDataDef("0D","0C");
        RowData[122] = new RowDataDef("0D","0E");
        RowData[123] = new RowDataDef("0D","0F");
        RowData[124] = new RowDataDef("0E","05");
        RowData[125] = new RowDataDef("0E","06");
        RowData[126] = new RowDataDef("0E","07");
        RowData[127] = new RowDataDef("0E","08");
        RowData[128] = new RowDataDef("0E","09");
        RowData[129] = new RowDataDef("0E","0A");
        RowData[130] = new RowDataDef("0E","0C");
        RowData[131] = new RowDataDef("0E","0D");
        RowData[132] = new RowDataDef("0E","0F");
        RowData[133] = new RowDataDef("0F","05");
        RowData[134] = new RowDataDef("0F","06");
        RowData[135] = new RowDataDef("0F","07");
        RowData[136] = new RowDataDef("0F","08");
        RowData[137] = new RowDataDef("0F","09");
        RowData[138] = new RowDataDef("0F","0A");
        RowData[139] = new RowDataDef("0F","0B");
        RowData[140] = new RowDataDef("0F","0C");
        RowData[141] = new RowDataDef("0F","0D");
        RowData[142] = new RowDataDef("0F","0F");
        RowData[143] = new RowDataDef("0G","04");
        RowData[144] = new RowDataDef("0G","0F");
        RowData[145] = new RowDataDef("0G","0H");
        RowData[146] = new RowDataDef("0H","0G");
        RowData[147] = new RowDataDef("0H","01");
        int UPB = RowData.length-1;

        StackClass st = new StackClass();

        for (int i=0;i<=UPB;i++){
            if (RowData[i].FromP.equals("01")){ //Start With句
                StackDataDef willPush = new StackDataDef(RowData[i]);
                willPush.Path = RowData[i].FromP;
                willPush.RowIDList = RowData[i].FromP+RowData[i].ToP + ",";
                st.push(willPush);
            }
        }

        int LoopChk = 0;
        while (st.isEmpty() == false){
            StackDataDef priInfo = st.pop();

            for (int i = 0;i<=UPB;i++){
                if (priInfo.RowData.ToP.equals(RowData[i].FromP)
                 && priInfo.LV <= LevelLimit
                 && !priInfo.RowData.ToP.equals("07")){ //connect by句

                     String Work = RowData[i].FromP + RowData[i].ToP;

                     //未訪問ノードならpush
                     if (priInfo.RowIDList.indexOf(Work) == -1){
                        StackDataDef willPush = new StackDataDef(new RowDataDef(RowData[i].FromP,RowData[i].ToP));
                        willPush.Path = priInfo.Path + "-" + RowData[i].FromP;
                        willPush.RowIDList = priInfo.RowIDList + Work + ",";
                        willPush.LV = priInfo.LV+1;
                        st.push(willPush);
                     }
                }
            }

            //合格経路なら表示
            if (priInfo.RowData.ToP.equals("07")) System.out.println(priInfo.Path + "-07");

            if (++LoopChk == 99999999){
                System.out.println("There is a Loop."); break;
            }
        }
    }
}


実行結果

01-09-0F-07
01-09-0E-07
01-09-0D-07
01-09-0B-07
01-09-0A-07
01-09-07
01-09-05-07
01-09-04-07
01-09-03-07
01-09-01-07
01-08-0F-07
01-08-0E-07
01-08-0D-07
01-08-0B-07
01-08-0A-07
01-08-04-07
01-08-03-07
01-08-01-07
01-07
01-06-0F-07
01-06-0E-07
01-06-0D-07
01-06-0B-07
01-06-0A-07
01-06-04-07
01-06-03-07
01-06-01-07
01-05-0F-07
01-05-0E-07
01-05-0D-07
01-05-0B-07
01-05-0A-07
01-05-09-07
01-05-07
01-05-04-07
01-05-03-07
01-05-01-07
01-04-09-07
01-04-07
01-04-05-07
01-04-03-07
01-04-01-07
01-03-09-07
01-03-07
01-03-05-07
01-03-04-07
01-03-01-07
01-02-09-07
01-02-05-07
01-02-04-07
01-02-03-07
01-02-01-07