今天给大家介绍一个 SQL 技巧：查找 100 以内的所有质数。

文章描述的方法适用于 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 等数据库。

质数又称素数。如果一个大于 1 的自然数，除了 1 和它自身外，不能被其他自然数整除就叫做质数。

根据质数的定义，对于一个自然数 N，我们可以检查它是否能够被任何从 2 到 N 的平方根加 1 的自然数整除。如果可以被整除，就不是质数；否则是质数。因此，我们可以使用以下查询返回 100 以内的所有质数：

-- MySQL、PostgreSQL、SQLite
WITH RECURSIVE t(n) AS (
SELECT 2
UNION ALL 
SELECT n+1 FROM t WHERE n<100
)
SELECT t1.n
FROM t t1
WHERE t1.n = 2
OR NOT EXISTS (
SELECT 1 
FROM t t2 
WHERE t2.n BETWEEN 2 AND sqrt(t1.n)+1
AND t1.n%t2.n = 0
);

n |
--+
2|
3|
5|
7|
11|
13|
17|
19|
23|
29|
31|
37|
41|
43|
47|
53|
59|
61|
67|
71|
73|
79|
83|
89|
97|

首先，我们使用一个 WITH RECURSIVE 语句构造了一个从 2 到 100 的自然数序列。关于通用表表达式（WITH 子句）的详细介绍，可以参考这篇文章。

然后，我们使用了一个 NOT EXISTS 子查询，返回了满足条件的所有质数。数字 2 属于质数，所以我们直接返回了该数字。

如果使用 Oracle 或者 Microsoft SQL Server 数据库，需要对以上语句进行略微调整：

-- Oracle
WITH t(n) AS (
SELECT 2 FROM dual
UNION ALL 
SELECT n+1 FROM t WHERE n<100
)
SELECT t1.n
FROM t t1
WHERE t1.n = 2
OR NOT EXISTS (
SELECT 1 
FROM t t2 
WHERE t2.n BETWEEN 2 AND sqrt(t1.n)+1
AND MOD(t1.n, t2.n) = 0
);

-- Microsoft SQL Server
WITH t(n) AS (
SELECT 2
UNION ALL 
SELECT n+1 FROM t WHERE n<100
)
SELECT t1.n
FROM t t1
WHERE t1.n = 2
OR NOT EXISTS (
SELECT 1 
FROM t t2 
WHERE t2.n BETWEEN 2 AND sqrt(t1.n)+1
AND t1.n%t2.n = 0
);

对于 Oracle，我们需要利用 dual 表创建数字序列，WITH 子句不需要关键字 RECURSIVE，MOD 函数用于求余运算。

对于 Microsoft SQL Server，WITH 子句不需要关键字 RECURSIVE。

除了以上方法之外，我们也可以换一个思路。对于一个自然数 N，只有 1 和它自己相乘等于 N。因此，以下查询也可以实现相同的结果：

-- MySQL、PostgreSQL、SQLite
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL 
SELECT n+1 FROM t WHERE n<100
)
SELECT t1.n*t2.n
FROM t t1, t t2
WHERE t1.n <= t2.n AND t1.n*t2.n > 1 AND t1.n*t2.n <= 100
GROUP BY t1.n*t2.n
HAVING count(*)=1
ORDER BY 1;

-- Oracle
WITH t(n) AS (
SELECT 1 FROM dual
UNION ALL 
SELECT n+1 FROM t WHERE n<100
)
SELECT t1.n*t2.n
FROM t t1, t t2
WHERE t1.n <= t2.n AND t1.n*t2.n > 1 AND t1.n*t2.n <= 100
GROUP BY t1.n*t2.n
HAVING count(*)=1
ORDER BY 1;

-- Microsoft SQL Server
WITH t(n) AS (
SELECT 1
UNION ALL 
SELECT n+1 FROM t WHERE n<100
)
SELECT t1.n*t2.n
FROM t t1, t t2
WHERE t1.n <= t2.n AND t1.n*t2.n > 1 AND t1.n*t2.n <= 100
GROUP BY t1.n*t2.n
HAVING count(*)=1
ORDER BY 1;

首先，我们使用一个 WITH RECURSIVE 语句构造了一个从 1 到 100 的自然数序列。

然后，在下面的语句中通过自连接查询计算小于等于 100 的数字，有多少种乘法的结果等于该数字。如果乘法的方式只有一种，说明该数字是一个质数。

这两种语句都能实现质数的查找，你喜欢哪一种呢？