现有这样一张表(cj_finish):
biaozhi BJ name yw sx yy zf bjmc
100238 1 XXX 114 149 100 363
100649 1 YYY 123 129 103 355
想求出班级(BJ)里每个人的排名,并填写到bjmc列。
1、算排名的方法如下:
SELECT a1.Name, a1.zf, COUNT(a1.zf) zhmc
FROM cj_finish a1, cj_finish a2
WHERE a1.bj=a2.bj and (a1.zf < a2.zf or (a1.zf=a2.zf and a1.Name = a2.Name))
GROUP BY a1.Name, a1.zf
2、添加数据用update cj_finish set cj_finish.bjmc
3、…………
谢谢
biaozhi BJ name yw sx yy zf bjmc
100238 1 XXX 114 149 100 363
100649 1 YYY 123 129 103 355
想求出班级(BJ)里每个人的排名,并填写到bjmc列。
1、算排名的方法如下:
SELECT a1.Name, a1.zf, COUNT(a1.zf) zhmc
FROM cj_finish a1, cj_finish a2
WHERE a1.bj=a2.bj and (a1.zf < a2.zf or (a1.zf=a2.zf and a1.Name = a2.Name))
GROUP BY a1.Name, a1.zf
2、添加数据用update cj_finish set cj_finish.bjmc
3、…………
谢谢
FROM cj_finish a1, cj_finish a2
WHERE a1.bj=a2.bj and (a1.zf < a2.zf or (a1.zf=a2.zf and a1.Name = a2.Name))
GROUP BY a1.Name, a1.zf )B
set cj_finish.bjmc=B.zhmc
update cj_finish A,(SELECT a1.biaozhi,a1.Name, a1.zf, COUNT(a1.zf) bjmc
FROM cj_finish a1, cj_finish a2
WHERE a1.bj=a2.bj and (a1.zf < a2.zf or (a1.zf=a2.zf and a1.Name = a2.Name))
GROUP BY a1.Name, a1.zf )B
set a.bjmc=B.bjmc
where a.biaozhi=B.biaozhi;