行转列方法
如果有这样的需求,数据库插入的有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) for
和 in
这两个是配合使用,你无法将in
改为你想要的 like
或者其他的语法。安安稳稳的照人家要求写吧。