oracle行转列、列转行、连续日期数字实现方式及mybatis下实现方式
2019-11-29

转载请注明出处:https://www.cnblogs.com/funnyzpc/p/9977591.html

九月份复习,十月份考试,十月底一直没法收心,赶在十一初 由于不可抗拒的原因又不得不重新找工作就;欸~,又是一番折腾,从入职到现在,可又没法闲下来了...

这次就简单介绍下oracle数据库下如何实现行转列、列转行及此在mybatis中的实现方式,就具体用法我就不详细说了,主要介绍下实战中所碰到的坑~

行转列大致的实现方式大致有三种

使用条件判断(case when...)+聚合函数方式

这种方式sql难度低,但是容易给DB造成较大的开销,毕竟每个最终的列的值都是一个聚合函数的值,同时非聚合列也要随聚合列而定,大多数情况下可能需要将多个子查询连表查;至于在mybatis中的时候就非常简单了,这里就不再缀诉哈~使用pivot函数方式

此种方式有一个缺点是:一次查询只能对一个列的数据进行拆分(成多列),如需对多列拆分,则可行的方式是做多个查询,一个查询拆分一列(同时保留连接字段),同时,这里需要主要的是 在 pivot 下的in中不可以是一个子查询,记得当时调试的时候怎么调试就是报错,后来翻了翻英文网站的说明,给的大致意思是这个pivot内拆分的目标字段不可以是一个子查询,只能是写死的列 ,其实也不是不可以,只是官方给出的说法是:要实现动态列,只能使用xml的方式(说了等于没说)。 ok,待SQL调试完毕,copy到应用中使用的时候还是会报错,这个时候就需要注意到mybatis的一些基本约定,就是传值方式,mybatis的传值方式大致有#{value}和${value}两种方式,#符号定义的值其实是将整个值对象交给DBMS去处理,而$符号定义的值是将值直接放入到语句内,对DBMS来说,后者更相当于一个定值,所以将povit应用在mybatis中需要使用$的值定义方式(仅在in内这样使用,pivot外视业务情况而定)。 同时,因为使用$符号定义值的方式不时mybatis并不会将传入的值作为一个String字符来处理,如果是日期及其他类型尽量使用string的方式将值传入,拆分的值若是日期类型的需要使用to_date()函数或者_to_char()_函数进转换才是,如果目标值就是String类型且一定要将整个声明值使用单引号引起来("#{value}"),另外,拆分的目标列也是可以定义别名的,不然DB又会给出pivot内不能使用动态语句的错误,需要使用CDATA标签做xml转义,最终的样子大致就是这样 : in (to_date("${value}","b") as <![CDATA[${key}]],to_date("${value}","b") as <![CDATA[${key}]]>)使用wm_concat窗口函数的方式

这种方式其实很容易将sql写的很复杂,主要有两个方面:一个是wm_concat 函数一定要指定partition by和order by的字段,就是组内分组和组内排序方式,不然最终的结果数据混乱不堪(除非这对你的业务不重要),另一个原因是窗口函数本身是不会聚合行,所以,组内拼接也是顺序拼接,所以需要做的就是将非结果行去掉才是,这个时候就需要使用另一个窗口函数rank(),这个函数是对组内做排序,由于目标是实现行转列,所以此时就需要将rank()的排序方式改为desc,而后再做一个子查询将rank的值为非1的全去掉。 由于使用wm_concat的结果是单列,需要此时需要根据逗号做分隔,截取为指定的列,wm_concat函数使用起来并不难,但是面对实际业务的时候,若是大数据量就得慎重咯,因为函数使用的越多就越容易造成DB的开销,这个是不容忽视的。 wm_concat在mybatis中的使用并没有障碍,需要注意的细节是拼接字段可能不是varchar类型,而是blob(大字段)类型,需要用to_char()函数做转换,同时wm_concat函数只能按照逗号进行内容拼接,如果字符包含逗号,建议将wm_concat内的源字段拼接一个唯一字符。

实现列转行的方式

使用unpivot函数的方式

此种方式同以上的pivot的方式相反,不过好处是他不会有行转列的单列问题,至于在mybatis中的使用,建议参照以上pivot的方式使用_regexp_substr_正则函数的方式

其实这个函数的说的意义并不大,因为regexp_substr函数在拆分十行数据的时候DB的开销就显现出来了,如果不得已要用的话首先推荐使用程序来处理,如果使用数据库处理,建议将目标数据拆分多个组来做,建议不要超过十个,而且拆分的列的数据复杂度不能太高(字符太长,正则太复杂),在mybatis下的使用中只需要注意下目标列的类型,必要时使用to_char函数进行转换才是~

连续日期的实现方式

使用level+connect by+rownum实现

相信大家已经猜出一部分了,这里简要说下这三个东东大概是干什么:level实现级的序号增加,connect by 实现递归,rownum则配合运算结果加减,以下就给出具体的SQL,可直接执行。

SELECT TO_DATE("2018-10-28","YYYY-MM-DD")+ROWNUM - 1 DT FROM DUAL CONNECT BY LEVEL <=(TO_DATE("2018-11-20","YYYY-MM-DD")-TO_DATE("2018-10-28","YYYY-MM-DD")+1)

貌似不使用rownum也是可以的,各位可以尝试下哈~

#### 最后,原本在写博客前在个人电脑中跑一个oracle的,实际安装的过程中发现oracle的安装包实在是太大了,许久不安装,安装过程难免也会出现各种问题,遂~就放弃了,改天我会尽量将语句都放出来,以飨广大读者哈~,至于行转列列转行的实现方式就给个粗糙的sql大家尝试着看哈~~

-- 行转列SELECT * from(SELECT tt1.SAP_ID,TT1.dt,TT1.EFF from (SELECT t1.SAP_ID,T1.DT,nvl(T2.EFFECTIVE,0) eff from(SELECT A1.SAP_ID,mr.dt from(SELECT DISTINCT SAP_ID from DATA_EMP_ATTENDANCE) a1,(SELECT TO_DATE("2018-11-01","YYYY-MM-DD")+ROWNUM - 1 DTFROM DUAL CONNECT BY LEVEL <=(TO_DATE("2018-11-15","YYYY-MM-DD")-TO_DATE("2018-11-01","YYYY-MM-DD")+1)) mr) t1LEFT JOIN(SELECT SAP_ID,BEGIN_DATE,1 effective from DATA_EMP_ATTENDANCE) t2ON T2.SAP_ID = T1.SAP_ID AND T2.BEGIN_DATE = T1.DTORDER BY t1.dt DESC) tt1) pivot(max(eff) for dt in (to_date("2018-11-05","yyyy-mm-dd") d1,to_date("2018-11-12","yyyy-mm-dd") d2,to_date("2018-11-12","yyyy-mm-dd") d3));```

现在是 2018-11-18 16:22:45 ,我得准备下一篇博客了,各位下午好~