sql面试题

表字段 学号sno，课程号cno，分数score

查询每门课程前两名

mysql

方式1

SELECT
	a.* 
FROM
	sc a
	LEFT JOIN sc b ON a.cno = b.cno 
 and
 a.score<=b.score
GROUP BY
	a.sno,
	a.cno
HAVING
	COUNT( a.score ) <= 2
ORDER BY
	a.cno,
	a.score DESC;

方式2

SELECT
	* 
FROM
	sc a 
WHERE
	( SELECT count( 1 ) FROM sc b WHERE a.cno = b.cno AND a.score <= b.score ) <= 2 
ORDER BY
	a.cno,
	a.score DESC;

方式3

SELECT
	* 
FROM
	sc a 
WHERE
	sno IN (
	SELECT
		* 
	FROM
		( SELECT sno FROM sc b WHERE b.cno = a.cno ORDER BY b.score DESC LIMIT 2 ) T 
	) 
ORDER BY
	cno,
	score DESC

查每科成绩和平均分的比值

SELECT
	a.*,
	b.avg,
	a.score / b.avg 
FROM
	sc a
	LEFT JOIN ( SELECT sno, AVG( score ) AS avg FROM sc GROUP BY sno ) b ON a.sno = b.sno