ソースコード
SELECT
SV_YEAR,
PREFECTURE,
KIND,
AMT
FROM
(
  SELECT
   1 AS no,
   SURVEY_YEAR AS "SV_YEAR",
   PF_NAME AS "PREFECTURE",
   '小学校' AS "KIND",
   SUM(ELEMENTARY)  AS "AMT",
   ES.PF_CODE
   FROM
    ENROLLMENT_STATUS AS ES
   JOIN
    PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
   WHERE
    ELEMENTARY IS NOT null
    AND SURVEY_YEAR = '2020'
   GROUP BY
    SURVEY_YEAR,
    PF_NAME
  UNION
  SELECT
   2 AS no,
   SURVEY_YEAR AS "SV_YEAR",
   PF_NAME AS "PREFECTURE",
   '中学校' AS "KIND",
   SUM(MIDDLE)  AS "AMT",
   ES.PF_CODE
   FROM
    ENROLLMENT_STATUS AS ES
   JOIN
    PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
   WHERE
    MIDDLE IS NOT null
    AND SURVEY_YEAR = '2020'
   GROUP BY
    SURVEY_YEAR,
    PF_NAME
  UNION
  SELECT
     3 AS no,
   SURVEY_YEAR AS "SV_YEAR",
   PF_NAME AS "PREFECTURE",
   '高校' AS "KIND",
   SUM(HIGH)  AS "AMT",
   ES.PF_CODE
   FROM
    ENROLLMENT_STATUS AS ES
   JOIN
    PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
   WHERE
    HIGH IS NOT null
    AND SURVEY_YEAR = '2020'
   GROUP BY
    SURVEY_YEAR,
    PF_NAME
  UNION
  SELECT
   4 AS no,
   SURVEY_YEAR AS "SV_YEAR",
   PF_NAME AS "PREFECTURE",
   '短大' AS "KIND",
   SUM(JUNIOR_CLG)  AS "AMT",
   ES.PF_CODE
   FROM
    ENROLLMENT_STATUS AS ES
   JOIN
    PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
   WHERE
    JUNIOR_CLG IS NOT null
    AND SURVEY_YEAR = '2020'
   GROUP BY
    SURVEY_YEAR,
    PF_NAME
  UNION
  SELECT
   5 AS no,
   SURVEY_YEAR AS "SV_YEAR",
   PF_NAME AS "PREFECTURE",
   '大学' AS "KIND",
   SUM(COLLEGE)  AS "AMT",
   ES.PF_CODE
   FROM
    ENROLLMENT_STATUS AS ES
   JOIN
    PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
   WHERE
    COLLEGE IS NOT null
    AND SURVEY_YEAR = '2020'
   GROUP BY
    SURVEY_YEAR,
    PF_NAME
  UNION
  SELECT
   6 AS no,
   SURVEY_YEAR AS "SV_YEAR",
   PF_NAME AS "PREFECTURE",
   '大学院' AS "KIND",
   SUM(GRADUATE)  AS "AMT",
   ES.PF_CODE
   FROM
    ENROLLMENT_STATUS AS ES
   JOIN
    PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
   WHERE
    GRADUATE IS NOT null
    AND SURVEY_YEAR = '2020'
   GROUP BY
    SURVEY_YEAR,
    PF_NAME
)
ORDER BY
   PF_CODE,
   no
提出情報
提出日時2022/09/21 18:44:50
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者sakula9384
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
102 MB
データパターン2
AC
99 MB