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,
-- 獲取測試數(shù)據(jù)數(shù)量
(
 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,
 -- 測試數(shù)據(jù)有效數(shù)量 
(
 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