行转列方法 及 pivot函数使用问题

数据库 专栏收录该内容
7 篇文章 1 订阅

行转列方法

  如果有这样的需求,数据库插入的有A表这样的结果,展示要变成B表这样的结果(下边的这个图是摘自别人的,我主要说pivot函数那种方式

这里写图片描述

对于这样的需求即需要行转列了。

1、第一种即是大家常见的,oracle、mysql都适用。这种方法也是别人的,我主要说后边的pivot函数那种方式

select [品号],
sum(case when [日期]='06-08' then [数量] else 0 end) as [06-08],
sum(case when [日期]='06-09' then [数量] else 0 end) as [06-09],
sum(case when [日期]='06-10' then [数量] else 0 end) as [06-10],
sum(case when [日期]='06-11' then [数量] else 0 end) as [06-11],
sum(case when [日期]='06-12' then [数量] else 0 end) as [06-12],
sum(case when [日期]='06-13' then [数量] else 0 end) as [06-13]
from tbA
group by [品号]

  通过以上方式可以实现上边A表------>B表的过程。
  速度我测试过,对于130多万的数据,假如来行转列的话,oracle数据库消耗了0.7s,总体来说还能满足我的需求,

2、第二种方式,pivot函数。仅适用oracle

这个函数是oracle 11g的时候新加的,用起来比较方便,同样是上边的A表-----B表的转变,sql如下:
select * from tbA 
pivot(
   sum(数量) for 日期  in ('06-08','06-09','06-10','06-11','06-12')
)

  的确少写了很多东西,但是!也是有好几个注意点的。

使用行转列注意的地方

1、不管第一种还是第二种,转成列的那个日期字段,即:

这里写图片描述
  这个列,无法写活,我说的那种活是,通过内套一个sql,比如像pivot函数那种行转列方式,我们肯定会想到用下边的这种方式来写:

select * from tbA 
pivot(
   sum(数量) for 日期  in (select 日期 from tbA)
)

  但是这么写直接报错!!!。必须写死才可以,好了,可能还会有人使用mybatis,打算在xml里,先把里边的查询出来的日期查完,拼成一个类似'06-08','06-09','06-10','06-11','06-12' 字符串,再塞进去,这样应该没问题了吧,然后如下这样下:

<select id="selectRowToCol" parameterType="java.util.Map" resultType="java.util.Map">
    select * from tbA 
pivot(
   sum(数量) for 日期  in (#{dateStr})
)
  </select>

  但是这么写还是直接报错!!!
有一种方法是,这里不要使用#{} ,而使用${} 即可。这个我经常用mybatis,竟然一下子忘了这个,被阅读的小伙伴提醒了,感谢大神的提醒。
  我以前是用的下边的方法,感觉脸红了,如下:
我使用的方法是最传统的JDBC那种方式,把sql拼好,然后:

Connection conn = null;
		ResultSet resultSet = null;
		try{
			// 1.加载驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2.获得数据库的连接
			conn = DriverManager.getConnection(URL, UNAME, PWD);
			conn.setAutoCommit(false);//事物开始
			Statement st = conn.createStatement();
			//下边的这个sql,提前拼好,然后传进去
			resultSet = st.executeQuery(sql);
			return resultSet;
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(!conn.isClosed()){
                    conn.close();
                }
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

  这么写才可以查询到,这俩行转列,想写活这个列的方式,我就只知道这一种。

2、对于pivot函数还有几个注意的地方就是,以下边这个sql为例:

select * from tbA 
pivot(
   sum(数量) for 日期  in ('日期1','日期2','日期3','日期4')
)

 (1) [数量] 和 [日期]两个字段,必须对应于select *中的字段名,如果是别名,就必须跟别名对应。
 (2) forin 这两个是配合使用,你无法将in 改为你想要的 like 或者其他的语法。安安稳稳的照人家要求写吧。

  • 7
    点赞
  • 4
    评论
  • 33
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

茁壮成长的凌大大

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值