sql查询代替代码业务查询

select *,concat(' ,(case student_type when ',id,' then 1 else 0 end) as \'',code,'\''),concat(',sum(',code,')')
from rt_condition_value where type_code='studenttype';

 

select hz.code,hz.name,sum(studenttype_01)
,sum(studenttype_02)
,sum(studenttype_03)
,sum(studenttype_04)
,sum(studenttype_05)
,sum(studenttype_06)
,sum(studenttype_031)
,sum(studenttype_07)
,sum(studenttype_08)
,sum(studenttype_071)
from (
select s.code,s.name,fl.*
from 
(select xsid,sum(case when type=0 then `middle_school_id` 
when type=1 and right(school_district_id,1)%2=1 then middle_school_id 
when type=2 and right(school_district_id,1)%2=0 then middle_school_id
else 0 end) xxid from (
select a.*,b.school_district_id,b.id xsid from `rt_school_district` a 
join rt_student b on a.road_id=b.school_district_road_id 
and school_district_id>=`start_number` and school_district_id<=`end_number`) t
group by xsid) g
join rt_middle_school s on g.xxid=s.id
join 
(select  id   ,(case student_type when 31 then 1 else 0 end) as 'studenttype_01'
 ,(case student_type when 32 then 1 else 0 end) as 'studenttype_02'
 ,(case student_type when 33 then 1 else 0 end) as 'studenttype_03'
 ,(case student_type when 34 then 1 else 0 end) as 'studenttype_04'
 ,(case student_type when 35 then 1 else 0 end) as 'studenttype_05'
 ,(case student_type when 36 then 1 else 0 end) as 'studenttype_06'
 ,(case student_type when 102 then 1 else 0 end) as 'studenttype_031'
 ,(case student_type when 103 then 1 else 0 end) as 'studenttype_07'
 ,(case student_type when 117 then 1 else 0 end) as 'studenttype_08'
 ,(case student_type when 125 then 1 else 0 end) as 'studenttype_071' from rt_student sx ) fl
on g.xsid=fl.id
where not exists (select 1 from rt_admission ad where g.xsid=ad.student_id and type_id in (83,84,85))
) hz
group by hz.code,hz.name
with rollup;

 

select s.code,s.name,sum(admissionnote01)
,sum(admissionnote02)
,sum(admissionnote03) from (
select middle_school_id   ,(case  when type_id=83 then 1 else 0 end) as 'admissionnote01'
 ,(case  when type_id=84 then 1 else 0 end) as 'admissionnote02'
 ,(case  when type_id=85 then 1 else 0 end) as 'admissionnote03' from rt_admission a
where type_id in (83,84) and not exists (select 1 from rt_admission b where a.student_id=b.student_id and b.type_id=85)
union all
select middle_school_id  ,(case  when type_id=83 then 1 else 0 end) as 'admissionnote01'
 ,(case  when type_id=84 then 1 else 0 end) as 'admissionnote02'
 ,(case  when type_id=85 then 1 else 0 end) as 'admissionnote03' from rt_admission
where type_id=85) g
join rt_middle_school s on g.middle_school_id=s.id
group by g.middle_school_id;