MySQL递归实现单列分列成多行 mysql递归查询语句

现实中经常遇到很多大聪明喜欢把一些汇总的数据给你，然后又需要里面明细字段的数据操作。比如下面这种，把四个国家的人员每个国家放在一起，然后告诉你他需要每个人员的各项数据。

现实情况不是这个4行汇总，而是N行的汇总，每一行数据量不一样，你不可能一行一行去分列然后在复制粘贴在一起吧。这一篇就是来介绍一下MySQL里面如果通过递归快速实现这类数据分操作。

MySQL里面不知道何时开始有递归这个功能的，我也不想去查资料了，反正8.0后都可以使用了，目的是解决问题。通过RECURSIVE语句来实现，整体思路大概如下↓

WITH recursive cte AS(
  SELECT 1 as n
  UNION ALL
  SELECT n+1 FROM cte WHERE n<5
)
SELECT * FROM cte;

通过WITH RECURSIVE创建一个递归，第一个SELECT是对原始数据的操作，然后通过UNION ALL连接下一个SELECT查询，而这个查询就是对上面语句结果的循环执行了，知道WHERE条件满足后结束循环。最后在下面通过一个新的查询来对这个递归结果进行查询就行了。

上面这个实例是先查询一条数据1，然后通过循环，每次加1，结束条件是n大于等于5后，所有最后是会生成5行数据，结果如下↓

下面来解决我们最开始提出的问题。思路是，我们可以发现，每个人的名字使用顿号分格的，我们只需要按顿号进行多次分列，每一次循环结果放在新的一行，循环结束了就形成了每个人单独一行的结果了。

我们每次循环只按顿号分列成两列，第一列是第一个顿号前的名字，剩下的是第二列。这里使用substr函数来进行数据的分列是最有效的，这个函数有3个参数，第一个是要分列的字段，第二个是开始位置，第三个是结束位置，因为每个人名字长度不一样，我们还需要使用instr函数来找出结束位置。执行一次的SQL语句和结果如下↓

SELECT id,county,substr(`name`, 1, instr(`name`, '、')-1), substr(concat(`name`,'、'), instr(`name`, '、')+1)
  FROM threekingdoms t

从结果可以看到，我们把每个国家第一个人名分列出来了，但是这里第四个群雄没有出现吕布的名字，因为原始数据里面吕布只有一个人，没有顿号进行分隔，我们substr加入了第三个参数，这样结果就没有了。但是我们通过在剩下一列加入一个顿号，在下一轮就可以得到这个名字了。然后我们按照这个语法就行循环就行了，结束条件是所有字段都没有顿号，最后再进行对循环结果的查询就行了，SQL语句和结果如下↓

WITH RECURSIVE t(id,county,`name`,str) AS (
  SELECT id,county,substr(`name`, 1, instr(`name`, '、')-1), substr(concat(`name`,'、'), instr(`name`, '、')+1)
  FROM threekingdoms t
  UNION ALL
  SELECT id, county,substr(str, 1, instr(str, '、')-1), substr(str, instr(str, '、')+1)
  FROM t WHERE instr(str, '、')>0 
)
SELECT id, county, `name`
FROM t
WHERE length(name)>1
ORDER BY id;

好了，这就完美实现了我们的需求，最后我们再来解决一个类似的问题。需求如下表，我们有四个名字，最后一列是每个人的计数，我们需要按计数来把每个人分成N行，比如Kobe是2，我们就需要分成两行Kobe来。

思路还是使用递归来操作，按照每个人的计数，每个人从1开始计数，每次递归+1，不满足条件就停止，比上面那个需求还要简单一些，SQL语句和结果如下↓

WITH recursive cte AS(
  SELECT id,`name`,t_count, 1 AS cnt FROM ungroup_test
  UNION ALL
  SELECT id,`name`,t_count, cnt+1 AS cnt FROM cte WHERE cte.t_count >= cnt+1
  )
SELECT id,`name`,t_count FROM cte ORDER BY id

到此，先结束了，后续应该还会有一些其他用法遇到的时候再分享，后续再分享一些其他SQL的面试题。

End