Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вывод последовательности чисел запросом  [new]
SQLap
Member [заблокирован]

Откуда:
Сообщений: 34063
Добрый день, уважаемые.

Вопрос у меня простой, прошу ногами не пинать.
Подскажите пожалуйста, как в MS SQL Server можно вывести последовательность натуральных чисел запросом (например от 1 до 10)? Я в MS SQL не бум-бум, в Oracle это можно сделать так:
select rownum from all_objects where rownum < 11

Просвятите пожалуйста, как это можно сделать в MS SQL
17 июл 09, 09:30    [7427295]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
SELECT TOP 10
  ROW_NUMBER() OVER (ORDER BY object_id)
FROM
  sys.objects
17 июл 09, 09:38    [7427318]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SELECT number FROM master.dbo.spt_values WHERE type='P' AND number BETWEEN 1 AND 10;
Но лучше один раз сделать свою постоянную таблицу с числами.
Это Вам здесь большинство посоветует.

Да, и можно, конечно, CTE
SQL2005/2008
WITH CTE(N) AS(SELECT 1 UNION ALL SELECT N+1 FROM CTE WHERE N<10)
SELECT N FROM CTE ORDER BY N;
Но это тот же цикл, вид сбоку!
17 июл 09, 09:41    [7427333]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
SQLap
Member [заблокирован]

Откуда:
Сообщений: 34063
pkarklin,

Большое Вам спасибо
17 июл 09, 09:44    [7427340]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
SQLap
Member [заблокирован]

Откуда:
Сообщений: 34063
iap,

И Вам спасибо, только непонятно, зачем делать таблицу с числами, если их можно просто выбрать пивотом? Ну это уже вопрос холивара наверное. Топик пожалуй можно закрывать
17 июл 09, 09:47    [7427354]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
SQLap
pkarklin,

Большое Вам спасибо


+1 за постоянную табличку с числами от 1 до необходимого Вам.
17 июл 09, 09:47    [7427358]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
SQLap
iap,

И Вам спасибо, только непонятно, зачем делать таблицу с числами, если их можно просто выбрать пивотом? Ну это уже вопрос холивара наверное. Топик пожалуй можно закрывать

холивары тут не при чем, это вопрос удобства и быстродействия. если такое вам требуется часто - сделайте ее один раз и пользуйтесь всю жизнь. вот такой запрос
SELECT TOP (N)
  ROW_NUMBER() OVER (ORDER BY (select 1))
FROM
  T -- любая таблица с достаточным числом строк

делает почти то же, но с перерасходом на чтения.
17 июл 09, 09:52    [7427385]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SQLap
их можно просто выбрать пивотом?
Пример не покажете?
17 июл 09, 09:56    [7427409]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
iljy
но с перерасходом на чтения.


С чего бы это чтений перерасход?! ;)

CREATE TABLE T1(col1 int Primary Key)
GO

INSERT T1 
SELECT TOP 10
  ROW_NUMBER() OVER (ORDER BY object_id)
FROM
  sys.objects
GO

SET STATISTICS IO ON
GO

SELECT TOP 5
  ROW_NUMBER() OVER (ORDER BY object_id)
FROM
  sys.objects

SELECT TOP 5 col1 FROM T1
GO

SET STATISTICS IO OFF
GO

DROP TABLE T1

(5 row(s) affected)
Table 'sysschobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(5 row(s) affected)
Table 'T1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Заводить отдельную табличку стоит потому, что не у всех простых сметрных может быть доступ к системным таблицам, ну, или число записей в них может быть недостаточным.
17 июл 09, 09:57    [7427412]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
pkarklin,

я имел ввиду случай с более длинной выборкой:) если человеку надо исключительно от 1 до 10 - пусть сделает
select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9 union all select 10

и не мучается. а вот когда у вас идет выборка скажем от 1 до 10000 - тут при размере записи 1 байт читается 2 страницы, а при размере 100 - 125.
17 июл 09, 10:02    [7427441]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
это я к тому собственно, что если прогнозируются большие последовательности - дешевле может оказаться не табличку с int заводить, а сделать tinyint, напихать туда нулей достаточно и выбирать row_number(). вообще такие задачи надо для конкретного случае решать, как и большинство прикладных:)
17 июл 09, 10:05    [7427459]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
SQLap
Member [заблокирован]

Откуда:
Сообщений: 34063
iap
SQLap
их можно просто выбрать пивотом?
Пример не покажете?


Я смогу что-то подобное для Оракла показать, так как в MS SQL совсем не рубинштейн

select rownum from
(select 1 from dual
  group by cube(1,1,1,1))
  where rownum < 11

