我是StackOverflow新手,遇到了打印从2到1000的质数的查询问题。我使用了下面的查询需要输入,如果这是最有效的编码方式。
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一起工作的输入。
任何帮助都会让我更好地理解。
发布于 2016-05-15 16:57:33
最明显的改进是,您可以检查从1到n的平方根,而不是从1到n。
第二个主要优化是使用临时表来存储结果并首先检查它们。这样,您可以从1到n递增地迭代,并且只检查从1到n的平方根的已知素数(递归地执行此操作,直到您有了一个列表)。如果你这样做,你可能想要在一个函数中设置质数检测,然后用你的数列生成器做同样的事情。
然而,第二个意味着扩展SQL,所以我不知道这是否符合您的需求。
对于postgresql,我将使用generate_series
go生成数字列表。然后,我将创建一些函数,这些函数要么将素数列表存储在临时表中,要么将它们以有序数组的形式传入传出,然后像这样将它们耦合起来
发布于 2019-02-12 14:57:56
Oracle,在获取零件时没有inner:
with tmp(id)
as (
select level id from dual connect by level <= 100
) select t1.id from tmp t1
JOIN tmp t2
on MOD(t1.id, t2.id) = 0
group by t1.ID
having count(t1.id) = 2
order by t1.ID
;
发布于 2020-03-25 14:18:32
/* Below is my solution */
/* Step 1: Get all the numbers till 1000 */
with tempa as
(
select level as Num
from dual
connect by level<=1000
),
/* Step 2: Get the Numbers for finding out the factors */
tempb as
(
select a.NUm,b.Num as Num_1
from tempa a , tempa b
where b.Num<=a.Num
),
/*Step 3:If a number has exactly 2 factors, then it is a prime number */
tempc as
(
select Num, sum(case when mod(num,num_1)=0 then 1 end) as Factor_COunt
from tempb
group by Num
)
select listagg(Num,'&') within group (order by Num)
from tempc
where Factor_COunt=2
;
https://stackoverflow.com/questions/37234893
复制相似问题