使用绑定表的多对多关系的SQL select

Question

我正在努力在图片上写一个select for图。

​

我想要做的是编写一个select，它将显示汽车维修的详细信息。正如您在表Repairs中看到的，只有2个属性，但我不确定是否有必要添加更多属性，特别是来自employees_list和parts_list的属性，因为我想按车辆的plate_number显示每辆车的维修数据。我所说的repair data指的是repair id、vehicle plate_number、所有从事维修工作的员工以及维修时使用的所有部件。如果我的图表是错误的，请帮助我修复它，我不知道如何为它编写select，因为多对多关系和绑定表的使用。

Answer 1

这并不像看起来那么难。

首先，很明显，我们必须选择汽车：

select vehicles.* from vehicles

然后，让我们加入修复：

select 
    vehicles.* 
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle.id

我们不需要结果集中来自修复的数据，所以我们只是连接它，而不是在“select”部分中提及。

然后我们必须连接维修所需的部件，以及有关部件本身的信息：

select 
    vehicles.* 
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle.id
    inner join parts_list on parts_list.repair_id = repairs.id
    inner join parts on parts_list.part_id = parts.id

对于该查询，我们得到了相当于维修所需零件量的行数。但是，如果我们将所有这些数据聚合到json列中，那么在代码中处理这些数据会更容易。因此，在结果集中，我们将看到类似于: vehicle_id，vehicle_part，parts_needed_as_json

让我们来汇总一下：

select 
    vehicles.*, json_agg(parts.*) as parts_needed 
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle_id
    inner join parts_list on parts_list.repair_id = repairs.id
    inner join parts on parts_list.part_id = parts.id
group by vehicles.id, repairs.id

现在，您可以为员工添加相同的逻辑：

select 
    vehicles.*, 
    json_agg(parts.*) as parts_needed,
    json_agg(employes.*) as employees_needed
from vehicles
    inner join repairs on vehicles.id = repairs.vehicle.id
    inner join parts_list on parts_list.repair_id = repairs.id
    inner join parts on parts_list.part_id = parts.id
    inner join employees_list on employes_list.repair_id = repairs.id
    inner join employees on employees_list.employee_id = employees.id
group by vehicles.id, repairs.id

顺便说一句，我建议您将您的表重命名为小写和单数。比如：'repair'，'employee‘和'vehicle'；

另外，将绑定表命名为：'repair_part‘和'repair_employee’。有些人甚至建议按字母表排列相关表格，例如：“employee_repair”和“part_repair”，但我认为这不是必需的；

也许这是一个品味问题，但在大多数情况下，这会导致更具可读性的查询。

也就是说，上面的查询将如下所示：

select 
    vehicle.*, 
    json_agg(part.*) as parts_needed,
    json_agg(employee.*) as employees_needed
from vehicle
    inner join repair on vehicle.id = repair.vehicle_id
    inner join parts_repair on parts_repair.repair_id = repair.id
    inner join part on parts_repair.part_id = part.id
    inner join employees_repair on employees_repair.repair_id = repair.id
    inner join employee on employees_repair.employee_id = employee.id
group by vehicle.id, repair.id

注意'on‘条件现在看起来是多么优雅: parts_repair.part_id = part.id，parts_repair.part_id = part.id

很抱歉我的英语不好