我们假设第一个表为t1,第二个表为t2select id,sum(score1) from ( select t1.id,t1.xk,t1.score,nvl(t2.score,t1.score) score1 from t1,t2 where t1.score = t2.mc(+) ) tt group by id
其实就是一个左连接,一个nvl
with score as( select '001' id, '语文' xk, '70' score from dual union all select '002' id, '语文' xk, '80' score from dual union all select '001' id, '体育' xk, '良' score from dual union all select '002' id, '体育' xk, '优' score from dual), grade as( select '优' mc, 90 score from dual union all select '良' mc, 80 score from dual union all select '中' mc, 70 score from dual)select id, sum(score) total_score from (select id, xk, decode(g.score, null, s.score, g.score) score from score s, grade g where s.score = g.mc(+)) group by id;
select id,sum(fh) from (select a.id,a.Xk,decode(a.Score,'优','90','良','80','中',70,a.Score) as fh from test1 a) group by id
from
(
select t1.id,t1.xk,t1.score,nvl(t2.score,t1.score) score1
from t1,t2
where t1.score = t2.mc(+)
) tt
group by id
select '001' id, '语文' xk, '70' score from dual union all
select '002' id, '语文' xk, '80' score from dual union all
select '001' id, '体育' xk, '良' score from dual union all
select '002' id, '体育' xk, '优' score from dual),
grade as(
select '优' mc, 90 score from dual union all
select '良' mc, 80 score from dual union all
select '中' mc, 70 score from dual)select id, sum(score) total_score
from (select id, xk, decode(g.score, null, s.score, g.score) score
from score s, grade g
where s.score = g.mc(+))
group by id;
(select a.id,a.Xk,decode(a.Score,'优','90','良','80','中',70,a.Score) as fh from test1 a) group by id