查询oracle中其他列之和的分组备注列

Question

让我们假设我有下面的表"marksheet"：

userid | subject |  type     | marks | remarks
1        science    theory    35     good
1        science    practical 25     very good
1        computer  theory    50     very good
1        computer   practical 25     average

现在我想要这样：

userid  | subject | theory_marks | theory_remarks | practical_marks | practical_remarks
1        science     60              good              25                very good
1        computer    50              very good         15                average

我已经试过了。

select userid, subject, sum(case when type = 'theory' then marks else 0) theory_marks, sum(case when type = 'practical' then marks else 0) practical_marks from marksheet group by userid, subject

现在，我想添加如上所述的theory_remarks和practical_remarks (table2)。

谢谢你的帮助。

Answer 1

假设userid,subject,type是唯一的

select userid, subject, 
   max(case when type = 'theory' then marks end) theory_marks, 
   max(case when type = 'practical' then marks end) practical_marks,
   max(case when type = 'theory' then remarks end) theory_remarks, 
   max(case when type = 'practical' then remarks end) practical_remarks    
from marksheet 
group by userid, subject