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

Материал из Oktell
Перейти к: навигация, поиск
м (Файлы)
 
(не показано 35 промежуточных версии этого же участника)
Строка 1: Строка 1:
 +
[[Практики|Наверх]]
 +
 +
__TOC__
 +
 +
 +
== Введение ==
 +
 
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
 
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
  
Возьмем к примеру следующую таблицу notsorted с двумя столбцами Id и name:   
+
Возьмем к примеру следующую таблицу notsorted с двумя столбцами '''id''' и '''name:'''  
 
   
 
   
  
Строка 23: Строка 30:
 
  |-
 
  |-
 
  | 6  
 
  | 6  
  |Алексей Алексей
+
  |Алексеев Алексей
 
  |}
 
  |}
 
</center>
 
</center>
  
  
Как видно в таблице id идет не по порядку. При решении данной задачи необходимо учитывать этот факт.  
+
Как видно в таблице '''id''' идет не по порядку. При решении данной задачи необходимо учитывать этот факт.  
  
=== Классический способ решения (не применим) ===  
+
== Для таблицы с числовым полем ==
  
  
[[Файл:Обработка таблицы 2.PNG |center]]
+
[[Файл:Обработка таблицы 3.PNG | center]]
  
В самом начале счетчику i присваивается значение "-1", конечному значению счетчика присваивается количество строк в таблице с помощью запроса:
+
 
 +
Начальное значение переменной 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 | center]]
 +
 
 +
 
 +
'''Недостатки:''' Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано '''только одно''' из этих значений (в связи с алгоритмом).
 +
 
 +
 
 +
== Для таблицы со строковыми полями ==
 +
 
 +
[[Файл:Обработка таблицы 4.PNG | center]]
 +
 
 +
 
 +
Начальному значению счетчика '''i''' присваиваем '''0'''. Конечное значение счетчика '''ilast''' равняется количеству строк в таблице, которые необходимо обработать, и находится с помощью sql-запроса:
  
 
  select @ilast = count(*) from notsorted
 
  select @ilast = count(*) from notsorted
  
Далее реализован цикл, счетчик инкрементируется, затем сравнивается с конечным значением. Выбор значения происходит следующим sql запросом:
+
Затем счетчик инкрементируется и входит в цикл. Условием окончания цикла является прохождения ilast строк, таким образом если '''i > ilast''', то цикл заканчивает выполняться. Далее необходимо выбрать необходимую строку. Предполагая, что оперируем только со строковым полем предлагается следующий алгоритм. Строки сортируются лексикографически по убыванию, затем выбираются первые '''i''' строк. Затем выбирается самая последняя строка (полученная выборка сортируется лексикографически по возрастанию и выбирается первая запись). Таким образом происходит доступ к элементу.
  
select @name=name from notsorted where id=@id
+
Так как нет возможности формировать sql-запрос динамически (добавив в него переменные), запрос формируется отдельно в строке '''sql''' (компонент присвоение). Вид формируемой строки:
  
С полученным значением name производятся действия.  
+
'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
  
[[Файл:Обработка таблицы 2-1.PNG| center]]
+
'''Результат:'''
  
Недостатки: обработка таблицы идет по значению id. Так как в таблице нет строк с id=1, 2 , то сценарий будет делать лишний цикл, выдавать ошибку на sql запросе (или выдавать одно значение несколько раз). Вариант неприменим, если в таблице нет уникального идентификатора, даже если есть некое числовое поле - если у некоторых строк оно одинаковое, то сценарий не сможет вывести эти значения.  
+
[[Файл:Обработка таблицы 4-1.PNG | center]]
  
=== Вариант 1 для таблицы с числовым полем ===
 
  
 +
'''Недостатки:''' сравнительно с первым вариантом, долгое время выполнения sql-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке.
  
[[Файл:Обработка таблицы 3.PNG | center]]
+
'''Достоинства:''' в таблице не обязательно иметь числовое поле - выборку можно сделать '''по строковым полям'''. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.
  
  
Начальное значение переменной id_before присваивается "-1000", далее с помощью sql запроса получаем самый первую строку (значения id, name), у которой id > id_before. Записываем id в переменную id_after. В этом же sql запросе получаем количество строк, которые попадают под это условие. Вид запроса:
+
== Метод с for xml path ==
  
select top 1 @id=id, @name=name from notsorted where id>@id_before
+
[[Файл:postrokova9_3.jpg | center]]
set @rowcount=@@rowcount
+
  
Если количество строк, у которых условие id> id_before равно нулю (т.е. таких строк нет), то это является индикатором конца цикла. Таким образом в проверке на завершение происходит сравнение rowcount с нулем. Если строки нашлись, то присваиваем id_before  текущее значение id_after. Далее совершаем необходимые действия со строкой.
 
  
Результат:
+
1)Из таблицы получаем xml структуру и записываем её в переменную text_xml. Присвоение происходит неявным образом, для этого необходимо чтобы была создана переменная с именем, которое совпадает с именованием возвращаемого столбца.
  
 +
Текст запроса:
 +
<pre>
 +
select cast( ( select * from oktell.dbo.notsorted for xml path ('') ) as xml ) as "text_xml"
 +
</pre>
  
[[Файл:Обработка таблицы 3-1.PNG | center]]
+
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)Завершаем сценарий, выводим уведомление.
  
  
=== Вариант 2 для таблицы со строковыми полями ===
+
[[Файл:postrokova9_3_1.jpg | center]]
  
 +
Метод является универсальным и не зависит от вида данных.
  
[[Файл:Обработка таблицы 4.PNG | 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|Для таблицы с числовым полем]]
  
[[Файл:Обработка таблицы 4-1.PNG | 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_2.zip|Для таблицы со строковыми полями]]
 +
 
 +
*[[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

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

Файлы