固定行转列比较容易实现.
但是动态行转列的需求,用SQL都难以实现,要不然就是超级复杂。
不过很多第三方工具都已经提供了很好的支持,不一定非得用SQL实现.比如帆软报表和Excel。
最近接到一个比较复杂的查询,并且要求动态行转列.
查询的SQL如下
由于这个SQL已然比较复杂,再加动态行转列,可读性几乎就没有了.
这个SQL查询的结果大致如下。
其中type可能是发布或者审核.
ts 表示月 日 和小时
cn表示模块类型
val表示数量.
将这个结果导入至Excel
选择插入,数据透视表
然后将ts 设置为列标签,这样 ts 就由行变成列显示
cn和type作为行标签.
val作为显示数值
然后选择视图->冻结窗格->冻结首列 方便观看数据
可以看到如下结果
完全符合动态行转列的需求,并且可以排序和筛选.太强大了.
但是动态行转列的需求,用SQL都难以实现,要不然就是超级复杂。
不过很多第三方工具都已经提供了很好的支持,不一定非得用SQL实现.比如帆软报表和Excel。
最近接到一个比较复杂的查询,并且要求动态行转列.
查询的SQL如下
- select type,ts,cn,cast(val as DECIMAL) val from (
- select
- starttime,'发布' type,base.ts,base.cn,ifnull(t1.val,0) val
- from
- (
- select
- t1.*,
- startdate+ interval id-1 hour starttime,
- startdate+ interval id hour endtime,
- date_format(startdate+ interval id-1 hour,'%m%d%H') ts,
- vars.*
- from nums ,(select ${starttime} startdate,${endtime} enddate) vars,
- (
- select 'mvbox_user.user_otherinfo' busidatatype,1 type,'内容审核.个人资料' cn union all
- select 'mvbox_user.user_baseinfo',1,'内容审核.个人喜好' union all
- select 'photo_album',1,'内容审核.相册信息' union all
- select 'photo_list',1,'内容审核.图片信息' union all
- select 'music_original',1,'内容审核.原唱信息' union all
- select 'music_cover',1,'内容审核.翻唱信息' union all
- select 'music_accompany',1,'内容审核.伴奏信息' union all
- select 'music_album',1,'内容审核.音乐专辑' union all
- select 'music_video',1,'内容审核.视频信息' union all
- select 'blog_album',1,'内容审核.日志与文章' union all
- select 'mvbox_user.user_baseinfo',2,'内容审核.MVBOX头像审核'
- ) t1
- where id<= TIMESTAMPDIFF(hour,startdate,enddate)
- order by busidatatype,type,starttime
- ) base left join
- (
- select busidatatype,type,date_format(createtime,'%m%d%H') ts,count(*) val from audit_obj_detail
- where createtime>=${starttime} and createtime<${endtime}
- and busitype = 'mvbox'
- group by busidatatype,type,date_format(createtime,'%m%d%H')
- ) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)
- group by base.busidatatype,base.type,base.ts,base.cn
- union all
- select
- starttime, '审核' type,base.ts,base.cn,ifnull(t1.val,0) val
- from
- (
- select
- t1.*,
- startdate+ interval id-1 hour starttime,
- startdate+ interval id hour endtime,
- date_format(startdate+ interval id-1 hour,'%m%d%H') ts,
- vars.*
- from nums ,(select ${starttime} startdate,${endtime} enddate) vars,
- (
- select 'mvbox_user.user_otherinfo' busidatatype,1 type,'内容审核.个人资料' cn union all
- select 'mvbox_user.user_baseinfo',1,'内容审核.个人喜好' union all
- select 'photo_album',1,'内容审核.相册信息' union all
- select 'photo_list',1,'内容审核.图片信息' union all
- select 'music_original',1,'内容审核.原唱信息' union all
- select 'music_cover',1,'内容审核.翻唱信息' union all
- select 'music_accompany',1,'内容审核.伴奏信息' union all
- select 'music_album',1,'内容审核.音乐专辑' union all
- select 'music_video',1,'内容审核.视频信息' union all
- select 'blog_album',1,'内容审核.日志与文章' union all
- select 'mvbox_user.user_baseinfo',2,'内容审核.MVBOX头像审核'
- ) t1
- where id<= TIMESTAMPDIFF(hour,startdate,enddate)
- order by busidatatype,type,starttime
- ) base left join
- (
- select busidatatype,type,date_format(AuditTime,'%m%d%H') ts,count(*) val from audit_obj_detail
- where AuditTime>=${starttime} and AuditTime<${endtime}
- and busitype = 'mvbox'
- group by busidatatype,type,date_format(AuditTime,'%m%d%H')
- ) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)
- group by base.busidatatype,base.type,base.ts,base.cn
- ) t1 ;
由于这个SQL已然比较复杂,再加动态行转列,可读性几乎就没有了.
这个SQL查询的结果大致如下。
![](http://img.blog.itpub.net/blog/attachment/201710/24/29254281_1508809477XmxG.png?x-oss-process=style/bb)
其中type可能是发布或者审核.
ts 表示月 日 和小时
cn表示模块类型
val表示数量.
将这个结果导入至Excel
选择插入,数据透视表
![](http://img.blog.itpub.net/blog/attachment/201710/24/29254281_1508809736wE1k.png?x-oss-process=style/bb)
然后将ts 设置为列标签,这样 ts 就由行变成列显示
cn和type作为行标签.
val作为显示数值
![](http://img.blog.itpub.net/blog/attachment/201710/24/29254281_1508809919ZPnT.png?x-oss-process=style/bb)
然后选择视图->冻结窗格->冻结首列 方便观看数据
可以看到如下结果
![](http://img.blog.itpub.net/blog/attachment/201710/24/29254281_1508810113AskJ.png?x-oss-process=style/bb)
完全符合动态行转列的需求,并且可以排序和筛选.太强大了.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2146291/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-2146291/