sql Ìõ¼þÅжÏÎÊÌâ

ÈçÏÂsqlÓï¾ä£¬ÈçºÎʵÏÖandºóÃæµÄÌõ¼þÅжÏ
select a.task_id,a.title,a.grade,a.school,
case when a.school=1 then 2 else cast((a.count1*100/c.count2) as dec(18,2)) end  '±ÈÖµ——Èý¸öԵǼ¹ýµÄÓû§' 
from
(select b.id as task_id,b.title as title,b.grade as grade,b.school as school ,sum(case when task_state=3  then 1 else 0 end)as count1 from ec_role_task c
join ec_role
a on a.id=c.role_id and del_state=0  and LAST_VISIT_TIME >=
convert(varchar(19),dateadd(month,-3,getdate()),21)

join ec_task
b on b.id=c.task_id and accept_again=0
group by b.title,b.grade,b.id,b.school
)a,
(select count(id) as  count2 ,school from ec_role where del_state =0 and LAST_VISIT_TIME >=
    convert(varchar(10),dateadd(month,-3,getdate()),21) and grade >= 25 and grade < 30 group by school  )c ,ec_task t
    where t.id=a.task_id and (case a.school =3 then a.school=c.school else a.school<>c.school end )
ºìÉ«²¿·ÖÊÇÓдíÎóµÄ²¿·Ö£¬´óϺÃÇ°ïÎÒ¸Äһϣ¬¶Ô£¬¸øËùÓзÖ

---

ºìÉ«²»·ÖÉÙÁ˸ö  when

---

select a.task_id,a.title,a.grade,a.school,
case when a.school=1 then 2 else cast((a.count1*100/c.count2) as dec(18,2)) end  '±ÈÖµ——Èý¸öԵǼ¹ýµÄÓû§' 
from
(select b.id as task_id,b.title as title,b.grade as grade,b.school as school ,sum(case when