如何拆分VARCHAR2(4000)字符串为多行并统计单词重复次数

我有一个问题数据库，视图VW_ISSUE_REPORT中每个问题对应多个已关闭/未关闭的解决方案。需要提取所有方案内容，按原书写格式拆分为单个单词，统计整个视图中各单词的出现次数。且无法创建函数、存储过程等，仅能使用常规SELECT语句实现。

示例数据 #

执行查询：

SELECT * FROM VW_ISSUE_REPORT;

返回结果：

解决方案SQL #

使用递归CTE实现文本拆分，无需创建额外对象：

WITH WordSplit AS (
    -- 初始步骤：提取每行的第一个单词
    SELECT 
        LTRIM(RTRIM(SUBSTRING(Solution, 1, CHARINDEX(' ', Solution + ' ') - 1))) AS SolutionKeyWord,
        SUBSTRING(Solution, CHARINDEX(' ', Solution + ' ') + 1, LEN(Solution)) AS RemainingText
    FROM VW_ISSUE_REPORT
    WHERE Solution IS NOT NULL AND Solution <> ''
    
    UNION ALL
    
    -- 递归步骤：拆分剩余文本中的单词
    SELECT 
        LTRIM(RTRIM(SUBSTRING(RemainingText, 1, CHARINDEX(' ', RemainingText + ' ') - 1))) AS SolutionKeyWord,
        SUBSTRING(RemainingText, CHARINDEX(' ', RemainingText + ' ') + 1, LEN(RemainingText)) AS RemainingText
    FROM WordSplit
    WHERE RemainingText IS NOT NULL AND RemainingText <> ''
)
SELECT 
    SolutionKeyWord,
    COUNT(*) AS SolutionRepetitions
FROM WordSplit
GROUP BY SolutionKeyWord
ORDER BY SolutionRepetitions DESC, SolutionKeyWord;

执行结果 #

内容的提问来源于stack exchange，提问作者Favini