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