Диапазон на основе набора SQL

Если вы используете SQL Server 2000 или выше, вы можете использовать тип данных таблицы, чтобы избежать создания нормальной или временной таблицы. Затем используйте на нем обычные операции с таблицей.

С помощью этого решения у вас есть, по сути, структура таблицы в памяти, которую вы можете использовать почти как настоящую таблицу, но с гораздо большей производительностью.

Я нашел здесь хорошее обсуждение: Временные таблицы и тип данных таблицы

По сути, это одна из тех вещей, которые показывают, что SQL не идеален. Я думаю, что, возможно, правильный способ сделать это - создать функцию, которая создает диапазон. (Или генератор.)

Я считаю, что правильный ответ на ваш вопрос - «вы не можете». (Извини.)

Я думаю, что очень короткий ответ на ваш вопрос - использовать предложения WITH для создания своих собственных.

К сожалению, громкие имена в базах данных не имеют встроенных запрашиваемых псевдотаблиц диапазона номеров. Или, в более общем плане, простые функции генерации данных на чистом SQL. Лично я считаю, что это ошибка огромный, потому что, если бы они это сделали, можно было бы переместить большой объем кода, который в настоящее время заблокирован в процедурных сценариях (T-SQL, PL / SQL и т. д.), В чистый SQL, который имеет ряд преимуществ для производительности и сложности кода.

Так или иначе, похоже, что в общем смысле вам нужна возможность генерировать данные на лету.

Oracle и T-SQL поддерживают предложение WITH, которое можно использовать для этого. Они работают немного по-разному в разных СУБД, и MS называет их «обычными табличными выражениями», но они очень похожи по форме. Используя их с рекурсией, вы можете довольно легко сгенерировать последовательность чисел или текстовых значений. Вот как это может выглядеть ...

В Oracle SQL:

WITH
  digits AS  -- Limit recursion by just using it for digits.
    (SELECT
      LEVEL - 1 AS num
    FROM
      DUAL
    WHERE
      LEVEL < 10
    CONNECT BY
      num = (PRIOR num) + 1),
  numrange AS
    (SELECT
      ones.num
        + (tens.num * 10)
        + (hundreds.num * 100)
        AS num
    FROM
      digits ones
      CROSS JOIN
        digits tens
      CROSS JOIN
        digits hundreds
    WHERE
      hundreds.num in (1, 2)) -- Use the WHERE clause to restrict each digit as needed.
SELECT
  -- Some columns and operations
FROM
  numrange
  -- Join to other data if needed

По общему признанию, это довольно многословно. Функциональность рекурсии Oracle ограничена. Синтаксис неуклюжий, неэффективный и ограничен 500 (я думаю) вложенными уровнями. Вот почему я решил использовать рекурсию только для первых 10 цифр, а затем перекрестные (декартовы) соединения, чтобы объединить их в реальные числа.

Я сам не использовал общие табличные выражения SQL Server, но поскольку они позволяют ссылаться на себя, рекурсия НАМНОГО проще, чем в Oracle. Сравнима ли производительность и каковы пределы вложенности, я не знаю.

В любом случае, рекурсия и предложение WITH - очень полезные инструменты при создании запросов, которые требуют наборов данных, генерируемых на лету. Затем, запрашивая этот набор данных, выполняя операции со значениями, вы можете получить всевозможные типы сгенерированных данных. Агрегации, дублирования, комбинации, перестановки и так далее. Вы даже можете использовать такие сгенерированные данные для облегчения свертывания или детализации других данных.

Обновлено: Я просто хочу добавить, что как только вы начнете работать с данными таким образом, это откроет ваш разум для новых взглядов на SQL. Это не просто язык сценариев. Это довольно надежный управляемый данными декларативный язык. Иногда это неудобно, потому что в течение многих лет он страдал от недостатка улучшений, помогающих уменьшить избыточность, необходимую для сложных операций. Но, тем не менее, это очень мощный и довольно интуитивно понятный способ работы с наборами данных как с целью, так и с движущей силой ваших алгоритмов.

Я создал функцию SQL CLR с табличным значением, которая отлично подходит для этой цели.

