Построковая обработка sql выборки в сценарии — различия между версиями

Материал из Oktell
Перейти к: навигация, поиск
м (Файлы)
 
(не показано 20 промежуточных версии этого же участника)
Строка 1: Строка 1:
 
[[Практики|Наверх]]
 
[[Практики|Наверх]]
 +
 +
__TOC__
 +
 +
 +
== Введение ==
  
 
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
 
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
Строка 25: Строка 30:
 
  |-
 
  |-
 
  | 6  
 
  | 6  
  |Алексей Алексей
+
  |Алексеев Алексей
 
  |}
 
  |}
 
</center>
 
</center>
Строка 32: Строка 37:
 
Как видно в таблице '''id''' идет не по порядку. При решении данной задачи необходимо учитывать этот факт.  
 
Как видно в таблице '''id''' идет не по порядку. При решении данной задачи необходимо учитывать этот факт.  
  
=== Вариант 1 для таблицы с числовым полем ===  
+
== Для таблицы с числовым полем ==
  
  
Строка 38: Строка 43:
  
  
Начальное значение переменной id_before присваивается "-1000", далее с помощью sql-запроса получаем самый первую строку (значения '''id''', ''' name'''), у которой '''id > id_before'''. Записываем '''id''' в переменную '''id_after'''. В этом же sql-запросе получаем количество строк, которые попадают под это условие. Вид запроса:  
+
Начальное значение переменной id_before присваивается "-1000", далее с помощью sql-запроса получаем самую первую строку (значения '''id''', ''' name'''), у которой '''id > id_before'''. Записываем '''id''' в переменную '''id_after'''. В этом же sql-запросе получаем количество строк, которые попадают под это условие. Вид запроса:  
  
 
  select top 1 @id=id, @name=name from notsorted where id > @id_before
 
  select top 1 @id=id, @name=name from notsorted where id > @id_before
Строка 54: Строка 59:
  
  
=== Вариант 2 для таблицы со строковыми полями ===
+
== Для таблицы со строковыми полями ==
  
 
[[Файл:Обработка таблицы 4.PNG | center]]
 
[[Файл:Обработка таблицы 4.PNG | center]]
Строка 89: Строка 94:
  
  
----
+
== Метод с for xml path ==
 +
 
 +
[[Файл:postrokova9_3.jpg | center]]
 +
 
 +
 
 +
1)Из таблицы получаем xml структуру и записываем её в переменную text_xml. Присвоение происходит неявным образом, для этого необходимо чтобы была создана переменная с именем, которое совпадает с именованием возвращаемого столбца.
 +
 
 +
Текст запроса:
 +
<pre>
 +
select cast( ( select * from oktell.dbo.notsorted for xml path ('') ) as xml ) as "text_xml"
 +
</pre>
 +
 
 +
2)Подсчитываем количество тегов id и записываем результат в cnt_id.
 +
 
 +
3)Устанавливаем счетчик итераций i=1.
 +
 
 +
4)Далее входим в цикл:
 +
:4.1)Получаем i-ый тег id
 +
:4.2)Получаем i-ый тег name
 +
:4.3)Выводим результат в уведомлении, на данном шаге с результатами можно произвести какие либо действия.
 +
:4.4)Производим инкремент i на 1
 +
:4.5)Проверяем условие i<=cnt_id, если перебрали еще не все теги то идем на шаг 4.1, иначе на шаг 5
 +
5)Завершаем сценарий, выводим уведомление.
 +
 
 +
 
 +
[[Файл:postrokova9_3_1.jpg | center]]
 +
 
 +
Метод является универсальным и не зависит от вида данных.
 +
 
 +
== Файлы ==
  
'''Файлы:'''
+
*[[Media:%D0%9F%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B_%D0%92%D0%B0%D1%80%D0%B8%D0%B0%D0%BD%D1%82_1.zip|Для таблицы с числовым полем]]
  
