ソースコード
SELECT SV_YEAR,PREFECTURE,KIND,AMT FROM(
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '小学校' AS KIND,
    SUM(ELEMENTARY) AS AMT, P.PF_CODE
FROM 
PREFECTURE P LEFT JOIN ENROLLMENT_STATUS E ON P.PF_CODE = E.PF_CODE
WHERE E.SURVEY_YEAR = 2020
GROUP BY SURVEY_YEAR, P.PF_CODE
UNION
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '中学校' AS KIND,
    SUM(MIDDLE) AS AMT, P.PF_CODE
FROM 
PREFECTURE P LEFT JOIN ENROLLMENT_STATUS E ON P.PF_CODE = E.PF_CODE
WHERE E.SURVEY_YEAR = 2020
GROUP BY SURVEY_YEAR, P.PF_CODE
UNION
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '高校' AS KIND,
    SUM(HIGH) AS AMT, P.PF_CODE
FROM 
PREFECTURE P LEFT JOIN ENROLLMENT_STATUS E ON P.PF_CODE = E.PF_CODE
WHERE E.SURVEY_YEAR = 2020
GROUP BY SURVEY_YEAR, P.PF_CODE
UNION
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '短大' AS KIND,
    SUM(JUNIOR_CLG) AS AMT, P.PF_CODE
FROM 
PREFECTURE P LEFT JOIN ENROLLMENT_STATUS E ON P.PF_CODE = E.PF_CODE
WHERE E.SURVEY_YEAR = 2020
GROUP BY SURVEY_YEAR, P.PF_CODE
UNION
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学' AS KIND,
    SUM(COLLEGE) AS AMT, P.PF_CODE
FROM 
PREFECTURE P LEFT JOIN ENROLLMENT_STATUS E ON P.PF_CODE = E.PF_CODE
WHERE E.SURVEY_YEAR = 2020
GROUP BY SURVEY_YEAR, P.PF_CODE
UNION
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学院' AS KIND,
    SUM(GRADUATE) AS AMT, P.PF_CODE
FROM 
PREFECTURE P LEFT JOIN ENROLLMENT_STATUS E ON P.PF_CODE = E.PF_CODE
WHERE E.SURVEY_YEAR = 2020
GROUP BY SURVEY_YEAR, P.PF_CODE
) t1
WHERE AMT IS NOT NULL
ORDER BY PF_CODE ASC,
  CASE KIND
  WHEN '小学校' THEN 1
  WHEN '中学校' THEN 2
  WHEN '高校' THEN 3
  WHEN '短大' THEN 4
  WHEN '大学' THEN 5
  WHEN '大学院' THEN 6
  END;
提出情報
提出日時2022/09/21 21:35:39
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者suzuki_osamu
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量100 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
100 MB
データパターン2
AC
93 MB