SELECT n FROM dbo.Range(1, 11, 2) -- returns odd integers 1 to 11
SELECT n FROM dbo.RangeF(3.1, 3.5, 0.1) -- returns 3.1, 3.2, 3.3 and 3.4, but not 3.5 because of float inprecision. !fault(this)

Вот код:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

[assembly: CLSCompliant(true)]
namespace Range {
    public static partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true, FillRowMethodName = "FillRow", TableDefinition = "n bigint")]
        public static IEnumerable Range(SqlInt64 start, SqlInt64 end, SqlInt64 incr) {
            return new Ranger(start.Value, end.Value, incr.Value);
        }

        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true, FillRowMethodName = "FillRowF", TableDefinition = "n float")]
        public static IEnumerable RangeF(SqlDouble start, SqlDouble end, SqlDouble incr) {
            return new RangerF(start.Value, end.Value, incr.Value);
        }

        public static void FillRow(object row, out SqlInt64 n) {
            n =  new SqlInt64((long)row);
        }

        public static void FillRowF(object row, out SqlDouble n) {
            n = new SqlDouble((double)row);
        }
    }

    internal class Ranger : IEnumerable {
        Int64 _start, _end, _incr;

        public Ranger(Int64 start, Int64 end, Int64 incr) {
            _start = start; _end = end; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            return new RangerEnum(_start, _end, _incr);
        }
    }

    internal class RangerF : IEnumerable {
        double _start, _end, _incr;

        public RangerF(double start, double end, double incr) {
            _start = start; _end = end; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            return new RangerFEnum(_start, _end, _incr);
        }
    }

    internal class RangerEnum : IEnumerator {
        Int64 _cur, _start, _end, _incr;
        bool hasFetched = false;

        public RangerEnum(Int64 start, Int64 end, Int64 incr) {
            _start = _cur = start; _end = end; _incr = incr;
            if ((_start < _end ^ _incr > 0) || _incr == 0)
                throw new ArgumentException("Will never reach end!");
        }

        public long Current {
            get { hasFetched = true; return _cur; }
        }

        object IEnumerator.Current {
            get { hasFetched = true; return _cur; }
        }

        public bool MoveNext() {
            if (hasFetched) _cur += _incr;
            return (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = false;
        }
    }

    internal class RangerFEnum : IEnumerator {
        double _cur, _start, _end, _incr;
        bool hasFetched = false;

        public RangerFEnum(double start, double end, double incr) {
            _start = _cur = start; _end = end; _incr = incr;
            if ((_start < _end ^ _incr > 0) || _incr == 0)
                throw new ArgumentException("Will never reach end!");
        }

        public double Current {
            get { hasFetched = true; return _cur; }
        }

        object IEnumerator.Current {
            get { hasFetched = true; return _cur; }
        }

        public bool MoveNext() {
            if (hasFetched) _cur += _incr;
            return (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = false;
        }
    }
}

и я развернул его так:

create assembly Range from 'Range.dll' with permission_set=safe -- mod path to point to actual dll location on disk.
go
create function dbo.Range(@start bigint, @end bigint, @incr bigint)
  returns table(n bigint)
  as external name [Range].[Range.UserDefinedFunctions].[Range]
go
create function dbo.RangeF(@start float, @end float, @incr float)
  returns table(n float)
  as external name [Range].[Range.UserDefinedFunctions].[RangeF]
go

Вот хитрость, которую вы никогда не должны использовать:

select sum(numberGenerator.rank)
from
(
select
    rank =  ( select count(*)  
              from reallyLargeTable t1 
              where t1.uniqueValue > t2.uniqueValue ), 
    t2.uniqueValue id1, 
    t2.uniqueValue id2
from reallyLargeTable t2 
) numberGenerator
where rank between 1 and 10

Вы можете упростить это, используя функции Rank () или Row_Number в SQL 2005.

Для этого в SQL2005 + можно использовать обычное табличное выражение.

WITH CTE AS
(
    SELECT 100 AS n
    UNION ALL
    SELECT n + 1 AS n FROM CTE WHERE n + 1 <= 200
)
SELECT n FROM CTE