以一个表中的值作为返回结果中的列标题,跨7个表查询

我使用的是MySQL版本5.6.39。

下面是我正在处理的一个应用程序的ERD。

我正在尝试编写一些SQL,以提供以下信息:

Here's an MCVE

这是 代码

SELECT
    v.VehicleReference,
    j.EndDateTime,
    COUNT(sp.SpeedingEventId) AS SpeedingCount
FROM
    AccountAndDevices.Journey j
INNER JOIN
    AccountAndDevices.Device d ON j.DeviceId = d.DeviceId
INNER JOIN
    AccountAndDevices.Vehicle v ON d.VehicleId = v.VehicleId
INNER JOIN
    AccountAndDevices.Account a ON a.AccountId = v.AccountId
LEFT OUTER JOIN
        AccountAndDevices.SpeedingEvent sp On j.DeviceId = sp.DeviceId
    AND
        sp.DateTime BETWEEN j.StartDateTime AND j.EndDateTime
WHERE
        a.AccountReference = 'TestUser03@example.com'
    AND
        JourneyId =
            (SELECT
                JourneyId
            FROM
                AccountAndDevices.Journey j
            INNER JOIN
                AccountAndDevices.Device d ON j.DeviceId = d.DeviceId
            INNER JOIN
                AccountAndDevices.Vehicle v ON d.VehicleId = v.VehicleId
            INNER JOIN
                AccountAndDevices.Account a ON a.AccountId = v.AccountId
            WHERE
                    a.AccountReference = 'TestUser03@example.com'
                AND
                    j.EndDateTime <= NOW()
            ORDER BY
                j.EndDateTime
            DESC
            LIMIT
                1)
GROUP BY
    v.VehicleReference,
    j.EndDateTime;

这让我很接近,但是它遗漏了所有的分数,我正在努力从JourneyScores表中获取分数,并将它们的类型(在ScoreType表中)作为列标题。