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