質問編集履歴
3
テーブルのデータ構造を追記しました。
test
CHANGED
File without changes
|
test
CHANGED
@@ -176,6 +176,70 @@
|
|
176
176
|
|
177
177
|
|
178
178
|
|
179
|
+
```sql
|
180
|
+
|
181
|
+
+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+
|
182
|
+
|
183
|
+
| Field | Type | Null | Key | Default | Extra |
|
184
|
+
|
185
|
+
+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+
|
186
|
+
|
187
|
+
| customer_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
|
188
|
+
|
189
|
+
| store_id | tinyint(3) unsigned | NO | MUL | NULL | |
|
190
|
+
|
191
|
+
| first_name | varchar(45) | NO | | NULL | |
|
192
|
+
|
193
|
+
| last_name | varchar(45) | NO | MUL | NULL | |
|
194
|
+
|
195
|
+
| email | varchar(50) | YES | | NULL | |
|
196
|
+
|
197
|
+
| address_id | smallint(5) unsigned | NO | MUL | NULL | |
|
198
|
+
|
199
|
+
| active | tinyint(1) | NO | | 1 | |
|
200
|
+
|
201
|
+
| create_date | datetime | NO | | NULL | |
|
202
|
+
|
203
|
+
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
|
204
|
+
|
205
|
+
+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+
|
206
|
+
|
207
|
+
9 rows in set (0.04 sec)
|
208
|
+
|
209
|
+
|
210
|
+
|
211
|
+
mysql> desc rental
|
212
|
+
|
213
|
+
-> ;
|
214
|
+
|
215
|
+
+--------------+-----------------------+------+-----+-------------------+-----------------------------------------------+
|
216
|
+
|
217
|
+
| Field | Type | Null | Key | Default | Extra |
|
218
|
+
|
219
|
+
+--------------+-----------------------+------+-----+-------------------+-----------------------------------------------+
|
220
|
+
|
221
|
+
| rental_id | int(11) | NO | PRI | NULL | auto_increment |
|
222
|
+
|
223
|
+
| rental_date | datetime | NO | MUL | NULL | |
|
224
|
+
|
225
|
+
| inventory_id | mediumint(8) unsigned | NO | MUL | NULL | |
|
226
|
+
|
227
|
+
| customer_id | smallint(5) unsigned | NO | MUL | NULL | |
|
228
|
+
|
229
|
+
| return_date | datetime | YES | | NULL | |
|
230
|
+
|
231
|
+
| staff_id | tinyint(3) unsigned | NO | MUL | NULL | |
|
232
|
+
|
233
|
+
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
|
234
|
+
|
235
|
+
+--------------+-----------------------+------+-----+-------------------+-----------------------------------------------+
|
236
|
+
|
237
|
+
7 rows in set (0.01 sec)
|
238
|
+
|
239
|
+
|
240
|
+
|
241
|
+
```
|
242
|
+
|
179
243
|
上記のようにrental_dateの条件を満たさない値が出力されないのでそれがnullで出力されるようにして欲しいです。
|
180
244
|
|
181
245
|
よろしくお願いします。
|
2
詳細
test
CHANGED
File without changes
|
test
CHANGED
File without changes
|
1
詳細記載
test
CHANGED
File without changes
|
test
CHANGED
@@ -34,9 +34,47 @@
|
|
34
34
|
|
35
35
|
rows in set (0.00 sec)
|
36
36
|
|
37
|
+
table:rental
|
38
|
+
|
37
39
|
```
|
38
40
|
|
41
|
+
```sql
|
39
42
|
|
43
|
+
+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+
|
44
|
+
|
45
|
+
| customer_id | store_id | first_name | last_name | email | address_id | active | create_date | last_update |
|
46
|
+
|
47
|
+
+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+
|
48
|
+
|
49
|
+
| 1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
50
|
+
|
51
|
+
| 2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
52
|
+
|
53
|
+
| 3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
54
|
+
|
55
|
+
| 4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
56
|
+
|
57
|
+
| 5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
58
|
+
|
59
|
+
| 6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
60
|
+
|
61
|
+
| 7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
62
|
+
|
63
|
+
| 8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
64
|
+
|
65
|
+
| 9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
66
|
+
|
67
|
+
| 10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
|
68
|
+
|
69
|
+
+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+
|
70
|
+
|
71
|
+
10 rows in set (0.01 sec)
|
72
|
+
|
73
|
+
table:customer
|
74
|
+
|
75
|
+
```
|
76
|
+
|
77
|
+
```sql
|
40
78
|
|
41
79
|
select *
|
42
80
|
|
@@ -52,10 +90,92 @@
|
|
52
90
|
|
53
91
|
rental_date <'2005-06-20 00:00:00');
|
54
92
|
|
93
|
+
Empty set (0.00 sec)
|
55
94
|
|
95
|
+
```
|
56
96
|
|
97
|
+
ustomerテーブルとrentalテーブルを⽤い、2005年6⽉15⽇〜6⽉19
|
98
|
+
|
57
|
-
|
99
|
+
⽇のレンタル本数をユーザごとに集計せよ。
|
100
|
+
|
101
|
+
ただし同期間に⼀度も購⼊していないユーザのレンタル本数はnullとして出⼒すること。
|
102
|
+
|
103
|
+
出⼒カラム︓customer_id
|
104
|
+
|
105
|
+
でやりたいです。
|
58
106
|
|
59
107
|
|
60
108
|
|
109
|
+
```sql
|
110
|
+
|
111
|
+
select
|
112
|
+
|
113
|
+
c.customer_id
|
114
|
+
|
115
|
+
from
|
116
|
+
|
117
|
+
customer c
|
118
|
+
|
119
|
+
LEFT outer JOIN
|
120
|
+
|
121
|
+
rental r
|
122
|
+
|
123
|
+
on
|
124
|
+
|
61
|
-
|
125
|
+
c.customer_id = r.customer_id
|
126
|
+
|
127
|
+
where
|
128
|
+
|
129
|
+
(r.rental_date >='2005-06-15 00:00:00'
|
130
|
+
|
131
|
+
and
|
132
|
+
|
133
|
+
r.rental_date <'2005-06-20 00:00:00')
|
134
|
+
|
135
|
+
-- and c.customer_id = 9
|
136
|
+
|
137
|
+
group by c.customer_id
|
138
|
+
|
139
|
+
order by c.customer_id
|
140
|
+
|
141
|
+
limit 10
|
142
|
+
|
143
|
+
;
|
144
|
+
|
145
|
+
|
146
|
+
|
147
|
+
+-------------+
|
148
|
+
|
149
|
+
| customer_id |
|
150
|
+
|
151
|
+
+-------------+
|
152
|
+
|
153
|
+
| 1 |
|
154
|
+
|
155
|
+
| 2 |
|
156
|
+
|
157
|
+
| 3 |
|
158
|
+
|
159
|
+
| 4 |
|
160
|
+
|
161
|
+
| 5 |
|
162
|
+
|
163
|
+
| 6 |
|
164
|
+
|
165
|
+
| 7 |
|
166
|
+
|
167
|
+
| 8 |
|
168
|
+
|
169
|
+
| 10 |
|
170
|
+
|
171
|
+
| 11 |
|
172
|
+
|
173
|
+
+-------------+
|
174
|
+
|
175
|
+
```
|
176
|
+
|
177
|
+
|
178
|
+
|
179
|
+
上記のようにrental_dateの条件を満たさない値が出力されないのでそれがnullで出力されるようにして欲しいです。
|
180
|
+
|
181
|
+
よろしくお願いします。
|