✔ 最佳答案
For proper display, single quotes around grade letter have been removed.
update mytable set grade = null
update mytable set grade=A where id in (select top 10 id from mytable order by marks desc)
update mytable set grade=A where marks = (select min(marks) from mytable where grade = A)
update mytable set grade=B where id in (select top 100-(select count(id) from mytable where grade = A) id from mytable where grade is null order by marks desc)
update mytable set grade=B where marks = (select min(marks) from mytable where grade = B)
update mytable set grade=C where id in (select top 300-(select count(id) from mytable where grade in (A,B)) id from mytable where grade is null order by marks desc)
update mytable set grade=C where marks = (select min(marks) from mytable where grade = C)
update mytable set grade=D where id in (select top 600-(select count(id) from mytable where grade in (A,B,C)) id from mytable where grade is null order by marks desc)
update mytable set grade=D where marks = (select min(marks) from mytable where grade = D)
update mytable set grade=E where id in (select top 1000-(select count(id) from mytable where grade in (A,B,C,D)) id from mytable where grade is null order by marks desc)
update mytable set grade=E where marks = (select min(marks) from mytable where grade = E)
2007-06-17 15:43:27 補充：
回雁妮, your sql do not achieve the desired result. I explain later.OK 5 sql statements will do.找出第10個最高分數,高於這分數或相同的就是Aupdate mytable set grade=A where marks >= (select marks from mytable order by marks desc limit 9,1)
2007-06-17 15:47:52 補充：
找出第100個最高分數,高於這分數或相同的就是B,A除外update mytable set grade=B where marks >= (select marks from mytable order by marks desc limit 99,1) and grade is null
2007-06-17 15:48:59 補充：
找出第300個最高分數,高於這分數或相同的就是C,A,B除外update mytable set grade=C where marks >= (select marks from mytable order by marks desc limit 299,1) and grade is null
2007-06-17 15:52:06 補充：
找出第600個最高分數,高於這分數或相同的就是D,A,B,C除外update mytable set grade=D where marks >= (select marks from mytable order by marks desc limit 599,1) and grade is nullupdate mytable set grade=E where grade is null
2007-06-17 16:02:03 補充：
To 雁妮select min(marks) from mytable order by marks desc limit 600I have not tested it, but my intuition tells me that this sql returns the lowest mark rather the 600th highest mark.
2007-06-17 16:19:08 補充：
[ 假設 10-100 都係同分, 咁 1-100 都係 'A', 101 已經係 'B' ]如果這情況出現,你我的sql都做不到
2007-06-18 09:26:01 補充：
[ 假設 10-100 都係同分, 咁 1-100 都係 A, 101 已經係 C. 係 C 先對, 即係一個 B 都無. ]Do it backward, from E first. This is similar to 雁妮's method.update mytable set grade=E
2007-06-18 09:27:00 補充：
找出第600個最高分數,高於這分數或相同的就是Dupdate mytable set grade=D where marks >= (select marks from mytable order by marks desc limit 599,1)找出第300個最高分數,高於這分數或相同的就是Cupdate mytable set grade=C where marks >= (select marks from mytable order by marks desc limit 299,1)
2007-06-18 09:27:43 補充：
找出第100個最高分數,高於這分數或相同的就是Bupdate mytable set grade=B where marks >= (select marks from mytable order by marks desc limit 99,1)找出第10個最高分數,高於這分數或相同的就是Aupdate mytable set grade=A where marks >= (select marks from mytable order by marks desc limit 9,1)
