如下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 )
红色部分是有错误的部分,大虾们帮我改一下,对,给所有分
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 )
红色部分是有错误的部分,大虾们帮我改一下,对,给所有分
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 when a.school =3 then a.school=c.school else a.school<>c.school end )
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 (a.school =3 and a.school=c.school or a.school <> 3 and a.school<>c.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
((a.school=3 and c.school = 3) or a.school<>c.school)
(a.school =3 and a.school=c.school or a.school <> 3 and a.school<>c.school )
and a.school=case WHEN a.school =3 then c.school else a.school end