在SQL SERVER 2005中实现素数计算

80酷酷网    80kuku.com

  

  我将提出一个挑战,谁能用SQLSEERVER提出计算素数最好的方法,我用了一个新的特点CTE和某些TSQL实现,但均不理想,前者(CTE)有限制,而后者(TSQL)产生一百万个素数用了7分种你可以干的更好么?这儿是我的一些代码段落

  (TSQL实现)

set nocount on
declare prime table (prime int not null primary key)
--insert into prime values (2)
--insert into prime values (3)
--insert into prime values (5)
--insert into prime values (7)
--insert into prime values (11)
declare number int, pc int
set number = 13
set pc = 1
while pc < 1000000
begin
if not exists (select 1 from prime where number % prime = 0 and prime < sqrt(number) )
begin
insert into prime select number
set pc = pc +1
end
set number = number
+ case when number %2 = 1 then 2
when number %3 = 2 then 2
when number %5 = 4 then 2
when number %7 = 6 then 2
when number %11 = 10 then 2
else 1 end
end
select pc

  和

  (CTE实现)

with seq
as( select 13 number
union all
select s.number
+ case when s.number %2 = 1 then 2
when s.number %3 = 2 then 2
when s.number %5 = 4 then 2
when s.number %7 = 6 then 2
when s.number %11 = 10 then 2
else 1 end
from seq s
where number < 32767
)
, prime as (
select s.number
from seq s
where not exists ( select 1 from seq s2 where s2.number < s.number and (s.number) % s2.number = 0)
)
select *
from prime
option (MAXRECURSION 32767)


分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间
点击: