vlambda博客
学习文章列表

【Mysql】用套娃大法写个“合计”

年后公司做了新业务,小骨又开始重头写表。


这次依旧需要统计团队成员的工作业绩,不仅需要按人group by,最好还能按组小计,按全体合计(小计、合计需求是我自己加的)


之前在里写过用group by xxx with rollup来做,但是在数据不全(存在较多null)的情况下,with rollup似乎不太灵。


于是小骨又百度了一个更好的方法,不仅可以小计、合计,还可以把“小计”、“合计”字样给显示出来。



方法其实也很简单,就是活用Union自制小计和合计。


SELECTu.u_group '组名',u.u_name '组员',sum(t.group_score)'小组积分',sum(t.leader_score) '组长积分',sum(t.happy_score) '喜悦积分'FROM qyd_u uLEFT JOIN qyd_t t on u.u_name = t.u_nameGROUP BY 1,2


这是我们按人聚合的每人积分总分。


【Mysql】用套娃大法写个“合计”


现在我们要把按组聚合的积分表给算出来跟上表去Union。


所以要保证列数、列名都是相同的


【Mysql】用套娃大法写个“合计”


SELECTq_t.`组名` '组名',(CASE WHEN q_t.`组名` IS NOT NULL THEN '小计'ELSE '合计' end) '组员',sum(q_t.`小组积分`)'小组积分',sum(q_t.`组长积分`)'组长积分',sum(q_t.`喜悦积分`)'喜悦积分'FROM(SELECTu.u_group '组名',u.u_name '组员',sum(t.group_score)'小组积分',sum(t.leader_score) '组长积分',sum(t.happy_score) '喜悦积分'FROM qyd_u uLEFT JOIN qyd_t t on u.u_name = t.u_nameGROUP BY 1,2) q_tGROUP BY 1 with ROLLUP


套娃大法把之前按人聚合的数据按组再聚合一次。


这里我们对“组员”字段做一个小处理,如果“组名”非空,就写“小计”,“组名”为空,就写“合计”。


最后,把两张表Union起来,再加一个order by,把“小计”放在每组最后,“合计”放在全表最后。


SELECTu.u_group '组名',u.u_name '组员',sum(t.group_score)'小组积分',sum(t.leader_score) '组长积分',sum(t.happy_score) '喜悦积分'FROM qyd_u uLEFT JOIN qyd_t t on u.u_name = t.u_nameGROUP BY 1,2UNIONSELECTq_t.`组名` '组名',(CASE WHEN q_t.`组名` IS NOT NULL THEN '小计'ELSE '合计' end) '组员',sum(q_t.`小组积分`)'小组积分',sum(q_t.`组长积分`)'组长积分',sum(q_t.`喜悦积分`)'喜悦积分'FROM(SELECTu.u_group '组名',u.u_name '组员',sum(t.group_score)'小组积分',sum(t.leader_score) '组长积分',sum(t.happy_score) '喜悦积分'FROM qyd_u uLEFT JOIN qyd_t t on u.u_name = t.u_nameGROUP BY 1,2) q_tGROUP BY 1 with ROLLUPorder by 1 desc,3 


【Mysql】用套娃大法写个“合计”


就成功啦~~~




拼了那么多表之后,小骨总结出来:


左右拼用left/right join,

上下拼用union


(对于很多数据大神来说,这种显而易见的规律都不用动脑子的吧【Mysql】用套娃大法写个“合计”【Mysql】用套娃大法写个“合计”【Mysql】用套娃大法写个“合计”)


不过,为了使数据适用metabase可视化图表,还真的得活用拼表!


例如用union拼出来的强调流失率的漏斗图。


Metabase做不了横向的漏斗图,只能做纵向的,有点儿像瀑布图了。


尽管这样,紫色的流失部分也可以很直观的表现出来,很赞了!


(只不过这个NB哄哄的代码不是我写的【Mysql】用套娃大法写个“合计”【Mysql】用套娃大法写个“合计”