* [http://wiki.oktell.ru/images/6/66/%D0%9F%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B_%D0%92%D0%B0%D1%80%D0%B8%D0%B0%D0%BD%D1%82_1.zip Построковая обработка таблицы Вариант 1]
+
* [[Media:%D0%9F%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B_%D0%92%D0%B0%D1%80%D0%B8%D0%B0%D0%BD%D1%82_2.zip|Для таблицы со строковыми полями]]
  
* [http://wiki.oktell.ru/images/6/6d/%D0%9F%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B_%D0%92%D0%B0%D1%80%D0%B8%D0%B0%D0%BD%D1%82_2.zip Построковая обработка таблицы Вариант 2]
+
*[[Media:Svc_%D0%BF%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F_%D0%BE%D0%B1%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0_for_xml_path.oscr|Метод с for xml path]]

Текущая версия на 11:10, 31 марта 2023

Наверх


Введение

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

Возьмем к примеру следующую таблицу notsorted с двумя столбцами id и name:


notsorted
id name
0 Иванов Иван
3 Петров Петр
4 Сергеев Сергей
5 Дмитриев Дмитрий
6 Алексеев Алексей


Как видно в таблице id идет не по порядку. При решении данной задачи необходимо учитывать этот факт.

Для таблицы с числовым полем

Обработка таблицы 3.PNG


Начальное значение переменной id_before присваивается "-1000", далее с помощью sql-запроса получаем самую первую строку (значения id, name), у которой id > id_before. Записываем id в переменную id_after. В этом же sql-запросе получаем количество строк, которые попадают под это условие. Вид запроса:

select top 1 @id=id, @name=name from notsorted where id > @id_before
set @rowcount=@@rowcount

Если количество строк, у которых условие id > id_before равно нулю (т.е. таких строк нет), то это является индикатором конца цикла. Таким образом в проверке на завершение происходит сравнение rowcount с нулем. Если строки нашлись, то присваиваем id_before текущее значение id_after. Далее совершаем необходимые действия со строкой.

Результат:


Обработка таблицы 3-1.PNG


Недостатки: Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано только одно из этих значений (в связи с алгоритмом).


Для таблицы со строковыми полями

Обработка таблицы 4.PNG


Начальному значению счетчика i присваиваем 0. Конечное значение счетчика ilast равняется количеству строк в таблице, которые необходимо обработать, и находится с помощью sql-запроса:

select @ilast = count(*) from notsorted

Затем счетчик инкрементируется и входит в цикл. Условием окончания цикла является прохождения ilast строк, таким образом если i > ilast, то цикл заканчивает выполняться. Далее необходимо выбрать необходимую строку. Предполагая, что оперируем только со строковым полем предлагается следующий алгоритм. Строки сортируются лексикографически по убыванию, затем выбираются первые i строк. Затем выбирается самая последняя строка (полученная выборка сортируется лексикографически по возрастанию и выбирается первая запись). Таким образом происходит доступ к элементу.

Так как нет возможности формировать sql-запрос динамически (добавив в него переменные), запрос формируется отдельно в строке sql (компонент присвоение). Вид формируемой строки:

'Select Top 1 name as name1
From (
	Select Top ' +Str([i])+ ' name
	From notsorted
	Order by name desc
) a
Order by name asc'

Выполнить данный запрос можно с помощью компонента sql-запрос. При выполнении нужное значение, как видно из запроса, сохраняется в строку name1. Необходимо в сценарии заранее создать переменную с идентичным названием. Строка запроса (в параметрах определяем @sql как строку с запросом sql). Фактически, "строка" преобразовывается в "SQL-запрос".

exec sp_executesql @sql 

Результат:

Обработка таблицы 4-1.PNG


Недостатки: сравнительно с первым вариантом, долгое время выполнения sql-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке.

Достоинства: в таблице не обязательно иметь числовое поле - выборку можно сделать по строковым полям. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.


Метод с for xml path

Postrokova9 3.jpg


1)Из таблицы получаем xml структуру и записываем её в переменную text_xml. Присвоение происходит неявным образом, для этого необходимо чтобы была создана переменная с именем, которое совпадает с именованием возвращаемого столбца.

Текст запроса:

select cast( ( select * from oktell.dbo.notsorted for xml path ('') ) as xml ) as "text_xml"

2)Подсчитываем количество тегов id и записываем результат в cnt_id.

3)Устанавливаем счетчик итераций i=1.

4)Далее входим в цикл:

4.1)Получаем i-ый тег id
4.2)Получаем i-ый тег name
4.3)Выводим результат в уведомлении, на данном шаге с результатами можно произвести какие либо действия.
4.4)Производим инкремент i на 1
4.5)Проверяем условие i<=cnt_id, если перебрали еще не все теги то идем на шаг 4.1, иначе на шаг 5

5)Завершаем сценарий, выводим уведомление.


Postrokova9 3 1.jpg

Метод является универсальным и не зависит от вида данных.

Файлы