質問編集履歴
3
実行したSQLの記載ミス
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -86,11 +86,17 @@
|
|
|
86
86
|
全体のsqlに組み込む方法がわからない。
|
|
87
87
|
|
|
88
88
|
実行したsql：
|
|
89
|
-
|
|
89
|
+
select
|
|
90
|
-
|
|
90
|
+
t1.user_id
|
|
91
|
-
|
|
91
|
+
,count(t1.user_id) as '2月のログイン回数'
|
|
92
|
-
|
|
92
|
+
from login_log t1
|
|
93
|
+
left join charge_log t2
|
|
94
|
+
on t1.user_id = t2.user_id
|
|
93
|
-
|
|
95
|
+
where month(t1.timestamp) = 2
|
|
96
|
+
and
|
|
97
|
+
t2.category = 'buy'
|
|
98
|
+
and
|
|
99
|
+
month(t2.timestamp) = 1
|
|
94
|
-
|
|
100
|
+
group by
|
|
95
|
-
|
|
101
|
+
t1.user_id
|
|
96
102
|
;
|
2
誤字
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -67,16 +67,16 @@
|
|
|
67
67
|
ON
|
|
68
68
|
t1.user_id = t2.user_id
|
|
69
69
|
AND
|
|
70
|
-
MONTH(t2.timestamp) =
|
|
70
|
+
MONTH(t2.timestamp) = 2
|
|
71
71
|
LEFT JOIN quest_log t3
|
|
72
72
|
ON
|
|
73
73
|
t1.user_id = t3.user_id
|
|
74
74
|
AND
|
|
75
|
-
MONTH(t3.timestamp) =
|
|
75
|
+
MONTH(t3.timestamp) = 2
|
|
76
76
|
WHERE
|
|
77
77
|
t1.category = 'buy'
|
|
78
78
|
AND
|
|
79
|
-
MONTH(t1.timestamp) =
|
|
79
|
+
MONTH(t1.timestamp) = 1
|
|
80
80
|
GROUP BY
|
|
81
81
|
t1.user_id
|
|
82
82
|
;
|
|
@@ -88,9 +88,9 @@
|
|
|
88
88
|
実行したsql：
|
|
89
89
|
SELECT
|
|
90
90
|
t2.user_id
|
|
91
|
-
,COUNT(t2.user_id) AS '
|
|
91
|
+
,COUNT(t2.user_id) AS '2月のログイン回数'
|
|
92
92
|
FROM login_log t2
|
|
93
|
-
WHERE MONTH(t2.timestamp) =
|
|
93
|
+
WHERE MONTH(t2.timestamp) = 2
|
|
94
94
|
GROUP BY
|
|
95
95
|
t2.user_id
|
|
96
96
|
;
|
1
テーブルの説明をCREATE TABLE文とINSERT文に修正。
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,4 +1,27 @@
|
|
|
1
|
+
```mysql
|
|
2
|
+
create table mydb.login_log (user_id integer, timestamp timestamp);
|
|
3
|
+
create table mydb.charge_log (user_id integer, timestamp timestamp, category char(10));
|
|
4
|
+
create table mydb.quest_log (user_id integer, timestamp timestamp, quest_id integer);
|
|
5
|
+
|
|
6
|
+
LOAD DATA LOCAL INFILE 'C:/Windows/Temp/login_log.csv' INTO TABLE mydb.login_log
|
|
7
|
+
FIELDS
|
|
8
|
+
TERMINATED BY ','
|
|
9
|
+
ENCLOSED BY '"'
|
|
10
|
+
LINES TERMINATED BY '\r\n';
|
|
11
|
+
|
|
12
|
+
LOAD DATA LOCAL INFILE 'C:/Windows/Temp/charge_log.csv' INTO TABLE mydb.charge_log
|
|
13
|
+
FIELDS
|
|
14
|
+
TERMINATED BY ','
|
|
15
|
+
ENCLOSED BY '"'
|
|
16
|
+
LINES TERMINATED BY '\r\n';
|
|
17
|
+
|
|
18
|
+
LOAD DATA LOCAL INFILE 'C:/Windows/Temp/quest_log.csv' INTO TABLE mydb.quest_log
|
|
19
|
+
FIELDS
|
|
20
|
+
TERMINATED BY ','
|
|
21
|
+
ENCLOSED BY '"'
|
|
22
|
+
LINES TERMINATED BY '\r\n';
|
|
23
|
+
|
|
1
|
-
### 前提・実現したいこと
|
|
24
|
+
```### 前提・実現したいこと
|
|
2
25
|
sqlで、複数の条件に合致したデータを抽出したい。
|
|
3
26
|
|
|
4
27
|
具体的には、
|