求每科前十名的学生用row_number和不用row_number的写法

好的，我来回答你的问题。

使用 row_number 的写法：

SELECT subject, name, score
FROM (
  SELECT subject, name, score, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rank
  FROM scores
) AS t
WHERE rank <= 10;

不使用 row_number 的写法：

SELECT s1.subject, s1.name, s1.score
FROM scores s1
WHERE (
  SELECT COUNT(*)
  FROM scores s2
  WHERE s2.subject = s1.subject AND s2.score > s1.score
) < 10;

以上两种写法都可以得到每科前十名的学生。