Есть ли способ выбрать числа (целые числа), которые включены между двумя числами с SQL в Oracle; Я не хочу создавать PL/SQL-процедуру или функцию.
Например, мне нужно получить числа от 3 до 10. Результатом будут значения 3,4,5,6,7,8,9,10.
спасибо.
Есть ли способ выбрать числа (целые числа), которые включены между двумя числами с SQL в Oracle; Я не хочу создавать PL/SQL-процедуру или функцию.
Например, мне нужно получить числа от 3 до 10. Результатом будут значения 3,4,5,6,7,8,9,10.
спасибо.
Этот трюк с таблицей Oracle DUAL также работает:
SQL> select n from
2 ( select rownum n from dual connect by level <= 10)
3 where n >= 3;
N
----------
3
4
5
6
7
8
9
10
Первое, что я делаю, создавая новую базу данных, - это создание и заполнение базовых таблиц.
Один из них представляет собой список всех целых чисел между -N и N, а другой - список дат 5 лет в прошлом через 10 лет в будущем (запланированное задание может продолжать создавать их по мере необходимости, в будущем) и последним это список всех часов в течение дня. Например, inetgers:
create table numbers (n integer primary key);
insert into numbers values (0);
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
insert into numbers select n+128 from numbers; commit;
insert into numbers select n+256 from numbers; commit;
insert into numbers select n+512 from numbers; commit;
insert into numbers select n+1024 from numbers; commit;
insert into numbers select n+2048 from numbers; commit;
insert into numbers select n+4096 from numbers; commit;
insert into numbers select n+8192 from numbers; commit;
insert into numbers select -n from numbers where n > 0; commit;
Это для DB2/z, у которого есть автоматический запуск транзакции, поэтому он, кажется, совершает голые фиксации.
Да, он занимает (минимальное) пространство, но делает запросы намного проще писать, просто выбирая значения из этих таблиц. Он также очень портативен практически в любой СУБД на базе SQL.
Ваш конкретный запрос был бы простым:
select n from numbers where n >=3 and n <= 10;
Часовые цифры и диапазоны дат весьма полезны для тех приложений, в которых мы работаем. Это позволяет нам создавать нулевые записи для тех часов дня (или дат), у которых нет реальных данных, чтобы вместо (где нет данных во второй месяц):
Date | Quantity
-----------+---------
2009-01-01 | 7
2009-01-03 | 27
2009-01-04 | 6
мы можем вместо этого получить:
Date | Quantity
-----------+---------
2009-01-01 | 7
2009-01-02 | 0
2009-01-03 | 27
2009-01-04 | 6
SQL> var N_BEGIN number
SQL> var N_END number
SQL> exec :N_BEGIN := 3; :N_END := 10
PL/SQL procedure successfully completed.
SQL> select :N_BEGIN + level - 1 n
2 from dual
3 connect by level <= :N_END - :N_BEGIN + 1
4 /
N
----------
3
4
5
6
7
8
9
10
8 rows selected.
Это использует тот же трюк, что и Тони. Обратите внимание, что когда вы используете SQL * Plus 9, вы должны сделать этот запрос встроенным, как показал Тони. В SQL * Plus 10 или выше это достаточно.
С уважением, Роб.
Для этого вы можете использовать предложение MODEL.
SELECT c1 from dual
MODEL DIMENSION BY (1 as rn) MEASURES (1 as c1)
RULES ITERATE (7)
(c1[ITERATION_NUMBER]=ITERATION_NUMBER+7)
Или вы можете использовать Между
Select Column1 from dummy_table where Column2 Between 3 and 10
Гэри, чтобы показать результат, который он объяснил, запрос модели будет:
SELECT c1
FROM DUAL
МОДЕЛЬНОЕ ИЗМЕРЕНИЕ BY (1 as rn)
МЕРЫ (1 как c1)
ПРАВИЛА ИТЕРАЦИИ (8)
(С1 [ITERATION_NUMBER] = ITERATION_NUMBER + 3)
ORDER BY rn
;)
Я всегда использую:
ВЫБРАТЬ (УРОВЕНЬ - 1) + 3 в результате FROM Dual CONNECT BY Level <= 8
Где 3 - начальное число, а 8 - число "итераций".
этот запрос одной строки поможет вам,
select level lvl from dual where level<:upperbound and
level >:lowerbound connect by level<:limt
В вашем случае:
select level lvl from dual where level<10 and level >3 connect by level<11
сообщите мне, есть ли какие-либо разъяснения.
Это последнее дополнение. Но решение кажется более элегантным и более простым в использовании.
Он использует конвейерную функцию, которая должна быть установлена один раз:
CREATE TYPE number_row_type AS OBJECT
(
num NUMBER
);
CREATE TYPE number_set_type AS TABLE OF number_row_type;
CREATE OR REPLACE FUNCTION number_range(p_start IN PLS_INTEGER, p_end IN PLS_INTEGER)
RETURN number_set_type
PIPELINED
IS
out_rec number_row_type := number_row_type(NULL);
BEGIN
FOR i IN p_start .. p_end LOOP
out_rec.num := i;
pipe row(out_rec);
END LOOP;
END number_range;
/
Затем вы можете использовать его следующим образом:
select * from table(number_range(1, 10));
NUM
---
1
2
3
4
5
6
7
8
9
10
Решение является специфичным для Oracle.
Я просто сделал функцию с табличной оценкой, чтобы сделать это на SQL-сервере, если кому-то это интересно, это работает безупречно.
CREATE FUNCTION [dbo].[NumbersBetween]
(
@StartN int,
@EndN int
)
RETURNS
@NumberList table
(
Number int
)
AS
BEGIN
WHILE @StartN <= @EndN
BEGIN
insert into @NumberList
VALUES (@StartN)
set @StartN = @StartN + 1
END
Return
END
GO
Если вы запустите запрос: "выберите * из dbo.NumbersBetween(1,5)" (без кавычек, конечно), результат будет
Number
-------
1
2
3
4
5
Один из способов генерации чисел из диапазона - использовать XMLTABLE('start to end')
:
SELECT column_value
FROM XMLTABLE('3 to 10');
Я хочу поделиться полезным запросом, который преобразует строку запятой и "-" разделенный список чисел в эквивалентный расширенный список чисел:
Пример, который преобразует "1,2,3,50-60" в
1 2 3 50 51 ... 60
select distinct * from (SELECT (LEVEL - 1) + mini as result FROM (select REGEXP_SUBSTR (value, '[^-]+', 1, 1)mini ,nvl(REGEXP_SUBSTR (value, '[^-]+', 1, 2),0) maxi from (select REGEXP_SUBSTR (value, '[^,]+', 1, level) as value from (select '1,2,3,50-60' value from dual) connect by level <= length(regexp_replace(value,'[^,]*'))+1)) CONNECT BY Level <= (maxi-mini+1)) order by 1 asc;
Вы можете использовать его как представление и параметризовать строку "1,2,3,50-60"
create table numbers (value number);
declare
x number;
begin
for x in 7 .. 25
loop
insert into numbers values (x);
end loop;
end;
/