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,
  (
  Select 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
) 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,
  (
  Select 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
) 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