注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

勇气

终生读书

 
 
 

日志

 
 

根据条件分组查询  

2016-12-23 19:58:53|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


SELECT
professional.`name` as professional_name,
phase.`name` as phase_name,
class_info.professional_id,
class_info.phase_id,
count(student_class.id) as totalStudentCount,
sum(student_class.pay_real_price) as totalPrice,
sum( case when student_class.pay_status=10 then student_class.pay_real_price END) as enrollPrice,
sum( case when student_class.pay_status=20 then student_class.pay_real_price END) as fullPrice,
count( case when student_class.pay_status=10 then student_class.id END) as enrollStuCount,
count( case when student_class.pay_status=20 then student_class.id END) as fullStuCount,
sum(student_class.coupon_price) as couponPrice


FROM student_class
LEFT JOIN class_info ON class_info.id = student_class.class_id
LEFT JOIN phase ON phase.id = class_info.phase_id
LEFT JOIN professional ON professional.id = phase.professional_id


WHERE
student_class.pay_status IN (10,20)

GROUP BY class_info.phase_id



---------------------------

SELECT `user`.`realname`, work_order.ada_user_id, (sum(consume_zhiye.price) -sum(consume_zhiye_refund.refund_price)) as saleprice, -- ada的销售额 (sum(case when consume_zhiye.type=1 then consume_zhiye.price ELSE 0 END) - sum(case when consume_zhiye_refund.type=1 then consume_zhiye_refund.refund_price ELSE 0 END)) as enrollPrice, -- 报名费金额 (sum(case when consume_zhiye.type=5 then consume_zhiye.price ELSE 0 END) - sum(case when consume_zhiye_refund.type=5 then consume_zhiye_refund.refund_price ELSE 0 END)) as fullPrice, -- 全款金额 (sum(case when consume_zhiye.coupon_price > 0 then consume_zhiye.coupon_price ELSE 0 END)) as couponPrice, -- 优惠金额 count(distinct work_order.id) as newWorOrderCount, -- 新增工单数 count(case when work_order.progress=10 then work_order.id END) as finishedCount, -- 成功关单数量 (sum(case when work_order.progress=10 then consume_zhiye.price ELSE 0 END) - sum(case when work_order.progress=10 then consume_zhiye_refund.refund_price ELSE 0 END)) as finishedPrice, -- 成功关单金额 sum(case when work_order.progress=10 then work_order.price ELSE 0 END) as totalPhasePrice, -- 全部跟进期数金额 sum(case when (work_order.progress=10 AND work_order_ada.created_at>0 ) then work_order.price ELSE 0 END) as rocordPhasePrice, -- 有跟进期数金额 sum(case when work_order.progress=10 then consume_zhiye.price ELSE 0 END) as totalRealPrice, -- 全部跟进实际交易金额 sum(case when (work_order.progress=10 AND work_order_ada.created_at>0 ) then consume_zhiye.price ELSE 0 END) as rocordRealPrice -- 有跟进实际交易金额 FROM work_order LEFT JOIN work_order_ada ON work_order_ada.`work_order_id` = work_order.id AND work_order_ada.`ada_user_id` = work_order.`ada_user_id` LEFT JOIN consume_zhiye ON consume_zhiye.work_order_id = work_order.id LEFT JOIN consume_zhiye_refund ON consume_zhiye_refund.work_order_id = work_order.id RIGHT JOIN `user` ON `user`.id = work_order.ada_user_id RIGHT JOIN entrust_role_user ON entrust_role_user.user_id = work_order.ada_user_id WHERE work_order.ada_user_id >0 group by ada_user_id

  评论这张
 
阅读(69)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017