SQL入門
SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出

SELECT T1.教科名,T1.生徒名,T1.点数
 FROM TBL成績 AS T1
 WHERE
   (SELECT COUNT(*)
    FROM TBL成績 AS T2
    WHERE T2.教科名 = T1.教科名 AND T2.点数 > T1.点数
   ) + 1 <= 3
 ORDER BY T1.教科名, T1.点数 DESC

SELECT T1.教科名,T1.生徒名,T1.点数
 FROM TBL成績 AS T1
 LEFT JOIN TBL成績 AS T2 ON T1.教科名 = T2.教科名 AND T1.点数 < T2.点数
 GROUP BY T1.教科名,T1.生徒名,T1.点数
 HAVING COUNT(T2.点数) <= 2
 ORDER BY T1.教科名,T1.点数 DESC

SELECT T1.教科名,T1.生徒名,T1.点数
 FROM
   (SELECT T2.教科名,T2.生徒名,T2.点数,
    RANK() OVER (PARTITION BY T2.教科名 ORDER BY T2.点数 DESC) AS rank_num
    FROM TBL成績 AS T2
   ) AS T1
 WHERE rank_num <= 3
 ORDER BY T1.教科名,T1.点数 DESC

=LET(sql,
"SELECT T.教科名,T.生徒名,T.点数
 FROM ?TBL AS T
 WHERE
   (SELECT COUNT(*)
    FROM ?TBL AS S
    WHERE S.教科名 = T.教科名 AND S.点数 > T.点数
   ) + 1 <= 3
 ORDER BY 教科名, 点数 DESC",
QUERY(SUBSTITUTE(sql,"?TBL","[TBL成績$A2:D20]")))

=LET(sql,
"SELECT T1.教科名,T1.生徒名,T1.点数
 FROM ?TBL AS T1
 LEFT JOIN ?TBL AS T2 ON T1.教科名 = T2.教科名 AND T1.点数 < T2.点数
 GROUP BY T1.教科名,T1.生徒名,T1.点数
 HAVING COUNT(T2.点数) <= 2
 ORDER BY T1.教科名,T1.点数 DESC",
QUERY(SUBSTITUTE(sql,"?TBL","[TBL成績$A2:D20]")))