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

导读

本文节选自松华老师《SQL优化专栏》

想阅读更多内容请点击订阅专栏

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

在之前的的系列文章：有趣的SQL(三)行转列，列转行和复制

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

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

需求如下，

有一个表如下图所示

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

'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优化方面疑问需要交流的，请加入QQ群(579036588)，并@骑龟的兔子 就可与我联系

点击下图小程序订阅 松华老师的《SQL优化专栏》 get更多优化技能

END

---

知 数 堂

这里有好课，为职场助攻

免费兑换知数堂的课程和周边，戳此了解

扫码加入的MySQL技术Q群

  (群号：579036588)