67,513
社区成员
发帖
与我相关
我的任务
分享
select t1.gd, decode(t1.gd, 'outer', count(1) - count(t2.id), count(t2.id))
from (select '1' gd, 93 mi, 100 ma
from dual
union all
select '2' gd, 80 mi, 93 ma
from dual
union all
select '3' gd, 70 mi, 80 ma
from dual
union all
select 'outer', null, null from dual) t1
left join (select 1 id, 95 score
from dual
union all
select 1 id, 95 score
from dual
union all
select 2 id, 300 score from dual) t2 on (t1.ma >= t2.score and
t1.mi < t2.score)
group by t1.gd
;
oracle写的,mysql可能要小改一下。