使用SQL查询打印素数 [英] Print Prime Numbers with SQL query
问题描述
我是StackOverflow的新手,并且对打印从2到1000的素数的查询感到困惑。
如果这是最有效的编码方式,则我使用以下查询需要输入。
I am new to StackOverflow and have got stuck with a query to print prime numbers from 2 to 1000. I have used the below query need input if this is the most efficient way to code it.
WITH NUM AS (
SELECT LEVEL N
FROM DUAL CONNECT BY LEVEL <= 1000
)
SELECT LISTAGG(B.N,'-') WITHIN GROUP(ORDER BY B.N) AS PRIMES
FROM (
SELECT N,
CASE WHEN EXISTS (
SELECT NULL
FROM NUM N_INNER
WHERE N_INNER .N > 1
AND N_INNER.N < NUM.N
AND MOD(NUM.N, N_INNER.N)=0
) THEN
'NO PRIME'
ELSE
'PRIME'
END IS_PRIME
FROM NUM
) B
WHERE B.IS_PRIME='PRIME'
AND B.N!=1;
我知道这个问题已经被问过多次了,我要求有更好的解决方案。需要更多有关如何将其与MySQL / MS SQL / PostgreSQL一起使用的信息。
I know this question has been asked multiple times and I am requesting better solution if any. More over need input on how this works with MySQL/MS SQL/PostgreSQL.
任何帮助都会使我的理解更好。
Any help will make my understanding better.
推荐答案
在PostgreSQL中最快打印质数最多为1000的查询可能是:
In PostgreSQL probably the most fastest query that prints prime numbers up to 1000 is:
SELECT regexp_split_to_table('2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,53,59,61,67,71,73,79,83,89,97,101,103,107,109,113,127,131,137,139,149,151,157,163,167,173,179,181,191,193,197,199,211,223,227,229,233,239,241,251,257,263,269,271,277,281,283,293,307,311,313,317,331,337,347,349,353,359,367,373,379,383,389,397,401,409,419,421,431,433,439,443,449,457,461,463,467,479,487,491,499,503,509,521,523,541,547,557,563,569,571,577,587,593,599,601,607,613,617,619,631,641,643,647,653,659,661,673,677,683,691,701,709,719,727,733,739,743,751,757,761,769,773,787,797,809,811,821,823,827,829,839,853,857,859,863,877,881,883,887,907,911,919,929,937,941,947,953,967,971,977,983,991,997',E',')::int
AS x
;
我的计算机只花了16毫秒。
It took only 16 ms on my computer.
- 注意:素数列表是从 https:复制的: //en.wikipedia.org/wiki/Prime_number
并粘贴到此长字符串中
如果您喜欢使用SQL,则可以这样做
If you prefer SQL, then this works
WITH x AS (
SELECT * FROM generate_series( 2, 1000 ) x
)
SELECT x.x
FROM x
WHERE NOT EXISTS (
SELECT 1 FROM x y
WHERE x.x > y.x AND x.x % y.x = 0
)
;
速度慢了两倍-31毫秒。
It's two times slower - 31 ms.
是Oracle的等效版本:
Ans an equivalent version for Oracle:
WITH x AS(
SELECT level+1 x
FROM dual
CONNECT BY LEVEL <= 999
)
SELECT x.x
FROM x
WHERE NOT EXISTS (
SELECT 1 FROM x y
WHERE x.x > y.x AND remainder( x.x, y.x) = 0
)
;
这篇关于使用SQL查询打印素数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!