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