递归cte

CTE（Common Table Expression，公用表表达式）是 SQL 中一种临时的、一次性的查询结果集，可在单个 SQL 语句内复用，能简化复杂嵌套查询的逻辑结构

lc3374

递归CTE生成数字序列，先按空格拆分文本为单词、再按连字符拆分单词为片段，对每个片段首字母大写后逐级重组，最终输出格式化后的文本

WITH RECURSIVE numbers AS (

SELECT 1 as n

UNION ALL

SELECT n + 1 FROM numbers WHERE n < 20

),

split_words AS (

SELECT

uc.content_id,

uc.content_text as original_text,

n.n as word_position,

SUBSTRING_INDEX(SUBSTRING_INDEX(uc.content_text, ' ', n.n), ' ', -1) as word

FROM user_content uc

JOIN numbers n ON n.n <= (LENGTH(uc.content_text) - LENGTH(REPLACE(uc.content_text, ' ', '')) + 1)

),

split_hyphens AS (

SELECT

sw.content_id,

sw.original_text,

sw.word_position,

sw.word,

h.n as part_position,

SUBSTRING_INDEX(SUBSTRING_INDEX(sw.word, '-', h.n), '-', -1) as word_part

FROM split_words sw

JOIN numbers h ON h.n <= (LENGTH(sw.word) - LENGTH(REPLACE(sw.word, '-', '')) + 1)

),

capitalized_parts AS (

SELECT

content_id,

original_text,

word_position,

word,

part_position,

CONCAT(

UPPER(SUBSTRING(word_part, 1, 1)),

LOWER(SUBSTRING(word_part, 2))

) as capitalized_part

FROM split_hyphens

),

reassembled_words AS (

SELECT

content_id,

original_text,

word_position,

GROUP_CONCAT(capitalized_part ORDER BY part_position SEPARATOR '-') as new_word

FROM capitalized_parts

GROUP BY content_id, original_text, word_position

),

final_result AS (

SELECT

content_id,

original_text,

GROUP_CONCAT(new_word ORDER BY word_position SEPARATOR ' ') as converted_text

FROM reassembled_words

GROUP BY content_id, original_text

)

SELECT

content_id,

original_text,

converted_text

FROM final_result

ORDER BY content_id;