我要评论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，我们就可以对此表进行操作了，我第一次见到，感谢让我有个小小的进步。
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论