回答編集履歴
1
SQLが誤っていたので修正
answer
CHANGED
|
@@ -3,12 +3,12 @@
|
|
|
3
3
|
```SQL
|
|
4
4
|
SELECT
|
|
5
5
|
employee_id,
|
|
6
|
-
MAX(CASE WHEN
|
|
6
|
+
MAX(CASE WHEN rnk = 1 THEN role_id END) AS role1,
|
|
7
|
-
MAX(CASE WHEN
|
|
7
|
+
MAX(CASE WHEN rnk = 2 THEN role_id END) AS role2
|
|
8
8
|
FROM (
|
|
9
9
|
SELECT
|
|
10
10
|
*,
|
|
11
|
-
|
|
11
|
+
ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY role_id) AS rnk
|
|
12
12
|
FROM employee
|
|
13
13
|
) AS e
|
|
14
14
|
GROUP BY employee_id;
|