新手請(qǐng)教如何用SQL在成績(jī)表和學(xué)生表通過(guò)學(xué)生名字查詢某人的總分和在全年級(jí)的排名

SELECT b.rank,c.score FROM (
SELECT count(*)+1 as 'rank' FROM (
SELECT id,u.score FROM 學(xué)生表 i LEFT JOIN (
SELECT id,SUM(score)as score FROM 成績(jī)表 GROUP BY id
)u ON u.id=i.id ORDER BY u.score DESC
) a WHERE a.score>(SELECT sum(score) FROM 成績(jī)表 WHERE id=1)
) b
LEFT JOIN (
SELECT sum(score)as score FROM 成績(jī)表 WHERE id=1
) c on 1=1

應(yīng)該是可以一步到位的，試一試

zhangshanScore = select sum(score) from 成績(jī) g inner join 學(xué)生 s g.student_id = s.id where s.id = 學(xué)生id;

select count(*) + 1 as 排名 from (select sum(score) from 成績(jī) group by student_id having sum(score) > zhangshanScore) as a;
排名是查詢表內(nèi)分?jǐn)?shù)比他多的人數(shù)+1