求行变列问题(大概是这个吧)

Question

主表  id   int  
      lesson  int   (1代表语文,2代表数学)
从表  id   int  
      pid  int    (对应主表的id)
      score  double  (分数)
我现在要查询出id,参加语文人数求和,参加语文分数求和,参加数学人数求和,参加数学分数求和

Answer 1

select 从表.id,sum(decode(lesson,1,1,0)) "语文人数",
sum(decode(lesson,2,1,0)) "数学人数",
sum(decode(lesson,1,score,0)) "语文分数",
sum(decode(lesson,2,score,0)) "数学分数"
from 主表,从表 
where 主表.id=从表.pid
group by 从表.id

Answer 2

select A.id,sum(decode(lesson,1,1,0)) "语文人数和",
sum(decode(lesson,1,score,0)) "语文分数和",
sum(decode(lesson,2,1,0)) "数学人数和",
sum(decode(lesson,2,score,0)) "数学分数和"
from A,B 
where A.id=B.pid
group by A.id ,A.lesson

Answer 3

with t1 as(
select 1 id,1 lesson from dual
union all
select 2 id,2 lesson from dual
union all
select 3 id,1 lesson from dual
union all
select 4 id,2 lesson from dual
),t2 as(
select 1 id,1 pid,90 score from dual
union all
select 2 id,2 pid,80 score from dual
union all
select 3 id,3 pid,70 score from dual
)
select sum(decode(lesson,1,1,0)) 语文人数,sum(decode(lesson,1,t2.score,0)) 语文分数和,
sum(decode(lesson,2,1,0)) 数学人数,sum(decode(lesson,2,t2.score,0)) 数学分数和
 from t1,t2 where t1.id = t2.pid

Answer 4

我把问题复杂化了，原来decode就能解决这个问题！结贴给分