質問編集履歴
5
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -38,7 +38,7 @@
|
|
|
38
38
|
|
|
39
39
|
CREATE TABLE Table2
|
|
40
40
|
|
|
41
|
-
("column1" varchar2(4), "column2" varchar2(4), "column3" varchar2(
|
|
41
|
+
("column1" varchar2(4), "column2" varchar2(4), "column3" varchar2(10))
|
|
42
42
|
|
|
43
43
|
;
|
|
44
44
|
|
4
SQL修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -20,7 +20,7 @@
|
|
|
20
20
|
|
|
21
21
|
INTO Table1 ("column1", "column2")
|
|
22
22
|
|
|
23
|
-
VALUES ('REI1', 100
|
|
23
|
+
VALUES ('REI1', 100)
|
|
24
24
|
|
|
25
25
|
INTO Table1 ("column1", "column2")
|
|
26
26
|
|
3
修正
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -53,10 +53,6 @@
|
|
|
53
53
|
INTO Table2 ("column1", "column2", "column3")
|
|
54
54
|
|
|
55
55
|
VALUES ('REI1', '101', 'かめ')
|
|
56
|
-
|
|
57
|
-
INTO Table2 ("column1", "column2", "column3")
|
|
58
|
-
|
|
59
|
-
VALUES ('REI1', '400', 'かめ')
|
|
60
56
|
|
|
61
57
|
INTO Table2 ("column1", "column2", "column3")
|
|
62
58
|
|
2
CREATE追加
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -33,6 +33,44 @@
|
|
|
33
33
|
```
|
|
34
34
|
|
|
35
35
|
|
|
36
|
+
|
|
37
|
+
```SQL
|
|
38
|
+
|
|
39
|
+
CREATE TABLE Table2
|
|
40
|
+
|
|
41
|
+
("column1" varchar2(4), "column2" varchar2(4), "column3" varchar2(2))
|
|
42
|
+
|
|
43
|
+
;
|
|
44
|
+
|
|
45
|
+
|
|
46
|
+
|
|
47
|
+
INSERT ALL
|
|
48
|
+
|
|
49
|
+
INTO Table2 ("column1", "column2", "column3")
|
|
50
|
+
|
|
51
|
+
VALUES ('REI1', '100', 'ねこ')
|
|
52
|
+
|
|
53
|
+
INTO Table2 ("column1", "column2", "column3")
|
|
54
|
+
|
|
55
|
+
VALUES ('REI1', '101', 'かめ')
|
|
56
|
+
|
|
57
|
+
INTO Table2 ("column1", "column2", "column3")
|
|
58
|
+
|
|
59
|
+
VALUES ('REI1', '400', 'かめ')
|
|
60
|
+
|
|
61
|
+
INTO Table2 ("column1", "column2", "column3")
|
|
62
|
+
|
|
63
|
+
VALUES ('REI2', NULL, 'いぬ')
|
|
64
|
+
|
|
65
|
+
INTO Table2 ("column1", "column2", "column3")
|
|
66
|
+
|
|
67
|
+
VALUES ('REI3', '200', 'とり')
|
|
68
|
+
|
|
69
|
+
INTO Table2 ("column1", "column2", "column3")
|
|
70
|
+
|
|
71
|
+
VALUES ('REI3', '300', 'へび')
|
|
72
|
+
|
|
73
|
+
```
|
|
36
74
|
|
|
37
75
|
|
|
38
76
|
|
1
CREATE/INSERT文を追加し、SQL文については「コードの挿入」で整形しました。半角文字・全角文字も統一させました
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -6,15 +6,39 @@
|
|
|
6
6
|
|
|
7
7
|
|
|
8
8
|
|
|
9
|
-
|
|
9
|
+
```SQL
|
|
10
10
|
|
|
11
|
-
|
|
11
|
+
CREATE TABLE Table1
|
|
12
|
+
|
|
13
|
+
("column1" varchar2(4) PRIMARY KEY, "column2" varchar2(3) )
|
|
14
|
+
|
|
15
|
+
;
|
|
12
16
|
|
|
13
17
|
|
|
14
18
|
|
|
15
|
-
|
|
19
|
+
INSERT ALL
|
|
16
20
|
|
|
21
|
+
INTO Table1 ("column1", "column2")
|
|
22
|
+
|
|
23
|
+
VALUES ('REI1', 100
|
|
24
|
+
|
|
25
|
+
INTO Table1 ("column1", "column2")
|
|
26
|
+
|
|
27
|
+
VALUES ('REI2', 101)
|
|
28
|
+
|
|
29
|
+
INTO Table1 ("column1", "column2")
|
|
30
|
+
|
|
31
|
+
VALUES ('REI3', 102)
|
|
32
|
+
|
|
33
|
+
```
|
|
34
|
+
|
|
35
|
+
|
|
36
|
+
|
|
37
|
+
|
|
38
|
+
|
|
39
|
+
######<Table1>
|
|
40
|
+
|
|
17
|
-
|
|
|
41
|
+
|column1|column2|
|
|
18
42
|
|
|
19
43
|
|:--|:--:|
|
|
20
44
|
|
|
@@ -26,13 +50,15 @@
|
|
|
26
50
|
|
|
27
51
|
|
|
28
52
|
|
|
29
|
-
######<
|
|
53
|
+
######<Table2>
|
|
30
54
|
|
|
31
|
-
|
|
|
55
|
+
|column1|column2|column3|
|
|
32
56
|
|
|
33
57
|
|:--|:--:|:--:|
|
|
34
58
|
|
|
35
59
|
|REI1|100|ねこ|
|
|
60
|
+
|
|
61
|
+
|REI1|101|かめ|
|
|
36
62
|
|
|
37
63
|
|REI2|null|いぬ|
|
|
38
64
|
|
|
@@ -44,7 +70,7 @@
|
|
|
44
70
|
|
|
45
71
|
######<ほしい結果>
|
|
46
72
|
|
|
47
|
-
|
|
|
73
|
+
|column1|column3|
|
|
48
74
|
|
|
49
75
|
|:--|:--:|
|
|
50
76
|
|
|
@@ -56,25 +82,31 @@
|
|
|
56
82
|
|
|
57
83
|
|
|
58
84
|
|
|
59
|
-
|
|
60
|
-
|
|
61
|
-
|
|
85
|
+
ほしい結果を、一回のSQLで得る方法はありますでしょうか。
|
|
62
86
|
|
|
63
87
|
|
|
64
88
|
|
|
65
|
-
**①
|
|
89
|
+
**①column1とcolumn2を結合項目にいれてINNER JOINします。**
|
|
66
90
|
|
|
91
|
+
```SQL
|
|
92
|
+
|
|
67
|
-
（SELECT
|
|
93
|
+
（SELECT Table1.column1,column3 FROM Table1 INNER JOIN Table2 ON Table1.column1 = Table2.column1 AND Table1.column2 = Table2.column2）
|
|
94
|
+
|
|
95
|
+
```
|
|
68
96
|
|
|
69
97
|
|
|
70
98
|
|
|
71
|
-
①のSQLではREI2とREI3の
|
|
99
|
+
①のSQLではREI2とREI3のcolumn3が取得できないので、
|
|
72
100
|
|
|
73
101
|
①で取得できなかった場合、
|
|
74
102
|
|
|
75
103
|
**②REI2とREI3に関しては結合項目を列１のみにしたいです。またREI3については一件のみ取得したいです。**
|
|
76
104
|
|
|
105
|
+
```SQL
|
|
106
|
+
|
|
77
|
-
（SELECT
|
|
107
|
+
（SELECT Table1.column1,min(column3) FROM Table1 INNER JOIN Table2 ON Table1.column1 = Table2.column1 GROUP BY Table1.column1 ）
|
|
108
|
+
|
|
109
|
+
```
|
|
78
110
|
|
|
79
111
|
|
|
80
112
|
|