Доступ на DUAL обычно есть у всех, запись в ней одна (по крайней мере если база в нормальном состоянии).

pkarklin
Заводить отдельную табличку стоит потому, что не у всех простых сметрных может быть доступ к системным таблицам, ну, или число записей в них может быть недостаточным


Теперь все понятно. Еще раз всем спасибо.
17 июл 09, 12:13    [7428418]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
Jaffar
Member

Откуда:
Сообщений: 633
ALTER function [dbo].[GenerateNumbers] (@From int, @To int, @Step int)
returns @t table ( N int )
as
begin
while @From <= @To
begin
insert into @t (N) values (@From)
select @From = @From + @Step
end
return
end
17 июл 09, 12:19    [7428457]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
Jaffar
Member

Откуда:
Сообщений: 633
не нужно никаких таблиц - табличная функция рулит.
Ничего не блокируется - работает моментально.
10000 записей работает ~1 сек.
17 июл 09, 12:29    [7428538]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
ну да! всего лишь 1 какая-то секунда, из 86400 в сутки. это даже меньше 1%...

для спящего время бодрствования равносильно сну
17 июл 09, 12:32    [7428555]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
Jaffar,
declare @t datetime
select @t = getdate()
;with L0(N) as (select 0 union all select 0),
	L1(N) as (select 0 from L0 t1, L0 t2),
	L2(N) as (select 0 from L1 t1, L1 t2),
	L3(N) as (select 0 from L2 t1, L2 t2),
	L4(N) as (select 0 from L3 t1, L3 t2),
	L5(N) as (select 0 from L4 t1, L4 t2)
select top 100000 row_number() over(order by (select 1))
from l5
option(maxrecursion 0)

select datediff(ms, @t, getdate())

сравните ради интереса быстродействие ;)
17 июл 09, 12:50    [7428670]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
кстати на больших последовательностях выборка из таблицы происходит сильно медленнее.
вот опять свалились на холивар... надо конкретный случай разбирать! с максимальной длинной последовательности.
17 июл 09, 12:55    [7428706]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
Добрый Э - Эх
Guest
2 ALL

Ну и не стоит забывать про старые добрые варианты на основе выборки одной строки из ниоткуда (в Oracle - из DUAL):
select (v1_100.rn_100 - 1) * 100 + v2_100.rn_100 as rn_10000
  from (
          select (v1.rn - 1) * 10 + v2.rn as rn_100
            from (
                   select 1 as rn union all
                   select 2 as rn union all
                   select 3 as rn union all
                   select 4 as rn union all
                   select 5 as rn union all
                   select 6 as rn union all
                   select 7 as rn union all
                   select 8 as rn union all
                   select 9 as rn union all
                   select 10 as rn
                 ) v1
           cross join
                 (
                   select 1 as rn union all
                   select 2 as rn union all
                   select 3 as rn union all
                   select 4 as rn union all
                   select 5 as rn union all
                   select 6 as rn union all
                   select 7 as rn union all
                   select 8 as rn union all
                   select 9 as rn union all
                   select 10 as rn
                 ) v2
      ) v1_100
      cross join 
      (
          select (v1.rn - 1) * 10 + v2.rn as rn_100
            from (
                   select 1 as rn union all
                   select 2 as rn union all
                   select 3 as rn union all
                   select 4 as rn union all
                   select 5 as rn union all
                   select 6 as rn union all
                   select 7 as rn union all
                   select 8 as rn union all
                   select 9 as rn union all
                   select 10 as rn
                 ) v1
           cross join
                 (
                   select 1 as rn union all
                   select 2 as rn union all
                   select 3 as rn union all
                   select 4 as rn union all
                   select 5 as rn union all
                   select 6 as rn union all
                   select 7 as rn union all
                   select 8 as rn union all
                   select 9 as rn union all
                   select 10 as rn
                 ) v2
      ) v2_100
 order by 1
17 июл 09, 13:03    [7428754]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
Alexander Us
Member

Откуда:
Сообщений: 1104
Вот готовая функция
17 июл 09, 16:44    [7430357]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
Alexander Us
Member

Откуда:
Сообщений: 1104
Alexander Us
Вот готовая функция

Модераторам: может сделать этот вопрос пунктом 11 в тор 10?
17 июл 09, 16:48    [7430386]     Ответить | Цитировать Сообщить модератору
 Re: Вывод последовательности чисел запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Alexander Us
Alexander Us
Вот готовая функция

Модераторам: может сделать этот вопрос пунктом 11 в тор 10?
Так его же при этом всё равно видно не будет!
17 июл 09, 18:55    [7431223]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить