我的資料庫中有以下查詢，我想添加一個新列來處理學生的最終 Apperciation：查詢：
select student_name,q4.percentage
from (select q2.student_id,mark *100/total as Percentage
from (select class_id,sum(max_mark)as total
from course
group by(class_id)
)q1 ,
(select sum(mark) as mark,student_id
from grades
group by(student_id)
) q2
where q2.student_id in (select student_id
from student
where student.section_id in(select section_id
from section
where class_id=q1.class_id)
)
order by q2.student_id
) q4
inner join student on q4.student_id=student.student_id;
結果如下： 在此處輸入影像描述
| 學生姓名 | 百分比 |
|---|---|
| 馬蘇德 | 50.41667 |
| 阿里-施比布 | 84.16667 |
| 莫娜 | 75.2941 |
現在我只需要在結果中添加一個新列，就像一個獎項，所以表格是這樣的：
student_name percetage award
mahmoud-kabbani 79.166667 B
Kareem-Alshaeer 54.583 c
uj5u.com熱心網友回復：
您可以在 case 陳述句中包含 1 列，如下所示 -
select student_name,q4.percentage,
CASE WHEN q4.percentage > 80 THEN 'A'
WHEN q4.percentage > 60 THEN 'B'
WHEN q4.percentage > 40 THEN 'C'
ELSE 'E'
END award
from (select q2.student_id,mark *100/total as Percentage
from (select class_id,sum(max_mark)as total
from course
group by(class_id)
)q1 ,
(select sum(mark) as mark,student_id
from grades
group by(student_id)
) q2
where q2.student_id in (select student_id
from student
where student.section_id in(select section_id
from section
where class_id=q1.class_id)
)
order by q2.student_id
) q4
inner join student on q4.student_id=student.student_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/372319.html
上一篇：無法“交換”資料庫中的值的腳本