mysql分别排名
姓名m 科目s 分数n
甲 A 90
甲 B 60
甲 C 70
甲 D 80
乙 A 80
乙 B 80
乙 C 90
丙 A 70
丙 B 80
丁 A 60
如果要得到针对每一个科目ABCD的排名，
mysql里没有rownum,很容易想到子语句
希望早点支持 RANK() OVER。
代码
select n,s,
(
select count(1) from s_score b where n>=
(
select n
from s_score a
where and a.s=c.s
order by n desc limit 1
)
and b.s=c.s
) as r
from s_score c where group by s;
(
select count(1) from s_score b where n>=
(
select n
from s_score a
where and a.s=c.s
order by n desc limit 1
)
and b.s=c.s
) as r
from s_score c where group by s;
优化以后
代码
SELECT t3.m, t3.s, max( n ) AS n, count( * ) AS r
FROM (
SELECT t1.m, t1.s, t1.n
FROM s_score t1, s_score t2
WHERE t2.s = t1.s
AND (
(
t1.n < t2.n
)
OR (
(
t1.n = t2.n
)
AND (
t1.id >= t2.id
)
)
)
ORDER BY t1.m, t1.s
)t3
GROUP BY t3.m, t3.s
ORDER BY n, r
FROM (
SELECT t1.m, t1.s, t1.n
FROM s_score t1, s_score t2
WHERE t2.s = t1.s
AND (
(
t1.n < t2.n
)
OR (
(
t1.n = t2.n
)
AND (
t1.id >= t2.id
)
)
)
ORDER BY t1.m, t1.s
)t3
GROUP BY t3.m, t3.s
ORDER BY n, r
