Генерация числовой последовательности и последовательности дат при помощи языка запросов.| В статье будет рассмотрен способ генерации числовой последовательности и последовательности дат с помощью запросов. Также будет рассмотрен практический пример. | | Автор статьи: lamort | Редакторы: vet7777
Последняя редакция №4 от 26.03.10 | История
URL: http://kb.mista.ru/article.php?id=704 | |
Ключевые слова: Запрос, язык запросов, последовательность чисел, последовательность дат.
Иногда может возникнуть ситуация, когда необходимо в запросе получить последовательность чисел. Обычно это не является самоцелью, а служит промежуточным результатом для получения последовательности дат. Тут есть два способа, первый — это пойти в лоб и написать запрос, объединяющий столько запросов, сколько чисел нам необходимо получить. Например:
ВЫБРАТЬ
1 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3
Второй способ заключается в использовании декартова произведения, особенностью которого является то, что мощность результата (количество строк) равно произведению мощностей участвующих в декартовом произведении таблиц. Т.е. если нам нужно получить последовательность 25 чисел мы можем использовать декартово произведение таблиц, каждая из которых содержит по 5 записей. В стандартном SQL для декартова произведения используется конструкция CROSS JOIN, в языке запросов 1С такая конструкция отсутствует, но мы можем воспользоваться обычным внутренним соединением, указав условие связи ИСТИНА. Далее приведен пример запроса, генерирующего последовательность от 1 до 25:
ВЫБРАТЬ
5*(a-1)+b
ИЗ
(ВЫБРАТЬ
1 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
1 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5) КАК t2
ПО (ИСТИНА)
В данном запросе мы используем декартово произведение двух таблиц содержащее по пять строк.
Значение мы вычисляем по следующему принципу: считаем что в первой таблицы у нас содержаться пятёрки, а во второй единицы. Результат вычисляем по следующей формуле ВЫБРАТЬ 5*(a-1)+b.
Чем больше мы хотим генерировать последовательность, тем больше таблиц в декртовом произведении нам необходимо использовать.
Генерация последовательности дат.
Допустим перед нами стоит задача, сгенерировать последовательность дат месяца.
В качестве параметра в запрос мы будем передавать первую дату месяца.
ВЫБРАТЬ ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&МЕСЯЦ, МЕСЯЦ), ДЕНЬ, n) КАК Дата
ИЗ (ВЫБРАТЬ
6*(a-1)+b-1 КАК n
ИЗ
(ВЫБРАТЬ
1 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6) КАК t1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
1 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6) КАК t2
ПО (ИСТИНА)) КАК T
ГДЕ
n < ДЕНЬ(КОНЕЦПЕРИОДА(&МЕСЯЦ, МЕСЯЦ))
Во вложенном запросе мы формируем числовую последовательность от 0 до 35, далее в запросе верхнего уровня мы генерируем последовательность дат, ограничивая её последним числом месяца.
Практическое применение последовательности дат
Все эти выкладки пригодились при решении практической задачи, суть которой состояла в следующем:
на каждую дату месяца необходимо получить актуальное состояние работников организации из периодического регистра сведений "СостояниеРаботниковОрганизаций", если по этому работнику есть данные (ЗУП). Запрос, решающий поставленную задачу приведен ниже.
ВЫБРАТЬ
СостоянияРаботниковОрганизацийНаДату.Дата,
СостоянияРаботниковОрганизацийНаДату.Сотрудник,
ВЫБОР
КОГДА СостояниеРаботниковОрганизаций.ПериодЗавершения <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
И СостоянияРаботниковОрганизацийНаДату.Дата >= СостояниеРаботниковОрганизаций.ПериодЗавершения
ТОГДА СостояниеРаботниковОрганизаций.СостояниеЗавершения
ИНАЧЕ СостояниеРаботниковОрганизаций.Состояние
КОНЕЦ КАК СостояниеСотрудника
ИЗ
(ВЫБРАТЬ
МАКСИМУМ(СостояниеРаботниковОрганизаций.Период) КАК Период,
СостояниеРаботниковОрганизаций.Сотрудник КАК Сотрудник,
СостояниеРаботниковОрганизаций.Организация КАК Организация,
СостояниеРаботниковОрганизаций.УдалитьФизЛицо КАК УдалитьФизЛицо,
СостояниеРаботниковОрганизаций.УдалитьПриказ КАК УдалитьПриказ,
Даты.Дата КАК Дата
ИЗ
(ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(&МЕСЯЦ, МЕСЯЦ), ДЕНЬ, t.n) КАК Дата
ИЗ
(ВЫБРАТЬ
6 * (a - 1) + b - 1 КАК n
ИЗ
(ВЫБРАТЬ
1 КАК a
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6) КАК t1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
1 КАК b
ОБЪЕДИНИТЬ
ВЫБРАТЬ
2
ОБЪЕДИНИТЬ
ВЫБРАТЬ
3
ОБЪЕДИНИТЬ
ВЫБРАТЬ
4
ОБЪЕДИНИТЬ
ВЫБРАТЬ
5
ОБЪЕДИНИТЬ
ВЫБРАТЬ
6) КАК t2
ПО (ИСТИНА)) КАК t
ГДЕ
t.n < ДЕНЬ(КОНЕЦПЕРИОДА(&МЕСЯЦ, МЕСЯЦ))) КАК Даты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СостояниеРаботниковОрганизаций КАК СостояниеРаботниковОрганизаций
ПО Даты.Дата >= СостояниеРаботниковОрганизаций.Период
ГДЕ
СостояниеРаботниковОрганизаций.Сотрудник = &Сотрудник
СГРУППИРОВАТЬ ПО
СостояниеРаботниковОрганизаций.Сотрудник,
СостояниеРаботниковОрганизаций.Организация,
СостояниеРаботниковОрганизаций.УдалитьФизЛицо,
СостояниеРаботниковОрганизаций.УдалитьПриказ,
Даты.Дата) КАК СостоянияРаботниковОрганизацийНаДату
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СостояниеРаботниковОрганизаций КАК СостояниеРаботниковОрганизаций
ПО СостоянияРаботниковОрганизацийНаДату.Период = СостояниеРаботниковОрганизаций.Период
И СостоянияРаботниковОрганизацийНаДату.Сотрудник = СостояниеРаботниковОрганизаций.Сотрудник
И СостоянияРаботниковОрганизацийНаДату.Организация = СостояниеРаботниковОрганизаций.Организация
И СостоянияРаботниковОрганизацийНаДату.УдалитьФизЛицо = СостояниеРаботниковОрганизаций.УдалитьФизЛицо
И СостоянияРаботниковОрганизацийНаДату.УдалитьПриказ = СостояниеРаботниковОрганизаций.УдалитьПриказ
Суть этого запроса состоит в следующем: генерируется последовательность дат, и на каждую дату получается последнее актуальное состояние работника. |