1:
select a.EID,Name,a.Department,b.Course,b.Grade from (select t.*,row_number() over(partition by CourseID order by Grade desc) rn from Training t) b,Employee a
where a.EID = b.EID and b.rn = 12:bu mingbai3:
select CourseID,Course,sum(case when Grade<=100 and Grade>84 then 1 else 0 end) "100-85",
sum(case when Grade<=84 and Grade>69 then 1 else 0 end) "84-70",
sum(case when Grade<=69 and Grade>59 then 1 else 0 end) "69-60",
sum(case when Grade<60 then 1 else 0 end) "<60"
from Training
group by CourseID,Course4:
update Training a set Order =
(select rk from (select c.*,rank() over(partition by CourseID order by Grade desc) rk
from Training c) b where a.CourseID = b.CourseID and a.EID = b.EID)
select a.EID,Name,a.Department,b.Course,b.Grade from (select t.*,row_number() over(partition by CourseID order by Grade desc) rn from Training t) b,Employee a
where a.EID = b.EID and b.rn = 12:bu mingbai3:
select CourseID,Course,sum(case when Grade<=100 and Grade>84 then 1 else 0 end) "100-85",
sum(case when Grade<=84 and Grade>69 then 1 else 0 end) "84-70",
sum(case when Grade<=69 and Grade>59 then 1 else 0 end) "69-60",
sum(case when Grade<60 then 1 else 0 end) "<60"
from Training
group by CourseID,Course4:
update Training a set Order =
(select rk from (select c.*,rank() over(partition by CourseID order by Grade desc) rk
from Training c) b where a.CourseID = b.CourseID and a.EID = b.EID)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货