teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問INNER JOINからEXISTSに書き換えたい

編集履歴

質問編集履歴

1

指摘内容の反映（テーブル情報をSQL文に更新）

2019/01/06 06:44

投稿

sendai
sendai

スコア13

test CHANGED
File without changes
test CHANGED
@@ -18,102 +18,182 @@
18
18
 
19
19
  ### 使うテーブル
20
20
 
21
- #### titles
22
-
23
- |emp_no|title|from_date|to_date|
24
-
25
- |:--:|:--:|:--:|:--:|
26
-
27
- |10001|Senior Engineer|1986-06-26|9999-01-01|
28
-
29
- |10002|Staff|1996-08-03|9999-01-01|
30
-
31
- |10003|Senior Engineer|1995-12-03|9999-01-01|
32
-
33
- |10004|Engineer|1986-12-01|1995-12-01|
34
-
35
- |10004|Staff|1995-12-01|9999-01-01|
36
-
37
-
38
-
39
- #### dept_emp
40
-
41
- |emp_no|dept_no|from_date|to_date|
42
-
43
- |:--:|:--:|:--:|:--:|
44
-
45
- |10001|d005|1986-06-26|9999-01-01|
46
-
47
- |10002|d007|1996-08-03|9999-01-01|
48
-
49
- |10003|d004|1995-12-03|9999-01-01|
50
-
51
- |10004|d004|1986-12-01|1995-12-01|
52
-
53
- |10005|d003|1989-09-12|9999-01-01|
54
-
55
-
56
-
57
- #### departments
58
-
59
- |dept_no|dept_name|
21
+ titlesテーブルとdept_empテーブルはそれぞれ443308レコード、331603レコードあってすべてのレコードをINSERT文の中に記述することはできないため、5レコードのみ記載します。
22
+
23
+
24
+
25
+
26
+
27
+ #####titles
28
+
29
+ ```SQL
30
+
31
+ CREATE TABLE "titles"
32
+
33
+ (
34
+
35
+ "emp_no" INT(11) PRIMARY KEY,
36
+
37
+ "title" VARCHAR(50) PRIMARY KEY,
38
+
39
+ "from_date" DATE PRIMARY KEY,
40
+
41
+ "to_date" DATE
42
+
43
+ )
44
+
45
+
46
+
47
+ INSERT INTO "titles"
48
+
49
+ ("emp_no", "title", "from_date", "to_date")
50
+
51
+ VALUES
52
+
53
+ (10001, "Senior Engineer", "1986-06-26", "9999-01-01"),
54
+
55
+ (10002, "Staff", "1996-08-03", "9999-01-01"),
56
+
57
+ (10003, "Senior Engineer", "1995-12-03", "9999-01-01"),
58
+
59
+ (10004, "Engineer", "1986-12-01", "1995-12-01"),
60
+
61
+ (10004, "Staff", "1995-12-01", "9999-01-01")
62
+
63
+
64
+
65
+ ```
66
+
67
+
68
+
69
+
70
+
71
+ ##### dept_emp
72
+
73
+ ```SQL
74
+
75
+ CREATE TABLE "dept_emp"
76
+
77
+ (
78
+
79
+ "emp_no" INT(11) PRIMARY KEY,
80
+
81
+ "dept_no" CHAR(4) PRIMARY KEY,
82
+
83
+ "from_date" DATE,
84
+
85
+ "to_date" DATE
86
+
87
+ )
88
+
89
+
90
+
91
+ INSERT INTO "dept_emp"
92
+
93
+ ("emp_no", "dept_no", "from_date", "to_date")
94
+
95
+ VALUES
96
+
97
+ (10001, "d005", "1986-06-26", "9999-01-01"),
98
+
99
+ (10002, "d007", "1996-08-03", "9999-01-01"),
100
+
101
+ (10003, "d004", "1995-12-03", "9999-01-01"),
102
+
103
+ (10004, "d004", "1986-12-01", "1995-12-01"),
104
+
105
+ (10005, "d003", "1989-09-12", "9999-01-01");
106
+
107
+ ```
108
+
109
+
110
+
111
+ ##### departments
112
+
113
+ ```SQL
114
+
115
+ CREATE TABLE "departments"
116
+
117
+ (
118
+
119
+ "dept_no" CHAR(4) PRIMARY KEY,
120
+
121
+ "dept_name" VARCHAR(40) UNIQUE
122
+
123
+ )
124
+
125
+
126
+
127
+ INSERT INTO "departments"
128
+
129
+ ("dept_no","dept_name")
130
+
131
+ VALUES
132
+
133
+ ("d001", "Marketing"),
134
+
135
+ ("d002", "Finance"),
136
+
137
+ ("d003", "Human Resources"),
138
+
139
+ ("d004", "Production"),
140
+
141
+ ("d005", "Development"),
142
+
143
+ ("d006", "Quality Management"),
144
+
145
+ ("d007", "Sales"),
146
+
147
+ ("d008", "Research"),
148
+
149
+ ("d009", "Customer Service");
150
+
151
+ ```
152
+
153
+
154
+
155
+ ### 試したこと
156
+
157
+
158
+
159
+ 内部結合を用いた集計を行うことには（おそらく）成功しました。
160
+
161
+
162
+
163
+ ```SQL
164
+
165
+ SELECT dept_name, COUNT(*) AS num
166
+
167
+ FROM
168
+
169
+ (SELECT a.emp_no, a.title, a.to_date, c.dept_name
170
+
171
+ FROM titles a
172
+
173
+ INNER JOIN dept_emp b
174
+
175
+ ON a.emp_no=b.emp_no
176
+
177
+ INNER JOIN departments c
178
+
179
+ ON b.dept_no=c.dept_no) joined
180
+
181
+ WHERE title="Staff" AND to_date="9999-01-01"
182
+
183
+ GROUP BY dept_name
184
+
185
+ ORDER BY dept_name;
186
+
187
+ ```
188
+
189
+
190
+
191
+ #### 実行結果
192
+
193
+ |dept_name|num|
60
194
 
61
195
  |:--:|:--:|
62
196
 
63
- |d001|Marketing|
64
-
65
- |d002|Finance|
66
-
67
- |d003|Human Resources|
68
-
69
- |d004|Production|
70
-
71
- |d005|Development|
72
-
73
-
74
-
75
- ### 試したこと
76
-
77
-
78
-
79
- 内部結合を用いた集計を行うことには（おそらく）成功しました。
80
-
81
-
82
-
83
- ```SQL
84
-
85
- SELECT dept_name, COUNT(*) AS num
86
-
87
- FROM
88
-
89
- (SELECT a.emp_no, a.title, a.to_date, c.dept_name
90
-
91
- FROM titles a
92
-
93
- INNER JOIN dept_emp b
94
-
95
- ON a.emp_no=b.emp_no
96
-
97
- INNER JOIN departments c
98
-
99
- ON b.dept_no=c.dept_no) joined
100
-
101
- WHERE title="Staff" AND to_date="9999-01-01"
102
-
103
- GROUP BY dept_name
104
-
105
- ORDER BY dept_name;
106
-
107
- ```
108
-
109
-
110
-
111
- #### 実行結果
112
-
113
- |dept_name|num|
114
-
115
- |:--:|:--:|
116
-
117
197
  |Customer Service|3902|
118
198
 
119
199
  |Development|315|