Mysql 从表的某些行创建列

Question

我有这三张表

表 #1:船舶 ID

Ship ID                         Ship Code
1                               ALBE
2                               AMBS
3                               AMBR

表#2:设备数据

Ship ID               Equipment_ID           
1                       1001
1                       1002
1                       1003
2                       1004
2                       1005
2                       1006
3                       1007
3                       1008
3                       1009

表#3:计数器

Equipment_ID       Hours          Date_Updated
1001                2                2018-01-01
1001                4                2018-05-01
1002                3                2018-01-01
1002                5                2018-05-01
1003                5                2018-01-01
1003                10               2018-05-01
1004                1                2018-01-01
1004                6                2018-05-01
1005                3                2018-03-01
1006                6                2018-03-01
1007                5                2018-01-01
1007                12               2018-05-01
1008                15               2018-01-01
1008                19               2018-05-01
1009                4                2018-01-01
1009                12               2018-06-01 

我想要以下结果:

根据每个船舶代码，最后的柜台时间和 Equipment_id 分类为 AE1、AE2、AE3，其中 AE1 为 1001,1004,1007 AE2 为 1002,1005,1008，AE3 为 1003,1006,1009

Ship Code       AE1         AE2         AE3
ALBE            4           5           10
AMBS            6           6           12
AMBR            12          19          12

如何创建这个动态数据透视表？

Answer 1

使用条件聚合:

select s.ship_code,
       sum(case when e.equipment_id in (1001, 1004, 1007) then hours else 0 end) as ae1,
       sum(case when e.equipment_id in (1001, 1004, 1007) then hours else 0 end) as ae2,
       sum(case when e.equipment_id in (1003,1 006, 1009) then hours else 0 end) as ae3
from ships s left join
     equipment e
     on e.ship_id = s.ship_id left join
     counters c
     on c.equipment_id = e.equipment_id
group by s.ship_code;