数据分析一

SELECT
  @i:=@i+1 AS serialNo,
    fasch.school_name,
  CASE
  WHEN fasch.LEVEL = 4  or exists (select * from fo_atte_school_cert fascc  where fascc.school_id=fasch.id and  fascc.cert_type='2')  THEN '示范园' 
    WHEN fasch.LEVEL = 5 THEN '公益园' 
    WHEN fasch.audit_state = '2'  or exists (select * from fo_atte_school_cert fascc  where fascc.school_id=fasch.id and  fascc.cert_type='1')  THEN '足特园' 
    ELSE '普通'  END AS level,
 CASE 
 fasch.type 
 WHEN '0' THEN '公立'
 WHEN '1' THEN '私立'
 ELSE '未知' END  as type,
 fasch.province,
 CASE 
 fasch.status   
 WHEN '88' THEN '已认证'
 ELSE '未认证' END  as status,
   (select count(*) from fo_atte_teacher  fatea where fatea.school_id =fasch.id  and fatea.deletetime is null and fatea.createtime >= 1609430400 and fatea.createtime <= 1640966399)  as countTeacher,
 (select count(*)  from fo_atte_student  fastu where fastu.school_id=fasch.id  and fastu.deletetime is null and fastu.createtime >= 1609430400  and fastu.createtime <= 1640966399)  as registerStudentCount,
 fnrank.study_count as studyCountTotal,
 fnrank.student_test_count as studentTestCountTotal,
-- 获取测试数据数量
(
 IFNULL((SELECT count(*) from fo_testing_log  ftl3  where ftl3.school_id=fasch.id  and  ftl3.type='0' and ftl3.testing_foundation_id=3 and ftl3.createtime >= 1609430400 and ftl3.createtime <= 1640966399 ),0)
 +
IFNULL( (SELECT count(*) from fo_testing_log  ftl1 where ftl1.school_id=fasch.id  and  ftl1.type='0' and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399) ,0)
 +
 IFNULL((SELECT count(*) from fo_testing_log  ftl2 where ftl2.school_id=fasch.id  and  ftl2.type='1' and ftl2.createtime >= 1609430400 and ftl2.createtime <= 1640966399 ),0) * 2
 +
 IFNULL((SELECT count(*) from fo_testing_log  ftl2 where ftl2.school_id=fasch.id  and  ftl2.type='1' and  ftl2.testing_sports_id in (4,15,16) and  ftl2.createtime >= 1609430400 and ftl2.createtime <= 1640966399 ),0) * 2
 ) as testCount,
 -- 测试数据有效数量 
(
 IFNULL((SELECT count(*) from fo_testing_log  ftl3  where ftl3.school_id=fasch.id  and  ftl3.type='0' and ftl3.testing_foundation_id=3 and ftl3.createtime >= 1609430400 and ftl3.createtime <= 1640966399 ),0)
 +
IFNULL( (SELECT count(*) from fo_testing_log  ftl1 where ftl1.school_id=fasch.id  and  ftl1.type='0' and ftl1.createtime >= 1609430400 and ftl1.createtime <= 1640966399) ,0)
 +
 IFNULL((SELECT count(*) from fo_testing_log  ftl2 where ftl2.school_id=fasch.id  and  ftl2.type='1' and ftl2.createtime >= 1609430400 and ftl2.createtime <= 1640966399 ),0) 
 +
 IFNULL((SELECT count(*) from fo_testing_log  ftl2 where ftl2.school_id=fasch.id  and  ftl2.type='1' and  ftl2.testing_sports_id in (4,15,16) and  ftl2.createtime >= 1609430400 and ftl2.createtime <= 1640966399 ),0) 
 ) as validTestCount,
 -- 学校总积分
  (
    IFNULL(fasch.integral,0) 
    +
    IFNULL( (SELECT  sum(fusl.score) from  fo_user_score_log  fusl INNER join  fo_atte_teacher fatea  on fusl.user_id=fatea.user_id 
  where  fatea.school_id =fasch.id and fusl.createtime >= 1609430400 and fusl.createtime <= 1640966399 and fusl.role='2') ,0)
  +
    IFNULL( (SELECT  sum(fusl.score) from  fo_user_score_log  fusl INNER join  fo_atte_student fatea  on fusl.user_id=fatea.id 
where  fatea.school_id =fasch.id and fusl.createtime >= 1609430400 and fusl.createtime <= 1640966399 and fusl.role='3'),0)
    ) as totalIntergral, 
 IFNULL(fasch.integral,0) as schoolIntegral,
 
 IFNULL( (SELECT  sum(fusl.score) from  fo_user_score_log  fusl INNER join  fo_atte_teacher fatea  on fusl.user_id=fatea.user_id 
where  fatea.school_id =fasch.id and fusl.createtime >= 1609430400 and fusl.createtime <= 1640966399 and fusl.role='2') ,0) as teacherIntegral,

 IFNULL( (SELECT  sum(fusl.score) from  fo_user_score_log  fusl INNER join  fo_atte_student fatea  on fusl.user_id=fatea.id 
where  fatea.school_id =fasch.id and fusl.createtime >= 1609430400 and fusl.createtime <= 1640966399 and fusl.role='3'),0) as studentIntegral

FROM fo_atte_school fasch
LEFT JOIN fo_new_ranking fnrank ON fasch.id = fnrank.school_id 
left join tmp_valid_test_count tvtc on tvtc.school_id=fasch.id,
(SELECT @i:=0)serial
where  fasch.createtime <= 1640966399