hive 行转列和列转行的方法_有趣的SQL(4) 行转列的复杂应用和优化思想

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

在之前的的系列文章中，有意思的SQL(3) 行转列，列转行和复制

已经给大家介绍了，行转列，列转行，复制等方法。

在这篇文章中，对其进行更深一层的应用。

需求如下，

有一个表如下图所示

现在有个需求是，我在画面输入

'10004,22744,24007' 要求返回对应的三行数据

当然有很多种解决方案，一类是，在前端或者server端 用语言把输入值分开，

还有一种是在数据库端，本文介绍的是在数据库端的方法，

当然还有 最简单方案如下

这种方案，也可以解决输入值有空格的情况

那这种方案的问题在哪呢？

先看下，这个表中是有索引的如下

运行了，下面的SQL 从执行计划中，可看出，走全表扫描了

也就是说表越大，有可能越慢！

写SQL 有几个阶段，第一个阶段是为了熟悉各种语法，和实现各种需求阶段

第二个阶段是，写出，更符合数据库特点，符合优化思路的SQL

上面的SQL 就是满足了需求，但是不符合优化。

如果写成如下

就可以有能满足需求，有能达到优化效果的SQL!

那现在的问题是，怎么把 '10004','22744','24007' 一行数据的变成3行数据的问题！

这样总算，文章又回到，本文开篇，没跑题~~

根据上篇文章，我们想把一行数据变成多行，我们需要复制

这里我用到了MySQL 8.0 开始支持的with 语句，

不支持的可以用临时表代替。

其中 num 是为了复制而弄的中间表，可以理解为，我们中学的几何题中，为了解决问题画的辅助线~~。

上面的SQL 已经达到复制效果了，但是显然，复制的有点多了，我们只需要3行数据，但复制了5行，显然不行，我们观察下，结果发现我们如果复制的行数是逗号的个数+1 就可以了！

那怎么表达呢，我们用原来的length-去掉逗号后length +1 就可以

从上图所示，可以看出，这样就复制了我们想要的行数。

现在就剩下，怎么截取的问题，我们想要的是第一行截取第一个，第二行第二个，第n行第n个 。。。

MySQL 8.0给我们提供了regexp_substr这样的强大的函数，专门解决这样的问题

当然还有别的解决方案，为了代码的简单，我就用了这个函数

这样我们就解决了，行转列的问题，剩下的是把这部分带到原来的SQL 中，

执行计划如下，执行计划稍微有点复杂，初学者比较难懂

下面是运行结果，还是符合需求的

为了，给大家一个直观的感觉，我用大表salaries 表替换了原来的dept_emp表

然后运行SQL如下 运行了3.8 秒 !

用修改之后代码 行数太多，我把数据删了保留时间如下 0.01秒

用倍数的话。。。

本文，通过一个简单案例，给大家讲述了，SQL开发过程中的几个方法和优化思路，我们不仅仅开发过程中想到怎么解决需求，若果想进一步，就需要考虑性能问题

谢谢大家~

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化