从mysql中的租赁表中选择以显示用户当前租赁的物品

发布于 2024-09-04 15:12:20 字数 504 浏览 5 评论 0原文

如果您了解 sakila 示例数据库，那么选择用户当前租用的项目的语句是什么。如果没有，这里有一个代码解释：

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;

请注意，没有归还日期，这是因为最后一个承租人的租赁时间是无限期的，直到有人请求该物品并且它也会无限期地转移给新的承租人。一个用户可以租用多件物品，并且每件物品只有一件，因此 item_id 不能同时分配给两个用户。

我想显示每个 user_id 当前租用的项目。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

锦爱 2024-09-11 15:12:20

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

这是一个经典的sql问题。答案是这里解释。

用作

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');

播放数据，要理解该方法，请查看

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             | 
+---------+---------+------------------+---------+---------+------------------+

前 3 列引用 r1 的列，最后 3 列引用 r2 的输出。

正如您所看到的，只要没有大于 r1.last_change_date 的 r2.last_change_date，该值为 NULL。这些是 r1.last_change_date 最大的行。因此，要查找所需的行，请使用
条件

WHERE  r2.last_change_date IS NULL

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