oracle 查询的时候加条件判断

方法一.

select a.idcard,
SUM(
 CASE WHEN a.subject_code=2 THEN a.SCOPE ELSE 0  END
) 

,
SUM(
 CASE WHEN a.subject_code=3 THEN a.SCOPE ELSE 0  END
)
,
SUM(
 CASE WHEN a.subject_code=4 THEN a.SCOPE ELSE 0  END
)
,
SUM(
 CASE WHEN a.subject_code=5 THEN a.SCOPE ELSE 0  END
)
from cm_student_score a   

GROUP BY a. idcard

注:使用方法
SUM(
CASE WHEN a.subject_code=2 THEN a.SCOPE ELSE 0  END
)
如果a.subject_code=2则值为a.SCOPE,否则为0
而SUM()这方法是,把里面所有的成绩全部加起来.意思:所有subject_code=2的总成绩

方法二:

SELECT a.idcard,sum(DECODE(subject_code,2,SCOPE,0)) subject_code2, 

       sum(DECODE(subject_code,2,SCOPE,0)) subject_code3, 

       sum(DECODE(subject_code,2,SCOPE,0)) subject_code4, 

       sum(DECODE(subject_code,2,SCOPE,0)) subject_code5

FROM cm_student_score  GROUP BY a. idcard