ITPUB首页 |  论坛 | 认证专区 | 博客 登录 | 注册

慎終如始,則無敗事

道學無止境,誠然心地行,超然於物外,絕待不歸真。

  • 博客访问: 5507428
  • 博文数量: 589
  • 用 户 组: 普通用户
  • 注册时间: 2013-10-19 21:50
个人简介

高山仰止http://my.csdn.net/wzy0623王工的博客 参加炼数成金培训输入 Dataguru培训优惠码 DR50,报名立减50%固定学费。 http://edu.dataguru.cn/

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(589)

文章存档

2017年(61)

2016年(104)

2015年(144)

2014年(220)

2013年(60)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
友情链接
  • ·翟永超
  • ·开涛
  • ·chenssy
  • ·算法和多线程
  • ·ELK大牛
  • ·江南白衣
  • ·汤雪华
  • ·大飞的博客

分类: MySQL

固定行转列比较容易实现.
但是动态行转列的需求,用SQL都难以实现,要不然就是超级复杂。
不过很多第三方工具都已经提供了很好的支持,不一定非得用SQL实现.比如帆软报表和Excel。

最近接到一个比较复杂的查询,并且要求动态行转列.

查询的SQL如下
  1. select type,ts,cn,cast(val as DECIMAL) val from (  
  2. select    
  3. starttime,'发布' type,base.ts,base.cn,ifnull(t1.val,0) val  
  4. from   
  5. (  
  6.     select    
  7.         t1.*,  
  8.         startdate+ interval id-1 hour starttime,  
  9.         startdate+ interval id hour endtime,  
  10.         date_format(startdate+ interval id-1 hour,'%m%d%H') ts,  
  11.         vars.*  
  12.     from nums ,(select ${starttime} startdate,${endtime} enddate) vars,  
  13.     (  
  14.         select 'mvbox_user.user_otherinfo' busidatatype,1 type,'内容审核.个人资料' cn union all   
  15.         select 'mvbox_user.user_baseinfo',1,'内容审核.个人喜好' union all   
  16.         select 'photo_album',1,'内容审核.相册信息' union all   
  17.         select 'photo_list',1,'内容审核.图片信息' union all   
  18.         select 'music_original',1,'内容审核.原唱信息' union all   
  19.         select 'music_cover',1,'内容审核.翻唱信息' union all   
  20.         select 'music_accompany',1,'内容审核.伴奏信息' union all   
  21.         select 'music_album',1,'内容审核.音乐专辑' union all   
  22.         select 'music_video',1,'内容审核.视频信息' union all   
  23.         select 'blog_album',1,'内容审核.日志与文章' union all   
  24.         select 'mvbox_user.user_baseinfo',2,'内容审核.MVBOX头像审核'  
  25.     ) t1  
  26.     where id<= TIMESTAMPDIFF(hour,startdate,enddate)  
  27.     order by busidatatype,type,starttime  
  28. ) base left join   
  29. (  
  30.     select busidatatype,type,date_format(createtime,'%m%d%H') ts,count(*) val from  audit_obj_detail   
  31.     where createtime>=${starttime} and createtime<${endtime}   
  32.     and busitype = 'mvbox'  
  33.     group by busidatatype,type,date_format(createtime,'%m%d%H')  
  34. ) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)  
  35. group by base.busidatatype,base.type,base.ts,base.cn    
  36. union all  
  37. select    
  38. starttime, '审核' type,base.ts,base.cn,ifnull(t1.val,0) val  
  39. from   
  40. (  
  41.     select    
  42.         t1.*,  
  43.         startdate+ interval id-1 hour starttime,  
  44.         startdate+ interval id hour endtime,  
  45.          date_format(startdate+ interval id-1 hour,'%m%d%H') ts,  
  46.         vars.*  
  47.     from nums ,(select ${starttime} startdate,${endtime} enddate) vars,  
  48.     (  
  49.         select 'mvbox_user.user_otherinfo' busidatatype,1 type,'内容审核.个人资料' cn union all   
  50.         select 'mvbox_user.user_baseinfo',1,'内容审核.个人喜好' union all   
  51.         select 'photo_album',1,'内容审核.相册信息' union all   
  52.         select 'photo_list',1,'内容审核.图片信息' union all   
  53.         select 'music_original',1,'内容审核.原唱信息' union all   
  54.         select 'music_cover',1,'内容审核.翻唱信息' union all   
  55.         select 'music_accompany',1,'内容审核.伴奏信息' union all   
  56.         select 'music_album',1,'内容审核.音乐专辑' union all   
  57.         select 'music_video',1,'内容审核.视频信息' union all   
  58.         select 'blog_album',1,'内容审核.日志与文章' union all   
  59.         select 'mvbox_user.user_baseinfo',2,'内容审核.MVBOX头像审核'  
  60.     ) t1  
  61.     where id<= TIMESTAMPDIFF(hour,startdate,enddate)  
  62.     order by busidatatype,type,starttime  
  63. ) base left join   
  64. (  
  65.     select busidatatype,type,date_format(AuditTime,'%m%d%H') ts,count(*) val from  audit_obj_detail   
  66.     where AuditTime>=${starttime} and AuditTime<${endtime}   
  67.     and busitype = 'mvbox'  
  68.     group by busidatatype,type,date_format(AuditTime,'%m%d%H')  
  69. ) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)  
  70. group by base.busidatatype,base.type,base.ts,base.cn   
  71. ) t1 ;  

由于这个SQL已然比较复杂,再加动态行转列,可读性几乎就没有了.
这个SQL查询的结果大致如下。

其中type可能是发布或者审核.
ts 表示月 日 和小时
cn表示模块类型
val表示数量.

将这个结果导入至Excel
选择插入,数据透视表


然后将ts 设置为列标签,这样 ts 就由行变成列显示
cn和type作为行标签.
val作为显示数值


然后选择视图->冻结窗格->冻结首列  方便观看数据

可以看到如下结果


完全符合动态行转列的需求,并且可以排序和筛选.太强大了.
阅读(831) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册

友情链接:万达娱乐平台  万达主管  万达娱乐开户  万达娱乐登录  万达娱乐招商QQ  测试  万达招商  万达主管  万达娱乐平台  万达娱乐注册