select t1.*, sum(decode(t2.class,'chinses',score,0)) chinses, sum(decode(t2.class,' math',score,0)) math , sum(decode(t2.class,'english,score,0)) english from table1 t1,table2 t2 where t1.sid=t2.sid
with t1 as ( select 1 sid,'zhangsa' name,20 age from dual union all select 2 sid,'lisi' name,21 age from dual union all select 3 sid,'wangwu' name,23 age from dual ), t2 as ( select 101 cid,'chinese' class,100 score,1 sid from dual union all select 102 cid,'math' class,100 score,3 sid from dual union all select 103 cid,'english' class,59 score,2 sid from dual )
select t1.sid,t1.name,t1.age, nvl(decode(t2.class,'chinese',t2.score),0) chinese, nvl(decode(t2.class,'math',t2.score),0) math , nvl(decode(t2.class,'english',t2.score),0) english from t1 left join t2 on t1.sid=t2.sid order by t1.sid
select a.sid,a.name,a.age,decode(b.class,'chinese',b.score,0)chinese, decode(b.class,'math',b.score,0)math, decode(b.class,'english',b.score,0)english from table1 a,table2 b where a.sid=b.sid
标量子查询即可解决,如下: select sid, name, age, nvl((select score from table2 where sid = table1.sid and class='chinese'),0) chinese, nvl((select score from table2 where sid = table1.sid and class='math'),0) math, nvl((select score from table2 where sid = table1.sid and class='english'),0) english from table1;结果验证过,正确
-- 没测试,大概如下,和楼上几位,大同小意。 select t1.sid,t1.name,t1.age, case when t2.class = 'chinese' then t2.score else 0 end chinese, case when t2.class = 'math' then t2.score else 0 end math, case when t2.class = 'english' then t2.score else 0 end english from t1 inner join t2 on t1.sid = t2.sid order by 1
WITH a AS (SELECT t1.sid,t1.name,t1.age,t2.class,t2.score from table1 t1, table2 t2 where t1.sid=t2.sid) select a.sid,a.name,a.age, case when a.class='chises' then score ELSE 0 end chinese, case when a.class='math' then score ELSE 0 end math, case when a.class='english' then score ELSE 0 end english from a ORDER BY SID;
sum(decode(t2.class,'chinses',score,0)) chinses,
sum(decode(t2.class,' math',score,0)) math ,
sum(decode(t2.class,'english,score,0)) english
from table1 t1,table2 t2
where t1.sid=t2.sid
t1 as
( select 1 sid,'zhangsa' name,20 age from dual
union all
select 2 sid,'lisi' name,21 age from dual
union all
select 3 sid,'wangwu' name,23 age from dual
),
t2 as
(
select 101 cid,'chinese' class,100 score,1 sid from dual
union all
select 102 cid,'math' class,100 score,3 sid from dual
union all
select 103 cid,'english' class,59 score,2 sid from dual
)
select t1.sid,t1.name,t1.age,
nvl(decode(t2.class,'chinese',t2.score),0) chinese,
nvl(decode(t2.class,'math',t2.score),0) math ,
nvl(decode(t2.class,'english',t2.score),0) english
from t1
left join t2
on t1.sid=t2.sid
order by t1.sid
decode(b.class,'math',b.score,0)math,
decode(b.class,'english',b.score,0)english
from table1 a,table2 b
where a.sid=b.sid
select sid, name, age,
nvl((select score from table2 where sid = table1.sid and class='chinese'),0) chinese,
nvl((select score from table2 where sid = table1.sid and class='math'),0) math,
nvl((select score from table2 where sid = table1.sid and class='english'),0) english
from table1;结果验证过,正确
-- 没测试,大概如下,和楼上几位,大同小意。
select t1.sid,t1.name,t1.age,
case when t2.class = 'chinese' then t2.score else 0 end chinese,
case when t2.class = 'math' then t2.score else 0 end math,
case when t2.class = 'english' then t2.score else 0 end english
from t1
inner join t2 on t1.sid = t2.sid
order by 1
select a.sid,a.name,a.age,
case when a.class='chises' then score ELSE 0 end chinese,
case when a.class='math' then score ELSE 0 end math,
case when a.class='english' then score ELSE 0 end english
from a ORDER BY SID;