mysql - 使用联接时 GROUP BY 未正确排序

Question

我只想查看连接表 fr_movements 中具有最新 sched_date 的一条记录，但我总是得到最旧的 sched_date。由于 ORDER BY 按 sched_date 排序，并且我按 people.ID 分组，所以我按预期从 fr_movements 中只得到了一条记录，只是错误的一条记录。

SELECT `fr_movements`.`ID`, fr_movements_list.movement_type, 
   fr_movements.sched_date, fr_movements.comp_date, people.firstname, people.lastname
FROM fr_movements
LEFT JOIN people ON people.ID = fr_movements.`people_id` 
LEFT JOIN fr_movements_list ON (fr_movements.move_type_id = fr_movements_list.ID)
WHERE fr_movements.org_id = 25 AND fr_movements.move_type_id IN (54,53,52,51,50,55)
GROUP BY people.ID
ORDER BY people.org_name, fr_movements.sched_date ASC

有人知道如何正确执行此操作吗？

Answer 1

正如@bluefeet提到的

you are using GROUP BY incorrectly. GROUP BY is supposed to be used with an aggregate function

您需要将所有列放在GROUP BY之后，但这很复杂。我认为 DISTINCT 可能对您有帮助。像这样:

SELECT *
FROM
(
    SELECT DISTINCT people.org_name, `fr_movements`.`ID`, fr_movements_list.movement_type, 
       fr_movements.sched_date, fr_movements.comp_date, people.firstname, people.lastname
    FROM fr_movements
        LEFT JOIN people ON people.ID = fr_movements.`people_id` 
        LEFT JOIN fr_movements_list ON (fr_movements.move_type_id = fr_movements_list.ID)
    WHERE fr_movements.org_id = 25
        AND fr_movements.move_type_id IN (54,53,52,51,50,55)
) x
ORDER BY org_name, sched_date ASC

如果不起作用，您会将您的数据和架构发布到 sqlfiddle 中吗？这让我们很高兴。