Mysql(Casewhenthenend)函数

Mysql(Casewhenthenend)函数

select t1.student_source,

t1.enroll_school,

count(t1.identity_card) as student_amount,

sum(t1.pay_tuition) as pay_tuition_amount,

sum(t1.channel_amount) as channel_amount,

sum(t1.channel_need_payback) as channel_need_payback_amount,

0 as channel_payback_amount,

sum(t1.channel_deduct) as channel_deduct_amount,

sum(t1.pay_money) as pay_money_amount,

sum(t1.discount_money) as discount_money_amount,

t1.current_percentage

from (

select e.student_source,

e.enroll_school,

s.identity_card,

p.pay_tuition,

sum(p.pay_money) as pay_money,

sum(p.channel_deduct) as channel_deduct,

cpr.current_percentage,

sum(case

when p.discount_type = '高校优惠'

then p.discount_money else 0 end)

as discount_money,

((sum(p.pay_money) - sum(case when p.discount_type = '中心优惠'

then p.discount_money else 0 end)) * current_percentage / 100 - sum(p.channel_deduct))

as channel_need_payback,

((p.pay_tuition - sum(case when p.discount_type = '高校优惠'

then p.discount_money else 0 end)) * current_percentage / 100) as channel_amount

from student s

left join student_enroll e on s.student_id = e.student_id

left join payment_detail p on s.student_id = p.student_id

left join channel_protocol cp on s.channel_id = cp.channel_id

left join channel_protocol_reward cpr on cp.protocol_id = cpr.channel_protocol_id

where p.settlement_id = 354 group by e.enroll_school, s.identity_card,

p.pay_tuition, e.student_source, cpr.current_percentage)

as t1 group by t1.student_source, t1.enroll_school, t1.current_percentage

从上边的sql，一眼看起来好像不是太友好，确实猛一看，扔掉了吧

但是你仔细一看，它可以分为两部分，所有复杂的语句都是有简单的语句构成的，select 后边t1表的字段 + from后边一个select生成的t1表，是不是瞬间感觉好理解的了，那接下来，我们剖析一下 from后边跟的什么呢?细细品尝，发现跟我们之前的 CASE WHEN THEN END 的函数先关，case 分为case 函数 和case 搜索函数，

case 函数 举个例子

1、

select sum(poplation),

case name

when '中国' then '亚洲'

when '印度' then '亚洲'

when '越南' then '亚洲'

when '美国' then '北美洲'

when '加拿大' then '北美洲'

else '其他' end

from test.pop

group by case name

when '中国' then '亚洲'

when '印度' then '亚洲'

when '越南' then '亚洲'

when '美国' then '北美洲'

when '加拿大' then '北美洲'

else '其他' end

case 搜索函数

select sum(poplation),

case

when name = '中国' then '亚洲'

when name = '印度' then '亚洲'

when name = '越南' then '亚洲'

when name = '美国' then '北美洲'

when name = '加拿大' then '北美洲'

else '其他' end

from test.pop

group by case

when name = '中国' then '亚洲'

when na me ='印度' then '亚洲'

when name = '越南' then '亚洲'

when name = '美国' then '北美洲'

when name = '加拿大' then '北美洲'

else '其他' end

 

从上面你可以看出，就是用了一个case函数然后把数据封装成了一个t1，我们就可以对此表进行操作了，我第一次见到，感谢让我有个小小的进步。