[SEQ] n 보다 작거나 같은 소수(prime number) 구하기

[원쿼리맨]

변수 n 이 주어졌을때,

n 보다 작거나 같은 소수(prime number)를 구하는 쿼리입니다.

SELECT level# prime
  FROM (SELECT     LEVEL level#
              FROM DUAL
             WHERE LEVEL > 1
        CONNECT BY LEVEL <= :n)
 WHERE NOT EXISTS (SELECT     1
                         FROM DUAL
                        WHERE MOD (level#, LEVEL) = 0 AND LEVEL > 1
                   CONNECT BY LEVEL <= level# / 2)

혹은,

SELECT   divd prime
    FROM (SELECT divd,
                 DECODE (TRUNC (divd / divr),
                         divd / divr, divd / divr,
                         0
                        ) sub
            FROM (SELECT     TRUNC (1 / 2 + SQRT (2 * LEVEL)) divd,
                               LEVEL
                             -   TRUNC ((SQRT (8 * LEVEL - 7) - 1) / 2)
                               * (TRUNC ((SQRT (8 * LEVEL - 7) - 1) / 2) + 1)
                               / 2 divr
                        FROM DUAL
                  CONNECT BY SQRT (2 * LEVEL + 1 / 4) - 1 / 2 <= :n)
           WHERE divr <= divd / 2)
GROUP BY divd
  HAVING SUM (sub) = MAX (sub)

속도는 위쪽이 훨씬 더 빠르겠다.

그리고 아래는 크기순으로 처음 n개의 소수를 구하는 쿼리가 되겠다.

SELECT level# prime
  FROM (SELECT     LEVEL level#
              FROM DUAL
             WHERE LEVEL > 1
        CONNECT BY LEVEL <= POWER (10, 125))
 WHERE NOT EXISTS (SELECT     1
                         FROM DUAL
                        WHERE MOD (level#, LEVEL) = 0 AND LEVEL > 1
                   CONNECT BY LEVEL <= level# / 2) AND ROWNUM <= :n