http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=78&page=1&position=1글쓴이 : 김홍선


테이블 t 가 아래와 같이 주어질때,
row로 주어진 수들의 최대공약수(gcd, greatest common divisor)와 최소공배수(lcm, least common multiple)를 구해보자.
row의 수를 변화시키면서 속도를 고려하여 쿼리를 만들어 보자.

WITH t AS
     (SELECT 15 num
        FROM DUAL
      UNION
      SELECT 9
        FROM DUAL
      UNION
      SELECT 42
        FROM DUAL)


-- 최대공약수
SELECT level# gcd
  FROM (SELECT     min_num / LEVEL level#
              FROM (SELECT MIN (num) min_num
                      FROM t)
             WHERE TRUNC (min_num / LEVEL) = min_num / LEVEL
        CONNECT BY LEVEL <= min_num)
WHERE EXISTS (SELECT 1
                 FROM t
               HAVING SUM (num / level#) = SUM (TRUNC (num / level#)))
   AND ROWNUM = 1


-- 최소공배수

SELECT level# lcm
  FROM (SELECT     max_num * LEVEL level#
              FROM (SELECT MAX (num) max_num, EXP (SUM (LN (num))) prod
                      FROM t)
        CONNECT BY LEVEL <= prod)
WHERE EXISTS (SELECT 1
                 FROM t
               HAVING SUM (level# / num) = SUM (TRUNC (level# / num)))
   AND ROWNUM = 1