ソースコード

SELECT A.PF_CODE AS [都道府県コード]
      ,A.PF_NAME AS [都道府県名]
      ,NATION1.NATION_NAME AS [1位 国名]
      ,B.AMT1  AS [1位 人数]
      ,NATION2.NATION_NAME AS [2位 国名]
      ,B.AMT2  AS [2位 人数]
      ,NATION3.NATION_NAME AS [3位 国名]
      ,B.AMT3  AS [3位 人数]
      ,B.AMT1 + B.AMT2 + B.AMT3  AS [合計人数] 

FROM PREFECTURE A
INNER JOIN 
(
SELECT sub.PF_CODE 
      ,MAX(CASE WHEN sub.Rank = 1 THEN sub.NATION_CODE ELSE '' END) AS NATION_CODE1
      ,MAX(CASE WHEN sub.Rank = 1 THEN sub.AMT ELSE 0 END) AS AMT1
      ,MAX(CASE WHEN sub.Rank = 2 THEN sub.NATION_CODE ELSE '' END) AS NATION_CODE2
      ,MAX(CASE WHEN sub.Rank = 2 THEN sub.AMT ELSE 0 END) AS AMT2
      ,MAX(CASE WHEN sub.Rank = 3 THEN sub.NATION_CODE ELSE '' END) AS NATION_CODE3
      ,MAX(CASE WHEN sub.Rank = 3 THEN sub.AMT ELSE 0 END) AS AMT3
FROM(
SELECT a.PF_CODE
      ,a.NATION_CODE
      ,a.AMT
      ,ROW_NUMBER() OVER(PARTITION BY a.PF_CODE
                         ORDER BY a.AMT DESC) AS Rank
FROM FOREIGNER a
WHERE a.NATION_CODE <> '113'
)sub
GROUP BY sub.PF_CODE
)B
ON A.PF_CODE = B.PF_CODE
INNER JOIN NATIONALITY NATION1
ON B.NATION_CODE1 = NATION1.NATION_CODE
INNER JOIN NATIONALITY NATION2
ON B.NATION_CODE2 = NATION2.NATION_CODE
INNER JOIN NATIONALITY NATION3
ON B.NATION_CODE3 = NATION3.NATION_CODE
ORDER BY B.AMT1 + B.AMT2 + B.AMT3 DESC
        ,A.PF_CODE
提出情報
提出日時2022/07/20 17:49:58
コンテスト第1回 SQLコンテスト
問題外国籍分布
受験者HoneyPlease
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量104 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
104 MB
データパターン2
WA
97 MB