我正在使用MySQL 5.6.39版 .

对于我正在处理的应用程序，我有以下ERD .

我想写一些SQL会给我这个：

基本上，对于给定的帐户参考，在旅程开始和结束之间，我希望选择AccelerationScore，BreakingScore，CorneringScore，SpeedingScore和DrivingScore .

Here's an MCVE .

这是 code 我有：

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表中）作为列 Headers .

mysql