ソースコード
SELECT *
FROM 
(SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '小学校' AS KIND,
    SUM(ELEMENTARY) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE ELEMENTARY IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE

UNION ALL

SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '中学校' AS KIND,
    SUM(MIDDLE) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE MIDDLE IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
    
UNION ALL

SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '高校' AS KIND,
    SUM(HIGH) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE HIGH IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE

UNION ALL

SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '短大' AS KIND,
    SUM(JUNIOR_CLG) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE JUNIOR_CLG IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
    
UNION ALL

SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学' AS KIND,
    SUM(COLLEGE) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE COLLEGE IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
    
UNION ALL

SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学院' AS KIND,
    SUM(GRADUATE) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE GRADUATE IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
)
ORDER BY
    PF_CODE
提出情報
提出日時2024/09/26 21:23:36
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者tatesoto
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量87 MB
メッセージ
テストケース(通過数／総数)
0/2
状態
メモリ使用量
データパターン1
WA
87 MB
データパターン2
WA
84 MB