回答編集履歴
8
スキーマの削除
test
CHANGED
@@ -34,9 +34,9 @@
|
|
34
34
|
|
35
35
|
c.course_name
|
36
36
|
|
37
|
-
FROM
|
37
|
+
FROM course c
|
38
38
|
|
39
|
-
LEFT JOIN
|
39
|
+
LEFT JOIN prereq p ON c.course_number = p.course_number
|
40
40
|
|
41
41
|
START WITH p.prereq IS NULL
|
42
42
|
|
7
追記
test
CHANGED
@@ -1,6 +1,6 @@
|
|
1
1
|
下記としてみましたが、いかがでしょうか。
|
2
2
|
|
3
|
-
ただし、
|
3
|
+
ただし、必修科目(prereq)として、さらにその前提として複数の必修科目を持つ科目を指定した場合に、SYS_CONNECT_BY_PATHで取得する都合上、講義コード(course_number)が重複して出力されてしまうようです。
|
4
4
|
|
5
5
|
|
6
6
|
|
@@ -48,6 +48,4 @@
|
|
48
48
|
|
49
49
|
ORDER BY 1;
|
50
50
|
|
51
|
-
|
52
|
-
|
53
51
|
```
|
6
追記
test
CHANGED
@@ -1,4 +1,6 @@
|
|
1
1
|
下記としてみましたが、いかがでしょうか。
|
2
|
+
|
3
|
+
ただし、前提科目(prereq)として複数の前提科目を持つ科目、を指定した場合に講義コードが重複して出力されてしまうようです。
|
2
4
|
|
3
5
|
|
4
6
|
|
5
スキーマの付与
test
CHANGED
@@ -32,9 +32,9 @@
|
|
32
32
|
|
33
33
|
c.course_name
|
34
34
|
|
35
|
-
FROM course c
|
35
|
+
FROM eb.course c
|
36
36
|
|
37
|
-
LEFT JOIN prereq p ON c.course_number = p.course_number
|
37
|
+
LEFT JOIN eb.prereq p ON c.course_number = p.course_number
|
38
38
|
|
39
39
|
START WITH p.prereq IS NULL
|
40
40
|
|
4
SQLの修正
test
CHANGED
@@ -18,27 +18,27 @@
|
|
18
18
|
|
19
19
|
SELECT
|
20
20
|
|
21
|
-
course_number,
|
21
|
+
c.course_number ,
|
22
22
|
|
23
|
-
CASE
|
23
|
+
CASE
|
24
24
|
|
25
|
-
|
25
|
+
WHEN SYS_CONNECT_BY_PATH(p.prereq, '/') = '/'
|
26
26
|
|
27
|
-
|
27
|
+
THEN ''
|
28
28
|
|
29
|
-
(
|
29
|
+
ELSE SYS_CONNECT_BY_PATH(p.prereq, '/')
|
30
30
|
|
31
|
-
|
31
|
+
END prereq,
|
32
32
|
|
33
|
-
|
33
|
+
c.course_name
|
34
34
|
|
35
|
-
|
35
|
+
FROM course c
|
36
36
|
|
37
|
-
|
37
|
+
LEFT JOIN prereq p ON c.course_number = p.course_number
|
38
38
|
|
39
|
-
START WITH prereq IS NULL
|
39
|
+
START WITH p.prereq IS NULL
|
40
40
|
|
41
|
-
CONNECT BY PRIOR course_number = prereq
|
41
|
+
CONNECT BY PRIOR c.course_number = p.prereq
|
42
42
|
|
43
43
|
) t
|
44
44
|
|
@@ -46,4 +46,6 @@
|
|
46
46
|
|
47
47
|
ORDER BY 1;
|
48
48
|
|
49
|
+
|
50
|
+
|
49
51
|
```
|
3
スキーマの付与
test
CHANGED
@@ -30,9 +30,9 @@
|
|
30
30
|
|
31
31
|
SELECT c.course_name, p.prereq, c.course_number
|
32
32
|
|
33
|
-
FROM course c
|
33
|
+
FROM eb.course c
|
34
34
|
|
35
|
-
LEFT JOIN prereq p ON c.course_number = p.course_number
|
35
|
+
LEFT JOIN eb.prereq p ON c.course_number = p.course_number
|
36
36
|
|
37
37
|
) t
|
38
38
|
|
2
書式の改善
test
CHANGED
@@ -4,13 +4,25 @@
|
|
4
4
|
|
5
5
|
```sql
|
6
6
|
|
7
|
+
SELECT
|
8
|
+
|
9
|
+
course_number "Course",
|
10
|
+
|
7
|
-
|
11
|
+
COALESCE(REPLACE(LISTAGG(prereq, '/') WITHIN GROUP (ORDER BY prereq ASC NULLS LAST), '//', ''), '-') "Prerequisite(s)",
|
12
|
+
|
13
|
+
course_name "Course Name"
|
8
14
|
|
9
15
|
FROM
|
10
16
|
|
11
17
|
(
|
12
18
|
|
19
|
+
SELECT
|
20
|
+
|
21
|
+
course_number,
|
22
|
+
|
13
|
-
|
23
|
+
CASE WHEN SYS_CONNECT_BY_PATH(prereq, '/') = '/' THEN '' ELSE SYS_CONNECT_BY_PATH(prereq, '/') END prereq,
|
24
|
+
|
25
|
+
course_name
|
14
26
|
|
15
27
|
FROM
|
16
28
|
|
1
SQL修正
test
CHANGED
@@ -1,22 +1,16 @@
|
|
1
|
-
ま
|
1
|
+
下記としてみましたが、いかがでしょうか。
|
2
2
|
|
3
3
|
|
4
4
|
|
5
5
|
```sql
|
6
6
|
|
7
|
-
SELECT
|
8
|
-
|
9
|
-
t1.course_number
|
10
|
-
|
11
|
-
|
7
|
+
SELECT course_number, COALESCE(REPLACE(LISTAGG(prereq, '/') WITHIN GROUP (ORDER BY prereq ASC NULLS LAST), '//', ''), '-') prereq, course_name
|
12
|
-
|
13
|
-
, t1.course_name
|
14
8
|
|
15
9
|
FROM
|
16
10
|
|
17
|
-
|
11
|
+
(
|
18
12
|
|
19
|
-
SELECT course_n
|
13
|
+
SELECT course_number, CASE WHEN SYS_CONNECT_BY_PATH(prereq, '/') = '/' THEN '' ELSE SYS_CONNECT_BY_PATH(prereq, '/') END prereq, course_name
|
20
14
|
|
21
15
|
FROM
|
22
16
|
|
@@ -34,38 +28,10 @@
|
|
34
28
|
|
35
29
|
CONNECT BY PRIOR course_number = prereq
|
36
30
|
|
37
|
-
|
31
|
+
) t
|
38
32
|
|
39
|
-
|
33
|
+
GROUP BY course_number, course_name
|
40
34
|
|
41
|
-
(
|
42
|
-
|
43
|
-
SELECT course_number, prereq
|
44
|
-
|
45
|
-
FROM
|
46
|
-
|
47
|
-
(
|
48
|
-
|
49
|
-
SELECT p.prereq, c.course_number
|
50
|
-
|
51
|
-
FROM course c
|
52
|
-
|
53
|
-
LEFT JOIN prereq p ON c.course_number = p.course_number
|
54
|
-
|
55
|
-
) t
|
56
|
-
|
57
|
-
START WITH prereq IS NULL
|
58
|
-
|
59
|
-
CONNECT BY PRIOR course_number = prereq
|
60
|
-
|
61
|
-
) t2
|
62
|
-
|
63
|
-
ON t1.prereq = t2.course_number
|
64
|
-
|
65
|
-
GROUP BY
|
66
|
-
|
67
|
-
t1.course_number, t1.course_name
|
68
|
-
|
69
|
-
ORDER BY 1;
|
35
|
+
ORDER BY 1;
|
70
36
|
|
71
37
|
```
|