lunes, 10 de mayo de 2010

JUGANDO CON LAS MATES (NÚMEROS PRIMOS)


Un pequeño divertimento : generar una tabla de números primos (de 0 a 1000) usando la tabla auxiliar Nums (con un único campo, entero, indexado -sin duplicados-, de nombre Num, con valores de 1 a 1000).

Select Nums.Num * T.Num
From Nums, Nums As T
Where Nums.Num * T.Num Between 1 And 1000
Group By Nums.Num * T.Num
Having Count(Nums.Num * T.Num)=2;

... y si queremos prescindir de la tabla Nums :

SELECT R1.Num * R2.Num
FROM
(
Select (T2.Num*10)+Nums.Num+1 AS Num
From
(
Select (T1.Num*10)+Nums.Num As Num
From
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) As Nums,
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) AS T1
) AS T2, Nums
) AS R1,
(
Select (T2.Num*10)+Nums.Num+1 AS Num
From
(
Select (T1.Num*10)+Nums.Num As Num
From
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) AS Nums,
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) AS T1
) AS T2, Nums
) AS R2
WHERE R1.Num * R2.Num Between 1 And 1000
GROUP BY R1.Num * R2.Num
HAVING Count(R1.Num * R2.Num)=2

raipon. Terrassa a 10/05/2010.
 

Contactar