sqlの値がない箇所をnullとして出力するにはどうしたらいいでしょうか?
sql
1mysql> select * from rental where customer_id =9;; 2+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+ 3| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update | 4+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+ 5| 350 | 2005-05-27 05:01:28 | 2756 | 9 | 2005-06-04 05:01:28 | 2 | 2006-02-15 21:30:53 | 6| 877 | 2005-05-30 05:48:59 | 886 | 9 | 2005-06-02 09:30:59 | 1 | 2006-02-15 21:30:53 | 7| 1075 | 2005-05-31 10:13:34 | 2279 | 9 | 2005-06-09 08:11:34 | 1 | 2006-02-15 21:30:53 | 8| 3142 | 2005-06-20 19:59:28 | 3926 | 9 | 2005-06-28 19:51:28 | 2 | 2006-02-15 21:30:53 | 9| 3262 | 2005-06-21 04:08:43 | 3773 | 9 | 2005-06-28 02:55:43 | 1 | 2006-02-15 21:30:53 | 10| 4454 | 2005-07-07 23:37:00 | 1395 | 9 | 2005-07-11 02:30:00 | 1 | 2006-02-15 21:30:53 | 11| 4748 | 2005-07-08 13:59:38 | 4279 | 9 | 2005-07-15 16:51:38 | 1 | 2006-02-15 21:30:53 | 12| 4796 | 2005-07-08 16:35:44 | 2484 | 9 | 2005-07-13 11:08:44 | 2 | 2006-02-15 21:30:53 | 13| 5659 | 2005-07-10 07:45:40 | 4265 | 9 | 2005-07-15 05:20:40 | 1 | 2006-02-15 21:30:53 | 14+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+ 15 rows in set (0.00 sec) 16table:rental
sql
1+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ 2| customer_id | store_id | first_name | last_name | email | address_id | active | create_date | last_update | 3+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ 4| 1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 5| 2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 6| 3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 7| 4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 8| 5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 9| 6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 10| 7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 11| 8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 12| 9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 13| 10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | 1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 | 14+-------------+----------+------------+-----------+-------------------------------------+------------+--------+---------------------+---------------------+ 1510 rows in set (0.01 sec) 16table:customer
sql
1select * 2from rental 3where customer_id =9 4AND 5(rental_date >='2005-06-15 00:00:00' 6and 7rental_date <'2005-06-20 00:00:00'); 8Empty set (0.00 sec)
ustomerテーブルとrentalテーブルを⽤い、2005年6⽉15⽇〜6⽉19
⽇のレンタル本数をユーザごとに集計せよ。
ただし同期間に⼀度も購⼊していないユーザのレンタル本数はnullとして出⼒すること。
出⼒カラム︓customer_id
でやりたいです。
sql
1select 2c.customer_id 3from 4customer c 5LEFT outer JOIN 6rental r 7on 8c.customer_id = r.customer_id 9where 10(r.rental_date >='2005-06-15 00:00:00' 11and 12r.rental_date <'2005-06-20 00:00:00') 13-- and c.customer_id = 9 14group by c.customer_id 15order by c.customer_id 16limit 10 17; 18 19+-------------+ 20| customer_id | 21+-------------+ 22| 1 | 23| 2 | 24| 3 | 25| 4 | 26| 5 | 27| 6 | 28| 7 | 29| 8 | 30| 10 | 31| 11 | 32+-------------+
sql
1+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+ 2| Field | Type | Null | Key | Default | Extra | 3+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+ 4| customer_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | 5| store_id | tinyint(3) unsigned | NO | MUL | NULL | | 6| first_name | varchar(45) | NO | | NULL | | 7| last_name | varchar(45) | NO | MUL | NULL | | 8| email | varchar(50) | YES | | NULL | | 9| address_id | smallint(5) unsigned | NO | MUL | NULL | | 10| active | tinyint(1) | NO | | 1 | | 11| create_date | datetime | NO | | NULL | | 12| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | 13+-------------+----------------------+------+-----+-------------------+-----------------------------------------------+ 149 rows in set (0.04 sec) 15 16mysql> desc rental 17 -> ; 18+--------------+-----------------------+------+-----+-------------------+-----------------------------------------------+ 19| Field | Type | Null | Key | Default | Extra | 20+--------------+-----------------------+------+-----+-------------------+-----------------------------------------------+ 21| rental_id | int(11) | NO | PRI | NULL | auto_increment | 22| rental_date | datetime | NO | MUL | NULL | | 23| inventory_id | mediumint(8) unsigned | NO | MUL | NULL | | 24| customer_id | smallint(5) unsigned | NO | MUL | NULL | | 25| return_date | datetime | YES | | NULL | | 26| staff_id | tinyint(3) unsigned | NO | MUL | NULL | | 27| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | 28+--------------+-----------------------+------+-----+-------------------+-----------------------------------------------+ 297 rows in set (0.01 sec) 30
上記のようにrental_dateの条件を満たさない値が出力されないのでそれがnullで出力されるようにして欲しいです。
よろしくお願いします。
回答2件
あなたの回答
tips
プレビュー