MySQL - 将多行合并为一行

Question

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

表1：list_vehicles

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

表格2：车辆列表

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 语句来获取所需的数据吗？"

- Jigna Jain

---

请标记您正在使用的数据库管理系统（MySQL、SQL Server、Oracle等）。 - Yogesh Sharma

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

- ScaisEdge