如何在SQL查询中找到第二高的平均绩点

Question

My database is a SQL server And My Query is Below

SELECT name, MAX(average) AS average
FROM Course
WHERE salary <> (SELECT MAX(salary) 
FROM Course);

我想获得第二名的平均成绩

Answer 1

SELECT name, average AS average
FROM Course
WHERE average = (SELECT Top(2) MAX(salary) 
FROM Course)

Answer 2

没有指定dbms，但MySQL和PostgreSQL可以使用limit和offset来获得第二高的等级。删除average列上的MAX聚合

SELECT name, average AS average
FROM Course
WHERE salary <> (SELECT MAX(salary) 
FROM Course)
ORDER BY average DESC
LIMIT 1,2;

In MSSQL>2012你可以使用OFFSET和FETCH

OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;