loading..
Русский    English
01:48
листать

Рекурсивные СТЕ стр. 3

Сначала напишем два простых  CTE (общее табличное выражение) позволяет в рамках запроса задать таблицу, на которую можно многократно ссылаться.CTE, которые формируют наш тестовый пример и определяют ASCII-код первой буквы алфавита (A) - не писать же константу. :-) Далее последует анкорная часть, которая выполняет ранее описанную операцию приведения всего текста к нижнему регистру с заглавной первой буквой. Здесь же выполним замену символа с кодом code и предшествующим ему пробелом на... него же. Пусть вас не смущает такая, казалось бы, бесполезная замена. Дело в том, что для регистронезависимых баз данных символы 'a' и 'A' не различаются. Давайте пока на этом остановимся и посмотрим результат.

Консоль
Выполнить
  1. WITH NM(name) AS
  2. (SELECT 'ALfa and omegA' AS name
  3. UNION ALL SELECT 'alfa beta gamma    zeta'
  4. UNION ALL SELECT 'KSI PSI'
  5. UNION ALL SELECT 'delta'
  6. ),
  7. Ascii_code AS(
  8. SELECT  ASCII('A') AS code
  9. ),
  10. Repl(name, code, rep) AS
  11. (SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) +
  12. LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep
  13. FROM Ascii_code, NM
  14. )
  15. SELECT name, rep FROM Repl;

 

name     rep       
ALfa and omegA     Alfa And omega       
alfa beta gamma zeta Alfa beta gamma    zeta       
KSI PSI     Ksi psi       
delta     Delta    

Добавим, наконец, рекурсивную часть, в которой мы выполним замену буквы с кодом code+1. Рекурсия будет продолжаться до тех пор, пока не будет нарушено условие code < ASCII('Z'), т.е. пока мы не переберем все буквы.

Что же мы получим на выходе? К строкам, которые были получены в результате выполнения анкорной части, на каждой итерации будут добавлены (UNION ALL) те же строки с заменой очередной буквы. Отметим большой объем результата при использовании данного метода; в нашем случае это 4х26 = 104 строки. Из этого множества строк нас интересуют только те, которые получены в результате последней итерации, т.е. когда были выполнены все замены. Этой последней итерации соответствует условие code = ASCII('Z'), которое и используется в финальном запросе:

Консоль
Выполнить
  1. WITH NM(name) AS
  2. (SELECT 'ALfa and omegA' AS name
  3. UNION ALL SELECT 'alfa beta gamma    zeta'
  4. UNION ALL SELECT 'KSI PSI'
  5. UNION ALL SELECT 'delta'
  6. ),
  7. Ascii_code AS(
  8. SELECT  ASCII('A') AS code
  9. ),
  10. Repl(name, code, rep) AS
  11. (SELECT name, code, REPLACE(UPPER(LEFT(name, 1)) +
  12. LOWER(SUBSTRING(name, 2, LEN(name) - 1)),' '+CHAR(code),' '+CHAR(code)) rep
  13. FROM Ascii_code, NM
  14. UNION ALL
  15. SELECT name, code+1 code, REPLACE(rep,' ' + CHAR(code+1), ' ' + char(code + 1)) rep
  16. FROM Repl
  17. WHERE code < ASCII('Z')
  18. )
  19. SELECT name, rep FROM Repl WHERE code=ASCII('Z');

Я хотел бы предостеречь вас от чрезмерного увлечения рекурсивными CTE, поскольку они зачастую проигрывают в производительности "традиционным" методам. Я не буду далеко ходить за примерами и сравню два представленных здесь метода. Увеличив количество обрабатываемых строк до 10000, я получил такое время использования CPU:

метод на основе REPLACE: 842 ms

рекурсивный метод: 6615 ms

Безусловно, есть задачи, которые нельзя решить непроцедурно в рамках стандарта SQL-92. В этих случаях использование рекурсивных CTE вполне обоснованно. В остальных случаях я бы рекомендовал выполнять тесты производительности для альтернативных решений.

Кстати, в Oracle и PostgreSQL есть встроенная функция INITCAP, которая решает данную задачу:

  1. SELECT INITCAP(name)
  2.   FROM
  3.    (SELECT 'ALfa and omegA' AS name
  4.      UNION ALL SELECT 'alfa beta gamma    zeta'
  5.      UNION ALL SELECT 'KSI PSI'
  6.      UNION ALL SELECT 'delta'
  7.  ) X;

Вы можете использовать консоль, чтобы убедиться в этом.

Рекомендуемые упражнения: 106

Bookmark and Share
Страницы: 1 2 3
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
https://exchangesumo.com/obmen/to/CNTEUR/
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100