update t set mingci = (case (c.id+a.num-e.id)%a.num when 0 then a.num else (c.id+a.num-e.id)%a.num end) from
(select dengji,count(*) as num from t group by dengji) as a,--返回该等级的记录数
(select fengshu,id=(select sum(1) from t where fengshu>=b.fengshu)--返回按分数排序的名次
from t b) as c,
(select dengji,id=(select sum(1) from t where dengji<=d.dengji) --返回小于该等级的记录总数+该等级的记录数(因为sum空值将被忽略)
from t d) as e
where t.dengji = a.dengji and t.fengshu = c.fengshu and t.dengji = e.dengjiselect * from t(所影响的行数为 10 行)fengshu dengji mingci
----------- ---------- -----------
100 A 1
90 A 2
80 A 3
70 B 1
60 B 2
50 B 3
40 C 1
30 C 2
20 C 3
10 C 4(所影响的行数为 10 行)
(select dengji,count(*) as num from t group by dengji) as a,--返回该等级的记录数
(select fengshu,id=(select sum(1) from t where fengshu>=b.fengshu)--返回按分数排序的名次
from t b) as c,
(select dengji,id=(select sum(1) from t where dengji<=d.dengji) --返回小于该等级的记录总数+该等级的记录数(因为sum空值将被忽略)
from t d) as e
where t.dengji = a.dengji and t.fengshu = c.fengshu and t.dengji = e.dengjiselect * from t(所影响的行数为 10 行)fengshu dengji mingci
----------- ---------- -----------
100 A 1
90 A 2
80 A 3
70 B 1
60 B 2
50 B 3
40 C 1
30 C 2
20 C 3
10 C 4(所影响的行数为 10 行)
(select dengji,count(*) as num from t group by dengji) as a,--返回该等级的记录数
(select fengshu,id=(select sum(1) from t where fengshu>=b.fengshu)--返回按分数排序的名次
from t b) as c,
(select dengji,id=(select sum(1) from t where dengji<=d.dengji) --返回小于该等级的记录总数+该等级的记录数(因为sum空值将被忽略,不知道怎样得到零值)
from t d) as e
where t.dengji = a.dengji and t.fengshu = c.fengshu and t.dengji = e.dengji
from 班级成绩表 a
from 班级成绩表 a
set mingci=1+(
select count(*) from 班级成绩表
where dengji=a.dengji
and fenshu>a.fenshu
)
from 班级成绩表 a