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

转载请注明出处: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));

#####    现在是 2018-11-18 16:22:45 ,我得准备下一篇博客了,各位下午好~![smile](https://img2018.cnblogs.com/blog/1161789/201811/1161789-20181118162251324-16542013.png)
上一篇:mysql 整数类型 数值类型 tinyint


下一篇:取计算机特征码(网卡MAC、硬盘序列号、CPU ID、BIOS编号)