Построковая обработка sql выборки в сценарии — различия между версиями
м (→Файлы) |
|||
(не показаны 32 промежуточные версии этого же участника) | |||
Строка 1: | Строка 1: | ||
+ | [[Практики|Наверх]] | ||
+ | |||
+ | __TOC__ | ||
+ | |||
+ | |||
+ | == Введение == | ||
+ | |||
Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку. | Если у вас имеется sql-таблица, в которой есть данные, зачастую возникает необходимость в обработке этих данных. Сложность заключается в том, что данные могут быть не пронумерованы или даже если пронумерованы, порядковые номера могут идти не по порядку. | ||
− | Возьмем к примеру следующую таблицу notsorted с двумя столбцами | + | Возьмем к примеру следующую таблицу notsorted с двумя столбцами '''id''' и '''name:''' |
Строка 23: | Строка 30: | ||
|- | |- | ||
| 6 | | 6 | ||
− | | | + | |Алексеев Алексей |
|} | |} | ||
</center> | </center> | ||
− | Как видно в таблице id идет не по порядку. При решении данной задачи необходимо учитывать этот факт. | + | Как видно в таблице '''id''' идет не по порядку. При решении данной задачи необходимо учитывать этот факт. |
− | == | + | == Для таблицы с числовым полем == |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
Строка 57: | Строка 43: | ||
− | Начальное значение переменной id_before присваивается "-1000", далее с помощью 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: | ||
− | Недостатки: Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано только одно из этих значений (в связи с алгоритмом). | + | '''Недостатки:''' Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано '''только одно''' из этих значений (в связи с алгоритмом). |
− | == | + | == Для таблицы со строковыми полями == |
[[Файл:Обработка таблицы 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 | ||
Строка 94: | Строка 80: | ||
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-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке. | |
+ | |||
+ | '''Достоинства:''' в таблице не обязательно иметь числовое поле - выборку можно сделать '''по строковым полям'''. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице. | ||
+ | |||
+ | |||
+ | == Метод с 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:
id | name |
0 | Иванов Иван |
3 | Петров Петр |
4 | Сергеев Сергей |
5 | Дмитриев Дмитрий |
6 | Алексеев Алексей |
Как видно в таблице id идет не по порядку. При решении данной задачи необходимо учитывать этот факт.
Для таблицы с числовым полем
Начальное значение переменной 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. Далее совершаем необходимые действия со строкой.
Результат:
Недостатки: Если в таблице нет числовых столбцов, то сценарий не применим; если в числовом столбце повторяются значения, то будет обработано только одно из этих значений (в связи с алгоритмом).
Для таблицы со строковыми полями
Начальному значению счетчика 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
Результат:
Недостатки: сравнительно с первым вариантом, долгое время выполнения sql-запроса; записи обрабатываются не в том порядке, в котором находятся в таблице, а в лексикографическом порядке.
Достоинства: в таблице не обязательно иметь числовое поле - выборку можно сделать по строковым полям. Это полезно, если у вас нет уникальных идентификаторов в вашей таблице.
Метод с for xml path
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)Завершаем сценарий, выводим уведомление.
Метод является универсальным и не зависит от вида данных.