質問編集履歴
3
実行したSQLの記載ミス
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -174,18 +174,30 @@
|
|
|
174
174
|
|
|
175
175
|
実行したsql：
|
|
176
176
|
|
|
177
|
-
|
|
177
|
+
select
|
|
178
|
-
|
|
178
|
+
|
|
179
|
-
|
|
179
|
+
t1.user_id
|
|
180
|
-
|
|
180
|
+
|
|
181
|
-
|
|
181
|
+
,count(t1.user_id) as '2月のログイン回数'
|
|
182
|
-
|
|
182
|
+
|
|
183
|
-
|
|
183
|
+
from login_log t1
|
|
184
|
+
|
|
184
|
-
|
|
185
|
+
left join charge_log t2
|
|
186
|
+
|
|
187
|
+
on t1.user_id = t2.user_id
|
|
188
|
+
|
|
185
|
-
|
|
189
|
+
where month(t1.timestamp) = 2
|
|
190
|
+
|
|
186
|
-
|
|
191
|
+
and
|
|
192
|
+
|
|
193
|
+
t2.category = 'buy'
|
|
194
|
+
|
|
195
|
+
and
|
|
196
|
+
|
|
197
|
+
month(t2.timestamp) = 1
|
|
198
|
+
|
|
187
|
-
|
|
199
|
+
group by
|
|
188
|
-
|
|
200
|
+
|
|
189
|
-
t
|
|
201
|
+
t1.user_id
|
|
190
202
|
|
|
191
203
|
;
|
2
誤字
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -136,7 +136,7 @@
|
|
|
136
136
|
|
|
137
137
|
AND
|
|
138
138
|
|
|
139
|
-
MONTH(t2.timestamp) =
|
|
139
|
+
MONTH(t2.timestamp) = 2
|
|
140
140
|
|
|
141
141
|
LEFT JOIN quest_log t3
|
|
142
142
|
|
|
@@ -146,7 +146,7 @@
|
|
|
146
146
|
|
|
147
147
|
AND
|
|
148
148
|
|
|
149
|
-
MONTH(t3.timestamp) =
|
|
149
|
+
MONTH(t3.timestamp) = 2
|
|
150
150
|
|
|
151
151
|
WHERE
|
|
152
152
|
|
|
@@ -154,7 +154,7 @@
|
|
|
154
154
|
|
|
155
155
|
AND
|
|
156
156
|
|
|
157
|
-
MONTH(t1.timestamp) =
|
|
157
|
+
MONTH(t1.timestamp) = 1
|
|
158
158
|
|
|
159
159
|
GROUP BY
|
|
160
160
|
|
|
@@ -178,11 +178,11 @@
|
|
|
178
178
|
|
|
179
179
|
t2.user_id
|
|
180
180
|
|
|
181
|
-
,COUNT(t2.user_id) AS '
|
|
181
|
+
,COUNT(t2.user_id) AS '2月のログイン回数'
|
|
182
182
|
|
|
183
183
|
FROM login_log t2
|
|
184
184
|
|
|
185
|
-
WHERE MONTH(t2.timestamp) =
|
|
185
|
+
WHERE MONTH(t2.timestamp) = 2
|
|
186
186
|
|
|
187
187
|
GROUP BY
|
|
188
188
|
|
1
テーブルの説明をCREATE TABLE文とINSERT文に修正。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -1,4 +1,50 @@
|
|
|
1
|
+
```mysql
|
|
2
|
+
|
|
3
|
+
create table mydb.login_log (user_id integer, timestamp timestamp);
|
|
4
|
+
|
|
5
|
+
create table mydb.charge_log (user_id integer, timestamp timestamp, category char(10));
|
|
6
|
+
|
|
7
|
+
create table mydb.quest_log (user_id integer, timestamp timestamp, quest_id integer);
|
|
8
|
+
|
|
9
|
+
|
|
10
|
+
|
|
11
|
+
LOAD DATA LOCAL INFILE 'C:/Windows/Temp/login_log.csv' INTO TABLE mydb.login_log
|
|
12
|
+
|
|
13
|
+
FIELDS
|
|
14
|
+
|
|
15
|
+
TERMINATED BY ','
|
|
16
|
+
|
|
17
|
+
ENCLOSED BY '"'
|
|
18
|
+
|
|
19
|
+
LINES TERMINATED BY '\r\n';
|
|
20
|
+
|
|
21
|
+
|
|
22
|
+
|
|
23
|
+
LOAD DATA LOCAL INFILE 'C:/Windows/Temp/charge_log.csv' INTO TABLE mydb.charge_log
|
|
24
|
+
|
|
25
|
+
FIELDS
|
|
26
|
+
|
|
27
|
+
TERMINATED BY ','
|
|
28
|
+
|
|
29
|
+
ENCLOSED BY '"'
|
|
30
|
+
|
|
31
|
+
LINES TERMINATED BY '\r\n';
|
|
32
|
+
|
|
33
|
+
|
|
34
|
+
|
|
35
|
+
LOAD DATA LOCAL INFILE 'C:/Windows/Temp/quest_log.csv' INTO TABLE mydb.quest_log
|
|
36
|
+
|
|
37
|
+
FIELDS
|
|
38
|
+
|
|
39
|
+
TERMINATED BY ','
|
|
40
|
+
|
|
41
|
+
ENCLOSED BY '"'
|
|
42
|
+
|
|
43
|
+
LINES TERMINATED BY '\r\n';
|
|
44
|
+
|
|
45
|
+
|
|
46
|
+
|
|
1
|
-
### 前提・実現したいこと
|
|
47
|
+
```### 前提・実現したいこと
|
|
2
48
|
|
|
3
49
|
sqlで、複数の条件に合致したデータを抽出したい。
|
|
4
50
|
|