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 ,我得准备下一篇博客了,各位下午好~

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

10分钟学会理解和解决MySQL乱码问题

本文将详细介绍MySQL乱码的成因和具体的解决方案。在阅读本文之前,强烈建议对字符集编码概念还比较模糊的同学 阅读下博主之前对相关概念的一篇科普:十分钟搞清字符...

2938
来自专栏yukong的小专栏

【ssm个人博客项目实战04】mybatis实现博客类别的增删改查分页

在前面一节我们已经完成后台管理界面的一个实现,在这一节我完成其中的博客类别管理中如果分页显示博客类别信息。

1445
来自专栏运维技术迷

连仕彤博客[Python笔记] Python 一次性替换多个字符

在一个字符串中,一次性替换多个字符串,可以使用字符串的replace函数来处理(目前我想到的是这个方法,大神请指教!) 要求 把INSERT语句中的riqi、b...

3535
来自专栏禹都一只猫博客

Python的flask:models.py来创建mysql数据库

9529
来自专栏西安-晁州

mysql随笔

Mysql学习笔记 1、操作数据库 use dataBaseName  //使用数据库 show databases   //显示所有数据库 show tabl...

1980
来自专栏前端儿

在PHP中使用MySQL Mysqli操作数据库 ,以及类操作方法

先来操作函数部分,普遍的MySQL 函数方法,但随着PHP5的发展,有些函数使用的要求加重了,有些则将废弃不用,有些则参数必填...

3083
来自专栏java系列博客

单例模式的各种实现

1936
来自专栏更流畅、简洁的软件开发方式

分页控件之分页算法 —— for SQL Server 版。

上两篇随笔: 我的分页控件(未完,待续)——控件件介绍及思路 我自己写的一个分页控件(源码和演示代码)PostBack分页版 for vs2003、SQL ...

2199
来自专栏抠抠空间

Django之ORM其他骚操作

Django ORM执行原生SQL # extra # 在QuerySet的基础上继续执行子语句 # extra(self, select=None, wher...

2895
来自专栏java系列博客

oracle 中start with ... connect by prior 子句的用法

1702

扫码关注云+社区