如何使用Oracle SQL编写阿姆斯特朗数判断查询？

阿姆斯特朗数（也叫自恋数）的定义是：一个n位数字，其每个位上的数字的n次幂之和等于它本身（例如153 = 1³ + 5³ + 3³）。以下是纯Oracle SQL的实现方案：

1. 单个数字的判断查询 #

直接替换示例中的153为你需要判断的数字即可：

WITH num_details AS (
    SELECT 
        153 AS original_num,
        LENGTH(TO_CHAR(153)) AS digit_count,
        REGEXP_SUBSTR(TO_CHAR(153), '\d', 1, LEVEL) AS single_digit
    FROM dual
    CONNECT BY LEVEL <= LENGTH(TO_CHAR(153))
)
SELECT 
    original_num,
    CASE 
        WHEN SUM(POWER(TO_NUMBER(single_digit), digit_count)) = original_num 
        THEN '是阿姆斯特朗数' 
        ELSE '不是阿姆斯特朗数' 
    END AS is_armstrong
FROM num_details
GROUP BY original_num, digit_count;

逻辑说明 #

• num_details临时数据集：拆分目标数字的每一位，同时记录原数字和它的位数
• 聚合计算每一位数字的digit_count次幂之和，与原数字对比后返回判断结果

2. 通用绑定变量版（方便动态输入） #

使用绑定变量:input_num，可以在执行时动态传入要判断的数字：

WITH num_details AS (
    SELECT 
        :input_num AS original_num,
        LENGTH(TO_CHAR(:input_num)) AS digit_count,
        REGEXP_SUBSTR(TO_CHAR(:input_num), '\d', 1, LEVEL) AS single_digit
    FROM dual
    CONNECT BY LEVEL <= LENGTH(TO_CHAR(:input_num))
)
SELECT 
    original_num,
    CASE 
        WHEN SUM(POWER(TO_NUMBER(single_digit), digit_count)) = original_num 
        THEN '是阿姆斯特朗数' 
        ELSE '不是阿姆斯特朗数' 
    END AS is_armstrong
FROM num_details
GROUP BY original_num, digit_count;

3. 查询指定范围内的所有阿姆斯特朗数 #

以查询1到10000之间的阿姆斯特朗数为例：

WITH numbers AS (
    SELECT LEVEL AS num
    FROM dual
    CONNECT BY LEVEL <= 10000
),
num_details AS (
    SELECT 
        num,
        LENGTH(TO_CHAR(num)) AS digit_count,
        REGEXP_SUBSTR(TO_CHAR(num), '\d', 1, LEVEL) AS single_digit
    FROM numbers
    CONNECT BY LEVEL <= LENGTH(TO_CHAR(num))
        AND PRIOR num = num
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免层级循环
)
SELECT DISTINCT num AS armstrong_number
FROM num_details
GROUP BY num, digit_count
HAVING SUM(POWER(TO_NUMBER(single_digit), digit_count)) = num
ORDER BY num;

注意点 #

• PRIOR SYS_GUID() IS NOT NULL用于防止CONNECT BY时出现循环，确保每个数字的拆分层级独立

内容的提问来源于stack exchange，提问作者Tural Hüseynov