mysql从表的某些行创建列

dsekswqp  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(227)

我有这三张table
表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

我想要以下结果:
每个船码的最后计数时间和设备id分类为ae1、ae2、ae3,其中ae1为100110041007,ae2为10021051008,ae3为100310061009

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

如何创建此动态透视表?

jfgube3f

jfgube3f1#

您可以这样接近数据透视表:

Ship Code       Equipment ID           Hour Type       
 ALBE            4                      AE1
 AMBS            6                      AE1
 AMBR            12                     AE2
 AMBR            12                     AE3

这样你可以更容易地查询它

myzjeezk

myzjeezk2#

使用条件聚合:

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;

相关问题