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;
