mysql - 正确加入1 :n:1:1 relation in mysql database

Question

我现在正在开发一个系统来管理租赁流程，我想知道如何有效地查询所有可出租的对象，如果对象正在租用，那么当前正在租用它的人名。否则该列中应该有 NULL。

我的表格看起来像:

对象

 | object_id | object_name | 
 ---------------------------
 | 1         | Object A    | 
 | 2         | Object B    | 
 | 3         | Object C    | 
 | 4         | Object D    | 
 | 5         | Object E    | 
 ---------------------------

人

 | person_id | person_name | 
 ---------------------------
 | 1         | John Doe    | 
 | 2         | Jane Doe    | 
 | 3         | Max Muster  | 
 | 4         | Foobar      | 
 ---------------------------

租金

 | rental_id | rental_state| person_person_id | 
 ----------------------------------------------
 | 1         | open        | 1                |
 | 2         | returned    | 1                |
 | 3         | returned    | 2                |
 | 4         | open        | 3                |
 | 5         | returned    | 4                |
 ----------------------------------------------

出租对象

 | rental_rental_id | object_object_id | 
 ---------------------------------------
 | 1                | 1                | 
 | 2                | 2                | 
 | 2                | 3                |
 | 3                | 3                | 
 | 4                | 2                |
 | 4                | 5                |
 | 5                | 2                |
 ---------------------------------------

我想要的结果应该是这样的:

 | object_id | object_name | rented_to     |
 -------------------------------------------
 | 1         | Object A    | John Doe      |
 | 2         | Object B    | Max Muster    |
 | 3         | Object C    | NULL          |
 | 4         | Object D    | NULL          |
 | 5         | Object E    | Max Muster    |
 -------------------------------------------

到目前为止我得到的是:

SELECT `object_id`, `object_name`, `person_name` FROM `object`
LEFT JOIN `rental2object` ON `object_id` = `object_object_id`
LEFT JOIN `rental` ON `rental_id` = `rental_rental_id` AND `rental_state` = 'open'
LEFT JOIN `person` ON `person_id` = `person_person_id`
GROUP BY `object_id`

明显的问题是我不知道如何在分组时以正确的方式聚合。

实现我的目标最有效的方法是什么？感谢您的帮助。

编辑

修正了预期结果，对象B也租给了Max Muster。

Answer 1

关于你的问题

对象 #2 和 #5 都在租金 #4 中。但是，根据您的预期结果，您正在以不同的方式处理这两种情况。对象 E 和对象 B 应该是相同的行为，因为它们在同一个租金中。如果没有，你应该解释女巫是判断产品是否有相关人员的标准。

分组依据

要符合 SQL92，您应该在 select 子句中包含所有非聚合列:

SELECT `object_id`, `object_name`, `person_name` as rented_to
FROM `object`
...
GROUP BY `object_id`, `object_name`, `person_name`

要符合 SQL99 标准，您应该在 select 子句中包含所有非功能依赖的非聚合列，在您的情况下，它们是 object_id 和 object_name 之间的依赖项:object_id -> object_name(字段 rental_state 打破了对 person 的依赖功能)，那么你可以只写:

SELECT `object_id`, `object_name`, `person_name` as rented_to
FROM `object`
...
GROUP BY `object_id`, `person_name` 

MySQL 5.7.5 及更高版本实现了功能依赖检测，那么最后一个选择是有效的，但我建议您，为了可读性，使用第一个。

阅读MySQL Handling of GROUP BY了解更多信息和ONLY_FULL_GROUP_BY 参数详情。

性能

确保你有索引:

• object:Object_id(是主键，那么index是隐式的)
• rental2object:object_object_id(可能是与其他字段的复合索引，但要确保 object_object_id 是索引的第一个字段)
• rental:rental_id & rental_state(包含两个字段的复合索引)
• person:person_id(是主键，那么index是隐式的)