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