首页手记 oracle行转列、列转行、连续日期数字实现方式及my...

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

标签：

Oracle

转载请注明出处：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 DT
FROM DUAL CONNECT BY LEVEL <=(TO_DATE('2018-11-15','YYYY-MM-DD')-TO_DATE('2018-11-01','YYYY-MM-DD')+1)) mr
) t1
LEFT JOIN
(
SELECT SAP_ID,BEGIN_DATE,1 effective from DATA_EMP_ATTENDANCE
) t2
ON T2.SAP_ID = T1.SAP_ID AND T2.BEGIN_DATE = T1.DT
ORDER 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));
```