sql 复杂查询count case 与 sum case

示例:查询不同类型来源的总数,来源自定义:

select count(case when c.source in
                ('web_form','phone_service','mobile_form','web_service','mobile_service')
                then 1  else null end ) as 线上,
            count(case when c.source in
                ('friends','business','offline','direct_form','email','customer')
                then 1  else null end ) as 线下,
            count(case when c.source in
                ('ctrip','Fliggy','mafengwo')
                then 1  else null end ) as 平台,
            count(case when c.source in
                ('wechat_service','wechat_form')
                then 1  else null end ) as 自媒体
            from case c
            where c.status = 3 and c.isdel = 0

2.查询不同类型来源订单的成交总金额:

select sum(case when c.source in
                ('web_form','phone_service','mobile_form','web_service','mobile_service')
                then o.rmb_price  else null end ) as 线上,
            sum(case when c.source in
                ('friends','business','offline','direct_form','email','customer')
                then o.rmb_price  else null end ) as 线下,
            sum(case when c.source in
                ('ctrip','Fliggy','mafengwo')
                then o.rmb_price  else null end ) as 平台,
            sum(case when c.source in
                ('wechat_service','wechat_form')
                then o.rmb_price  else null end ) as 自媒体
            from case c left JOIN order o
                on c.case_id=o.case_id
            where c.status = 3 and c.isdel = 0
不算最优,但很管用。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值