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
Ramon Poch. Terrassa a 10/05/2010.