如何在oracle中找到20到30之间的数字和

Question

如何在oracle中使用select语句查找20到30之间的数字和

Answer 1

添加数字:

SELECT 20+21+22+23+24+25+26+27+28+29+30 AS total FROM DUAL

或者，计算总数:

SELECT (:range_start + :range_end)/2 * (:range_end - :range_start + 1) AS total FROM DUAL

，加上硬编码的值将是:

SELECT (20 + 30)/2 * (30 - 20 + 1) AS total FROM DUAL

或者，使用分层查询生成行并将它们相加:

SELECT SUM(20 + (LEVEL - 1)) AS total FROM DUAL CONNECT BY LEVEL <= 11

或者，使用递归查询生成行并对它们求和:

WITH data (value) AS (
SELECT 20 FROM DUAL
UNION ALL
SELECT value + 1 FROM data WHERE value < 30
)
SELECT SUM(value) AS total FROM data

或者，使用PL/SQL循环:

DECLARE
total NUMBER := 0;
BEGIN
FOR i IN 20 .. 30 LOOP
total := total + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(total);
END;
/

db<此处小提琴>

Answer 2

with one_to_twenty as (            
select 
rownum ints
from 
dual 
connect by rownum <= 20
)

select 
sum(ints)
from
one_to_twenty  
where 
ints > 10   

one_to_twenty生成一个数字范围，从10到20，子选择从10开始的值求和。