SQL入門
SQL基礎問題7：成績表から各教科の最高点と最低点を抽出

SELECT T.教科名,
        CASE WHEN T.点数 = M.最大点 THEN '最高'
             WHEN T.点数 = M.最小点 THEN '最低'
        END AS 区分,
        T.生徒名,T.点数
 FROM TBL成績 AS T
 INNER JOIN (SELECT 教科名,MAX(点数) AS 最大点,MIN(点数) AS 最小点
             FROM TBL成績
             GROUP BY 教科名
            ) AS M
         ON T.教科名 = M.教科名
 WHERE T.点数 = M.最大点 OR T.点数 = M.最小点
 ORDER BY T.教科名,T.点数 DESC

SELECT 教科名,区分,生徒名,点数
 FROM (
   SELECT T.教科名,'最高' AS 区分,T.生徒名,T.点数
     FROM TBL成績 AS T
     INNER JOIN (SELECT 教科名, MAX(点数) AS 点数 FROM TBL成績 GROUP BY 教科名) AS M
             ON T.教科名 = M.教科名 AND T.点数 = M.点数
   UNION ALL
   SELECT T.教科名,'最低' AS 区分,T.生徒名,T.点数
     FROM TBL成績 AS T
     INNER JOIN (SELECT 教科名, MIN(点数) AS 点数 FROM TBL成績 GROUP BY 教科名) AS N
        ON T.教科名 = N.教科名 AND T.点数 = N.点数
 ) AS UNIONED_RESULT
 ORDER BY 教科名,点数 DESC

SELECT T.教科名,COALESCE(M.区分, N.区分) AS 区分,T.生徒名,T.点数
 FROM TBL成績 AS T
 LEFT JOIN (SELECT 教科名, MAX(点数) AS 点数, '最高' AS 区分 FROM TBL成績 GROUP BY 教科名) AS M
        ON T.教科名 = M.教科名 AND T.点数 = M.点数
 LEFT JOIN (SELECT 教科名, MIN(点数) AS 点数, '最低' AS 区分 FROM TBL成績 GROUP BY 教科名) AS N
        ON T.教科名 = N.教科名 AND T.点数 = N.点数
WHERE M.区分 IS NOT NULL OR N.区分 IS NOT NULL
ORDER BY T.教科名,T.点数 DESC

SELECT R.教科名,
        CASE WHEN 順位_降順 = 1 THEN '最高'
             WHEN 順位_昇順 = 1 THEN '最低'
        END AS 区分,
        R.生徒名,R.点数
FROM (SELECT T.教科名,T.生徒名,T.点数,
             RANK() OVER (PARTITION BY T.教科名 ORDER BY T.点数 DESC) AS 順位_降順,
             RANK() OVER (PARTITION BY T.教科名 ORDER BY T.点数 ASC) AS 順位_昇順
      FROM TBL成績 AS T
     ) AS R
WHERE 順位_降順 = 1 OR 順位_昇順 = 1
ORDER BY R.教科名,R.点数 DESC

WITH 教科別集計 AS (
      SELECT 教科名,MAX(点数) AS 最高点,MIN(点数) AS 最低点
      FROM TBL成績
      GROUP BY 教科名
 )
 SELECT T.教科名,
        CASE WHEN T.点数 = M.最高点 THEN '最高'
             WHEN T.点数 = M.最低点 THEN '最低'
        END AS 区分,
        T.生徒名,T.点数
 FROM TBL成績 AS T
 INNER JOIN 教科別集計 AS M
         ON T.教科名 = M.教科名
 WHERE T.点数 = M.最高点 OR T.点数 = M.最低点
 ORDER BY T.教科名,T.点数 DESC

WITH 順位付け結果 AS (
      SELECT T.教科名,T.生徒名,T.点数,
             RANK() OVER (PARTITION BY T.教科名 ORDER BY T.点数 DESC) AS 順位_降順,
             RANK() OVER (PARTITION BY T.教科名 ORDER BY T.点数 ASC) AS 順位_昇順
      FROM TBL成績 AS T
 )
 SELECT R.教科名,
        CASE WHEN 順位_降順 = 1 THEN '最高'
             WHEN 順位_昇順 = 1 THEN '最低'
        END AS 区分,
        R.生徒名,R.点数
 FROM 順位付け結果 AS R
 WHERE 順位_降順 = 1 OR 順位_昇順 = 1
 ORDER BY R.教科名,R.点数 DESC