Select from rental table in mysql to display items currently rented by user
If you know about sakila sample database, then what is the statement to select items currently rented by a user. If not here is开发者_开发问答 a code explanation:
CREATE TABLE IF NOT EXISTS `rentals` (
`item_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`last_change_date` date NOT NULL,
PRIMARY KEY (`item_id`,`user_id`,`last_change_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Notice there is no return date, this is because the last renter has indefinite rental time, until somebody requests this item and it's transferred to the new renter for indefinite time as well. A user can rent more than one item and there is only one piece of each item so an item_id cannot go to two users at the same time.
I would like to display currently rented items per user_id.
SELECT r1.*
FROM rentals r1
LEFT JOIN rentals AS r2
ON r1.item_id = r2.item_id
AND r1.last_change_date < r2.last_change_date
WHERE r2.last_change_date IS NULL
This is a classic sql question. The answer is explained here.
Using
INSERT INTO rentals (item_id, user_id, last_change_date) VALUES (1, 1, '2009-01-01'), (1, 3, '2009-11-10'), (3, 3, '2009-02-13'), (3, 5, '2010-05-11'), (5, 5, '2010-06-04'), (7, 7, '2010-06-04'), (9, 9, '2010-06-04');
as play-data, to understand the method, look at the output of
SELECT r1.*,r2.*
FROM rentals r1
LEFT JOIN rentals AS r2
ON r1.item_id = r2.item_id
AND r1.last_change_date < r2.last_change_date
+---------+---------+------------------+---------+---------+------------------+
| item_id | user_id | last_change_date | item_id | user_id | last_change_date |
+---------+---------+------------------+---------+---------+------------------+
| 1 | 1 | 2009-01-01 | 1 | 3 | 2009-11-10 |
| 1 | 3 | 2009-11-10 | NULL | NULL | NULL |
| 3 | 3 | 2009-02-13 | 3 | 5 | 2010-05-11 |
| 3 | 5 | 2010-05-11 | NULL | NULL | NULL |
| 5 | 5 | 2010-06-04 | NULL | NULL | NULL |
| 7 | 7 | 2010-06-04 | NULL | NULL | NULL |
| 9 | 9 | 2010-06-04 | NULL | NULL | NULL |
+---------+---------+------------------+---------+---------+------------------+
The first 3 columns refer to r1
's columns, the last 3 refer to r2
's.
As you can see, whenever there is no r2.last_change_date
which is greater than r1.last_change_date
, the value is NULL. Those are the rows where r1.last_change_date
is greatest. So to find the rows you want, you use
the condition
WHERE r2.last_change_date IS NULL
精彩评论