Хотя в наши дни я в основном работаю с MySQL / MariaDB над проектами внутренней веб-разработки, я изучаю Oracle SQL с конечной целью стать сертифицированным. Недавно я посмотрел отличное видео на YouTube и решил попробовать свои силы в переносе запросов Oracle SQL в сопоставимый MySQL в качестве учебного упражнения. В этом сообщении блога подробно описаны вопросы, которые я придумал для получения конечных результатов. Продолжайте читать, чтобы узнать о некоторых забавных рекурсивных CTE с использованием символьной функции SUBSTRING() и интересных вариантов отображения вывода.
Самореклама:
Если вам нравится содержание, написанное здесь, обязательно поделитесь этим блогом и своими любимыми сообщениями с другими людьми, которым он также может быть полезен или им понравится. Поскольку кофе - мой любимый напиток, вы можете даже купить мне его, если хотите!
Кредитный ресурс и вдохновение
В видео ниже автор предоставляет несколько примеров отображения определенного слова в различных форматах вывода с использованием предложения Oracle SQL CONNECT BY и псевдостолбца ROWNUM. Siva Academy - фантастический канал об Oracle SQL. Автор действительно хорошо осведомлен и всегда имеет интересные примеры для демонстрации. Я настоятельно рекомендую посетить этот канал, если вас интересует Oracle SQL. Я был вдохновлен написать этот пост в блоге из-за этого видео и хочу поблагодарить постер.
Вдохновляющее видео от Siva Academy. (внешняя ссылка)
Рекурсивный CTE, определяемая пользователем переменная сеанса, функции SUBSTRING () и LENGTH ()
Для более простой и удобной обработки я буду использовать определяемую пользователем переменную сеанса, чтобы представить показанное здесь слово «ИЗМЕНЕНИЯ» с помощью команды SET:
На момент написания MySQL не поддерживает предложение CONNECT BY или псевдостолбец ROWNUM, найденные в Oracle SQL. Поэтому, чтобы воспроизвести функциональность обеих конструкций, я реализовал несколько обходных путей.
К счастью, MySQL 8 представил предложение WITH (также называемое общим табличным выражением). Более того, предложение RECURSIVE WITH играет важную роль в имитации псевдостолбца ROWNUM, в этом конкретном контексте, из-за его рекурсивного характера. Поэтому, если вы планируете следовать примерам запросов, вам понадобится MySQL 8 или выше.
На время этого сообщения и последующих запросов предположим, что рекурсивный CTE, rownum_generator, (показанный ниже) находится в области действия:
Получение всех строк из CTE rownum_generator возвращает увеличивающийся набор целочисленных значений, заканчивающихся на 7, что является длиной целевой строки «CHANGES»:
В этой статье я буду использовать еще две функции MySQL: SUBSTRING() и LENGTH(). SUBSTRING() принимает до 3-х параметров; 2 обязательных и 1 необязательный. Два обязательных параметра: target_string и start_position. Единственный необязательный параметр - это значение length.
SUBSTRING() возвращает часть строки в зависимости от поддерживающих параметров (или их отсутствия). То, как SUBSTRING() обрабатывается с помощью рекурсивного CTE, определяет уникальные выходные данные.
По теме: Прочтите сообщение Символьная функция Oracle SQL SUBSTR () - Среднее кросс-пост (внешняя ссылка) для получения дополнительной информации о аналогичной символьной функции Oracle.
Рекурсивные CTE и SUBSTRING (): по 1 символу в строке для каждого символа.
При просмотре этого первого запроса цель состоит в том, чтобы вернуть по одному символу в строке для всех символов в слове «ИЗМЕНЕНИЯ».
Параметр start_position является динамическим на основе столбца CTE rownum_generator, rownum. Поэтому каждое исходное положение меняется. Кроме того, параметр length указывает только 1 символ, который должен быть извлечен для этого варианта отображения. Более наглядный пример можно реализовать, возвращая значение rownum в интервале каждой строки в результатах запроса:
Рекурсивные CTE и SUBSTRING (): по 1 символу в строке для каждого символа, начиная с конца строки.
В следующем примере я зациклился, пока я снова не вернул движущееся значение rownum вместе с вызовом функции SUBSTRING() в результатах запроса. Здесь я запрашиваю 1 букву из целевой строки в каждой строке, но начинаю извлечение с конца строки «CHANGES» из-за отрицательного числа rownum (показано в последующем запросе):
Рекурсивные CTE и SUBSTRING (): динамическое количество символов в строке, начиная с конца строки
Приведенный ниже экран вывода очень похож на предыдущий, за исключением того, что когда из target_string был извлечен только 1 символ, теперь я извлекаю динамическое количество символов на основе любого значения rownum для этой конкретной строки:
Опять же, установка для параметра start_position значения выражения rownum * - 1 начинает извлечение в конце target_string, поскольку оно отрицательное. Но вместо извлечения только 1 символа количество символов определяется значением столбца rownum. Оказывается, это то же самое число, которое необходимо для извлечения оставшейся части строки «CHANGES».
Я взял за правило писать еженедельное электронное письмо о SQL / PHP, которое я изучаю и которое меня интересует. Если это похоже на то, частью чего вы хотели бы стать, используйте эту подписку (внешняя ссылка) сформировать и подписаться. Спасибо!
Рекурсивные CTE и SUBSTRING (): динамическое количество символов в строке, начиная с 1-го символа.
Самый простой пример, на мой взгляд, показан в следующих результатах запроса. Я просто устанавливаю для параметра start_position значение 1, а для параметра length значение столбца rownum.
Например, при просмотре последней строки в результатах запроса возвращается слово «ИЗМЕНЕНИЯ». Почему?
Имея start_position, равное 1, и значение столбца rownum, равное 7 для значения параметра length (на данном этапе выполнения), все 7 символы текста возвращаются SUBSTRING(). Достаточно просто, правда?
Рекурсивные CTE и SUBSTRING (): все символы до последнего символа
В тех случаях, когда параметр length не предоставлен для SUBSTRING(), функция возвращает оставшееся количество символов, найденных в target_string из start_position и Именно так работает следующий пример запроса с использованием значения столбца rownum, чтобы указать, где должно начинаться извлечение:
Рекурсивные CTE и SUBSTRING (): заменяйте отсутствующие символы пробелом
Я должен быть здесь откровенен. Последние 2 примера доставили мне наибольшее горе из всех запросов. Я не уверен, что смог бы решить эти две последние загадки без использования CASE выражения, хотя автор YouTube не использовал CASE в продемонстрированных примерах.
По теме: Прочтите сообщение Функция динамического RPAD () с использованием выражения CASE - пример Oracle SQL (внешняя ссылка), где вы найдете еще один пример динамического CASE выражения я много позаимствовал у :).
Похоже: Вы не знакомы с выражением CASE? Не беспокоиться. Я написал 2 сообщения, о которых вы рассказали. См. Простое выражение CASE MySQL - с примерами и Выражение CASE поиска MySQL - с примерами (внешняя ссылка), чтобы узнать больше.
Ниже приведены результаты запроса:
С общей целью дополнить выражение SUBSTRING(@changes, 1, rownum) числом пробелов, равным и не превышающим длину 7 (длина строки "CHANGES"), я использую динамический CASE выражение для 2-го LPAD() параметра. Какова конечная длина строки, которая будет дополнена после применения всего заполнения.
Давайте выполним запрос и отобразим значение выражения CASE вместе с выходными данными функции LPAD() для большей ясности:
Обратите внимание, что значение столбца cse_lt согласовано с 7. Это желаемая длина строки после того, как LPAD() применяет какое-либо заполнение. Таким образом, правильное количество пробелов добавляется к возвращаемому значению SUSTRING() в зависимости от того, какая часть извлекается на основе значения столбца rownum.
Переходя к последнему примеру запроса, он больше похож на предыдущий, за исключением того, что для параметра SUBSTRING() start используется 1, и мы извлекаем количество символов, равное rownum value параметр length.
Рекурсивные CTE и SUBSTRING (): отображаются все выходные данные.
Я предоставил все нижеприведенные решения одним запросом:
Надеюсь, вам понравился этот пост. Я знаю, что мне было очень весело изучать различные паттерны и писать о них. Попробуйте свои собственные и дайте мне знать о них в комментариях. Спасибо за чтение
Если у вас есть вопросы или вы видите ошибки в коде, оставьте комментарий в разделе комментариев. Поступая так, я могу продолжать публиковать ценные и точные сообщения в блоге.
Нравится то, что вы прочитали? Видите что-нибудь неправильное? Прокомментируйте, пожалуйста, ниже и спасибо за чтение !!!
Призыв к действию!
Спасибо, что нашли время прочитать этот пост. Я искренне надеюсь, что вы открыли для себя что-то интересное и поучительное. Пожалуйста, поделитесь своими выводами здесь с кем-нибудь, кого вы знаете, кто тоже получит от этого такую же ценность.
Посетите страницу Портфолио-проекты, чтобы увидеть сообщения в блоге / технические статьи, которые я написал для клиентов.
Горячий черный кофе так хорош! Купи мне чашку!
Чтобы получать уведомления по электронной почте (Никогда не спамить) от этого блога (Проза цифровой совы) о последних публикациях в блоге по мере их публикации, пожалуйста, подпишитесь (по собственному желанию), нажав кнопку Нажмите, чтобы подписаться! На боковой панели на домашняя страница! (Не стесняйтесь в любое время просмотреть страницу политики конфиденциальности Digital Owl's Prose по любым вопросам, которые могут у вас возникнуть: обновления по электронной почте, подписка, отказ, контактные формы и т. Д.)
Обязательно посетите страницу Best Of, где собраны мои лучшие сообщения в блоге.
Джош Отвелл хочет учиться и расти как разработчик SQL и блогер. Другие любимые занятия находят его, уткнувшись носом в хорошую книгу, статью или командную строку Linux. Среди них он разделяет любовь к настольным ролевым играм, чтению фэнтезийных романов и проведению времени с женой и двумя дочерьми.
Отказ от ответственности: примеры, представленные в этом посте, представляют собой гипотетические идеи о том, как достичь аналогичных результатов. Это не самое лучшее решение. Большинство, если не все, из представленных примеров выполняются в среде рабочей станции для личного развития / обучения и не должны считаться производственными или готовыми к использованию. Ваши конкретные цели и потребности могут отличаться. Используйте те методы, которые лучше всего подходят для ваших нужд и целей. Мнения мои собственные.
Первоначально опубликовано на https://joshuaotwell.com 14 июля 2021 г.
