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

Материал из Oktell
Перейти к: навигация, поиск
м (Файлы)
 
(не показаны 33 промежуточные версии этого же участника)
Строка 1: Строка 1:
 +
[[Практики|Наверх]]
 +
 +
__TOC__
 +
 +
 +
== Введение ==
 +
 
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
 
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку.
  
Возьмем к примеру следующую таблицу notsorted с двумя столбцами Id и name:   
+
Возьмем к примеру следующую таблицу notsorted с двумя столбцами '''id''' и '''name:'''  
 
   
 
   
  
Строка 23: Строка 30:
 
  |-
 
  |-
 
  | 6  
 
  | 6  
  |Алексей Алексей
+
  |Алексеев Алексей
 
  |}
 
  |}
 
</center>
 
</center>
  
  
Как видно в таблице id идет не по порядку. При решении данной задачи необходимо учитывать этот факт.  
+
Как видно в таблице '''id''' идет не по порядку. При решении данной задачи необходимо учитывать этот факт.  
  
=== Классический способ решения (не применим) ===
+
== Для таблицы с числовым полем ==
 
+
 
+
[[Файл:Обработка таблицы 2.PNG |center]]
+
 
+
В самом начале счетчику i присваивается значение "-1", конечному значению счетчика присваивается количество строк в таблице с помощью запроса:
+
 
+
select @ilast = count(*) from notsorted
+
 
+
Далее реализован цикл, счетчик инкрементируется, затем сравнивается с конечным значением. Выбор значения происходит следующим sql запросом:
+
 
+
select @name=name from notsorted where id=@id
+
 
+
С полученным значением name производятся действия.
+
 
+
Результат:
+
 
+
[[Файл:Обработка таблицы 2-1.PNG| center]]
+
 
+
Недостатки: обработка таблицы идет по значению id. Так как в таблице нет строк с id=1, 2 , то сценарий будет делать лишний цикл, выдавать ошибку на sql запросе (или выдавать одно значение несколько раз). Вариант неприменим, если в таблице нет уникального идентификатора, даже если есть некое числовое поле - если у некоторых строк оно одинаковое, то сценарий не сможет вывести эти значения.
+
 
+
=== Вариант 1 для таблицы с числовым полем ===  
+
  
  
Строка 57: Строка 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
 
  set @rowcount=@@rowcount
 
  set @rowcount=@@rowcount
  
Если количество строк, у которых условие id> id_before равно нулю (т.е. таких строк нет), то это является индикатором конца цикла. Таким образом в проверке на завершение происходит сравнение rowcount с нулем. Если строки нашлись, то присваиваем id_before  текущее значение id_after. Далее совершаем необходимые действия со строкой.  
+
Если количество строк, у которых условие '''id > id_before''' равно нулю (т.е. таких строк нет), то это является индикатором конца цикла. Таким образом в проверке на завершение происходит сравнение rowcount с нулем. Если строки нашлись, то присваиваем '''id_before''' текущее значение '''id_after'''. Далее совершаем необходимые действия со строкой.  
  
Результат:
+
'''Результат:'''
  
  
Строка 70: Строка 56:
  
  
Недостатки: Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано только одно из этих значений (в связи с алгоритмом).
+
'''Недостатки:''' Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано '''только одно''' из этих значений (в связи с алгоритмом).
  
  
=== Вариант 2 для таблицы со строковыми полями ===
+
== Для таблицы со строковыми полями ==
  
 
[[Файл:Обработка таблицы 4.PNG | center]]
 
[[Файл:Обработка таблицы 4.PNG | center]]
  
  
Начальному значению счетчика i присваиваем 0. Конечное значение счетчика ilast равняется количеству строк в таблице, которые необходимо обработать, и находится с помощью sql-запроса:
+
Начальному значению счетчика '''i''' присваиваем '''0'''. Конечное значение счетчика '''ilast''' равняется количеству строк в таблице, которые необходимо обработать, и находится с помощью sql-запроса:
  
 
  select @ilast = count(*) from notsorted
 
  select @ilast = count(*) from notsorted
  
Затем счетчик инкрементируется и входит в цикл. Условием окончания цикла является прохождения ilast строк, таким образом если i> ilast, то цикл заканчивает выполняться. Далее необходимо выбрать необходимую строку. Предполагая, что оперируем только со строковым полем предлагается следующий алгоритм. Строки сортируются лексикографически по убыванию, затем выбираются первые i строк. Затем выбирается самая последняя строка (полученная выборка сортируется лексикографически по возрастанию и выбирается первая запись). Таким образом происходит доступ к элементу.  
+
Затем счетчик инкрементируется и входит в цикл. Условием окончания цикла является прохождения ilast строк, таким образом если '''i > ilast''', то цикл заканчивает выполняться. Далее необходимо выбрать необходимую строку. Предполагая, что оперируем только со строковым полем предлагается следующий алгоритм. Строки сортируются лексикографически по убыванию, затем выбираются первые '''i''' строк. Затем выбирается самая последняя строка (полученная выборка сортируется лексикографически по возрастанию и выбирается первая запись). Таким образом происходит доступ к элементу.  
  
Так как нет возможности формировать sql запрос динамически (добавив в него переменные), запрос формируется отдельно в строке sql (компонент присвоение). Вид формируемой строки:  
+
Так как нет возможности формировать sql-запрос динамически (добавив в него переменные), запрос формируется отдельно в строке '''sql''' (компонент присвоение). Вид формируемой строки:  
  
 
  'Select Top 1 name as name1
 
  'Select Top 1 name as name1
 
  From (
 
  From (
Select Top ' +Str([i])+ ' name
+
Select Top ' +Str([i])+ ' name
From notsorted
+
From notsorted
Order by name desc
+
Order by name desc
 
  ) a
 
  ) a
 
  Order by name asc'
 
  Order by name asc'
 
   
 
   
Выполнить данный запрос можно с помощью компонента sql-запрос. При выполнении нужное значение, как видно из запроса, сохраняется в строку name1. Необходимо в сценарии заранее создать переменную с идентичным названием. Строка запроса (в параметрах определяем @sql как строку с запросом sql).  
+
Выполнить данный запрос можно с помощью компонента '''sql-запрос'''. При выполнении нужное значение, как видно из запроса, сохраняется в строку '''name1'''. Необходимо в сценарии заранее создать переменную с идентичным названием. Строка запроса (в параметрах определяем '''@sql''' как строку с запросом '''sql''').  Фактически, "строка" преобразовывается в "SQL-запрос".
  
exec sp_executesql @sql  
+
exec sp_executesql @sql  
  
Результат:  
+
'''Результат:'''
  
 
[[Файл:Обработка таблицы 4-1.PNG | center]]
 
[[Файл:Обработка таблицы 4-1.PNG | center]]
  
Недостатки: сравнительно с первым вариантом, долгое время выполнения sql-запроса.
 
  
Достоинства: записи обрабатываются лексикографически; в таблице не обязательно иметь числовое поле - выборку можно сделать по строковым поля. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.
+
'''Недостатки:''' сравнительно с первым вариантом, долгое время выполнения sql-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке.
 +
 
 +
'''Достоинства:''' в таблице не обязательно иметь числовое поле - выборку можно сделать '''по строковым полям'''. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.
 +
 
 +
 
 +
== Метод с 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|Для таблицы с числовым полем]]
 +
 
 +
* [[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

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

Файлы