トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-24 パスカルの三角形

SQLパズル

パスカルの三角形を出力する。

出力結果
パスカルの三角形
-----------------------------------
                1
               1 1
              1 2 1
             1 3 3 1
            1 4 6 4 1
          1 5 10 10 5 1
        1 6 15 20 15 6 1
       1 7 21 35 35 21 7 1
     1 8 28 56 70 56 28 8 1
   1 9 36 84 126 126 84 36 9 1
1 10 45 120 210 252 210 120 45 10 1

こちらを参考にさせていただきました(英語)


SQL

--■■■PL/SQLを使う方法■■■
declare
    willOut varchar2(200);

    function comb(hikiN in pls_Integer,hikiR in pls_Integer) return pls_Integer is
        willReturn pls_Integer := 1;
    begin
        if hikiN = 0 or hikiR = 0 then
            return 1;
        end if;
        for N in hikiN-hikiR+1..hikiN Loop
            willReturn := willReturn * N;
        end Loop;
        for R in 1..hikiR Loop
            willReturn := willReturn / R;
        end Loop;
        return willReturn;
    end;
begin
    for N in 0..10 Loop
        for R in 0..N Loop
            willOut := willOut || to_char(comb(N,R)) || ',';
        end Loop;
        DBMS_Output.Put_Line(willOut);
        willOut := null;
    end Loop;
end;
/

--■■■with句を多用する方法■■■
col パスカルの三角形 for a40

with Renban as (
select RowNum-1 as Val
from dual connect by Level <= 11),
WorkView as (
select Ra.Val as n,Rb.Val as r
from Renban Ra,Renban Rb
where Ra.Val >= Rb.Val),
RowList as (
select n,r,
case when r=0 then 1
else (select round(exp(sum(Ln(a.N-b.R+1)))) from WorkView b
      where b.N=a.N
        and b.R > 0
        and b.R<=a.R) end as NPR,
case when r=0 then 1
else (select round(exp(sum(Ln(b.R)))) from WorkView b
       where b.N=a.N
         and b.R > 0
         and b.R<=a.R) end as "R!"
from WorkView a),
PascalTriangle as (
select N,SubStr(sys_connect_by_path(Val,' '),2) as Pascal
 from (select N,R,NPR / "R!" as Val from RowList)
where Connect_by_IsLeaf = 1
start with R=0
connect by prior N=N
       and prior R=R-1)
select Lpad(' ',(MaxLength-Length(Pascal))/2-1) || Pascal as パスカルの三角形
from PascalTriangle,(select max(Length(Pascal)) as MaxLength from PascalTriangle);


解説

対数を使って、掛け算を足し算に変形して、順列組み合わせや階乗を取得してます。

パスカルの三角形の資料