mySQL - 多行合并为一行

Question

我需要使用连接从多个表中获取数据。以下是表格。

表 1:list_vehicles

pk_vehicle_id   vehicle_reg_no  vehicle_type
1                 REG1              Bus
2                  1                Bus
7                  1                Bus

表 2:list_vehicles

pk_route_id     route_code    route_name    route_description
26                CODE1        Route1         First Route
27                CODE2        Route2         Second Route
28                CODE3        Route3         Third Route

表 3:tbl_route_vehicle_mgmt

pk_route_veh_id   fk_route_id   fk_vehicle_id
4                    22             2
5                    23             1
6                    27             1

表 4:tbl_staff_allocation

pk_id   fk_route_id     fk_staff_id    staff_type
 1           27             13          Attendant
 2           27             14          Driver
 3           27             15          Conductor

我需要上表中的以下数据，比如 pk_route_id =27

Route_Name Vehicle_Number Vehicle_Type Driver_Id    Attendant_Id    Conductor
Route 2         REG1            Bus            13           14             15

我试着写了一部分SQL如下。我被卡住了，不确定如何增强它以获得所需的结果。

SELECT a.route_code,a.route_name,a.route_description, tbl_b.fk_vehicle_id,tbl_c.fk_staff_id,tbl_c.staff_type, tbl_c.fk_route_id
FROM `list_routes` AS a
INNER JOIN tbl_route_vehicle_mgmt AS tbl_b 
    ON a.pk_route_id = tbl_b.fk_route_id
INNER JOIN tbl_staff_allocation AS tbl_c 
    ON a.pk_route_id = tbl_c.fk_route_id

     where a.pk_route_id =27 AND (tbl_c.staff_type ="Driver" OR  tbl_c.staff_type ="Conductot" OR tbl_c.staff_type ="Attendant" )

任何人都可以帮助我使用 SQL 来获取所需的数据。

Answer 1

你应该在 tbl_staff_allocation 上使用多个自连接

SELECT 
      a.route_code
      ,a.route_name
      ,a.route_description
      ,tbl_b.fk_vehicle_id
      ,tbl_c1.fk_route_id
      ,tbl_c1.fk_staff_id as Attendant_id
      ,tbl_c2.fk_staff_id as Driver_id
      ,tbl_c3.fk_staff_id as Conductor_id

FROM `list_routes` AS a
INNER JOIN tbl_route_vehicle_mgmt AS tbl_b  ON a.pk_route_id = tbl_b.fk_route_id
INNER JOIN tbl_staff_allocation AS tbl_c1   ON a.pk_route_id = tbl_c1.fk_route_id and tbl_c1.staff_type ='Attendant'
INNER JOIN tbl_staff_allocation AS tbl_c2   ON a.pk_route_id = tbl_c2.fk_route_id and tbl_c.staff_type ='Driver'
INNER JOIN tbl_staff_allocation AS tbl_c3   ON a.pk_route_id = tbl_c3.fk_route_id and tbl_c.staff_type ='Conductor'
INNER JOIN list_vehicles AS d on d.pk_vehicle_id = tbl_b.fk_vehicle_id