sqlserver 输入水仙花数语句

在 SQL Server 中，可以使用以下语句查询 100~999 之间的水仙花数：

SELECT num
FROM (
   SELECT num, POWER(num%10, 3) + POWER((num/10)%10, 3) + POWER(num/100, 3) AS result
   FROM (
      SELECT 100 AS num
      UNION ALL
      SELECT num + 1
      FROM (
         SELECT TOP 899 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 100 AS num
         FROM master..spt_values a, master..spt_values b
      ) AS nums
   ) AS num_list
) AS result_list
WHERE num = result;

该语句使用了子查询和内置函数 `POWER`，可以逐一计算 100~999 中每个数是否为水仙花数，并将结果输出。

相关问题

sql语句输出所有的水仙花数

在 SQL 中可以通过以下语句输出所有的水仙花数：

SELECT num FROM (
    SELECT num, (num % 10) * (num % 10) * (num % 10) + 
                FLOOR(num / 10) % 10 * FLOOR(num / 10) % 10 * FLOOR(num / 10) % 10 + 
                FLOOR(num / 100) * FLOOR(num / 100) * FLOOR(num / 100) AS result
    FROM (
        SELECT num FROM (
            SELECT 100 * a + 10 * b + c AS num
            FROM (SELECT 0 AS digit UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
            CROSS JOIN (SELECT 0 AS digit UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
            CROSS JOIN (SELECT 0 AS digit UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
        ) AS numbers
    ) AS nums
) AS results
WHERE num = result

以上 SQL 语句的执行结果将会返回所有的水仙花数。

PL/SQL水仙花数

PL/SQL实现水仙花数的代码如下：

DECLARE
   num NUMBER;
   sum NUMBER := 0;
   temp NUMBER;
BEGIN
   FOR i IN 100..999 LOOP
      num := i;
      WHILE num > 0 LOOP
         temp := MOD(num, 10);
         sum := sum + temp * temp * temp;
         num := TRUNC(num / 10);
      END LOOP;
      IF sum = i THEN
         DBMS_OUTPUT.PUT_LINE(i || '是水仙花数');
      END IF;
      sum := 0;
   END LOOP;
END;