MySQL - левое соединение с отметкой времени MAX

Попробуй это:

    SELECT
        'chairs' AS work_type,        -- do you really need this in the result?
        c.id AS work_id,
        c.title,
        c.dimensions,
        l.floor,
        l.bin,
        l.bay,
        l.other
    FROM 
        chairs AS c
      LEFT JOIN
        ( SELECT work_id,
                 MAX(timestamp) AS latestLocation
          FROM   locations
          WHERE  work_type = 'chairs'
          GROUP BY work_id
        ) AS cl                                  -- CurrentLocations
          ON  cl.work_id = c.id
      LEFT JOIN
        locations AS l 
          ON  l.work_type = 'chairs'
          AND l.work_id = cl.work_id
          AND l.timestamp = cl.latestLocation 
    WHERE 
        c.cur_loc LIKE '%19th Street%'
    ORDER BY 
        FIELD(l.floor, 'Basement', '3rd Floor', '4th Floor', '5th Floor'), 
        l.bin, 
        l.bay,
        l.other ;

Описание:

Ты хочешь:

В основном у меня есть таблицы разных предметов (chairs, tables), и у каждого типа есть запись со своим идентификатором для определенных стульев или столов. Затем у меня есть таблица locations, в которой хранятся все местоположения, которые когда-либо были, включая текущее. Текущее местоположение отмечено максимальным timestamp.

Мой запрос предназначен для получения всех предметов (стульев) и присоединения к текущему местоположению.

Итак, для каждого элемента (то есть chair) вам нужен только последний location. Для каждого work_id в таблице location вам нужно MAX(timestamp). Это можно легко найти с помощью GROUP BY, как вы уже выяснили:

        ( SELECT work_id,
                 MAX(timestamp) AS latestLocation
          FROM   locations
          GROUP BY work_type
                 , work_id
        ) AS cl

Но вы также хотите использовать другие столбцы из этой таблицы, и если вы просто добавите их в список выбора, он покажет неправильные результаты. (Обратите внимание, что в большинстве СУБД, чтобы использовать столбцы, которых нет в списке GROUP BY, вам придется применить функцию агрегации, как вы применили MAX() к timestamp. MySQL допускает такое поведение, которое при правильном использовании может иметь некоторые преимущества. , Но он не реализовал его на 100% отказоустойчивым, и поэтому его применение в некоторых случаях, таких как этот, может дать неправильные результаты. Postgres имеет гораздо лучшую реализацию этой функции, которая не допускает ошибочных результатов.)

Итак, что вам нужно сделать, так это использовать приведенный выше запрос GROUP BY в качестве производной таблицы (назовите ее cl или CurrentLocation, как хотите) и соединить ее с таблицей locations, используя все столбцы из списка GROUP BY и агрегированного списка (timestamp ):

      JOIN
        locations AS l 
          ON  l.work_type = cl.work_type
          AND l.work_id = cl.work_id
          AND l.timestamp = cl.latestLocation 

После этого вы можете присоединить их к таблице chairs как обычно. Поскольку в исходном запросе у вас было chairs LEFT JOIN locations, я тоже оставил их в ответе.

Еще одно небольшое изменение заключалось в том, что, поскольку у вас было CurrentLocations.work_id = 'chairs', вам нужны были только те строки из таблицы locations. Таким образом, довольно избыточно сначала выполнять группировку, а затем отклонять все строки, которые не соответствуют этому условию. Вероятно, более эффективно сначала применить условие, а затем сгруппировать по. Окончательный запрос группировки и условия соединения изменяются соответствующим образом.

Также обратите внимание, что индекс для (work_type, work_id, timestamp) повысит эффективность запроса (подзапрос cl будет обрабатываться с использованием только этого индекса, а JOIN locations также будет использовать индекс для поиска необходимых строк из таблицы местоположения.)