关于case when复杂sql语句查询

问题描述：
需要查询出学生在不同流程状态对应的活动个数，流程状态分为五个状态audit、havePass、notPass、reject、giveup；活动有社会活动(xg_credit_comm_activity_apply)、比赛活动(xg_credit_innov_match_apply)、培训活动(xg_credit_innov_train_apply)。

需要得到的效果：

SQL拼接步骤：
Step1: 统计学生在所有活动中不同流程状态对应的活动个数

SELECT student_id,`status`,COUNT(*) as counts from (
  SELECT student_id, `status` from xg_credit_comm_activity_apply 
  UNION ALL 
 SELECT student_id, `status` from xg_credit_innov_match_apply 
  UNION ALL 
 SELECT student_id,`status` from xg_credit_innov_train_apply 
    )as t GROUP BY t.student_id, t.`status`

Step2:

SELECT t1.student_id as stuId,
CASE when t1.`status`='AUDIT' then t1.counts ELSE 0 END as audit,
CASE when t1.`status`='HAVEPASS' then t1.counts ELSE 0 END as havePass,
CASE when t1.`status`='NOTPASS' then t1.counts else 0 END as notPass,
CASE when t1.`status`='REJECT' then t1.counts else 0 END as reject,
CASE when t1.`status`='GIVEUP' then t1.counts else 0 END as giveup
 from (

SELECT student_id,`status`,COUNT(*) as counts from (

SELECT student_id, `status`  from xg_credit_comm_activity_apply 
UNION ALL
SELECT student_id, `status` from xg_credit_innov_match_apply 
UNION ALL
SELECT student_id,`status` from xg_credit_innov_train_apply 

 )as t GROUP BY t.student_id, t.`status`

)as t1

Step3:

SELECT stuId, SUM(audit) as audit,SUM(havePass) as havePass, SUM(notPass) as notPass, SUM(reject) as reject, SUM(giveup) as giveup from (

SELECT t1.student_id as stuId,
CASE when t1.`status`='AUDIT' then t1.counts ELSE 0 END as audit,
CASE when t1.`status`='HAVEPASS' then t1.counts ELSE 0 END as havePass,
CASE when t1.`status`='NOTPASS' then t1.counts else 0 END as notPass,
CASE when t1.`status`='REJECT' then t1.counts else 0 END as reject,
CASE when t1.`status`='GIVEUP' then t1.counts else 0 END as giveup
 from (

SELECT student_id,`status`,COUNT(*) as counts from (

SELECT student_id, `status`  from xg_credit_comm_activity_apply 
UNION ALL
SELECT student_id, `status` from xg_credit_innov_match_apply 
UNION ALL
SELECT student_id,`status` from xg_credit_innov_train_apply 

 )as t GROUP BY t.student_id, t.`status`

)as t1

)as  t2 GROUP BY t2.stuId