SQL 趣味题——计算 10 的阶乘

SQL> WITH equation AS
  2   (SELECT listagg(LEVEL, '*') within GROUP(ORDER BY LEVEL) e
  3      FROM dual
  4    CONNECT BY LEVEL <= 10)
  5  SELECT e, dbms_aw.eval_number(e) f FROM equation;


E                               F
---------------------- ----------
1*2*3*4*5*6*7*8*9*10      3628800

SQL> SELECT power(10, SUM(log(10, LEVEL))) f FROM dual CONNECT BY LEVEL <= 10;


         F
----------
   3628800

SQL> WITH factorial(n, f) AS
  2   (SELECT 1 n, 1 f
  3      FROM dual
  4    UNION ALL
  5    SELECT n + 1, f * (n + 1)
  6      FROM factorial
  7     WHERE n < 10)
  8  SELECT f FROM factorial WHERE n = 10;


         F
----------
   3628800

SQL> WITH factorial AS
  2   (SELECT n, f
  3    FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 10)
  4    MODEL RETURN UPDATED ROWS
  5    DIMENSION BY(n) MEASURES(0 f)
  6    RULES ITERATE(10)
  7    (f[n] ORDER BY n = presentv(f[cv(n) - 1], f[cv(n) - 1], 1) * cv(n)))
  8  SELECT f FROM factorial WHERE n = 10;


         F
----------
   3628800