質問編集履歴

3

テーブルのデータ構造を追記しました。

2019/06/14 00:52

投稿

humuhimi
humuhimi

スコア49

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

詳細

2019/06/14 00:52

投稿

humuhimi
humuhimi

スコア49

test CHANGED
File without changes
test CHANGED
File without changes

1

詳細記載

2019/06/14 00:44

投稿

humuhimi
humuhimi

スコア49

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
- →Empty set (0.00 sec)
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
- これをnull値が入った状態で出力したいです。よろしくお願いします
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
+ よろしくお願いします。