mysql - LEFT JOIN 子选择失败

Question

我尝试从 LEFT JOIN 的表中选择 change_date 中具有最新时间戳的行。我真的不知道为什么这个查询失败:

SELECT
    i.ID, i.title, i.create_date,
    u1.username creator_name,
    u2.username assignee
FROM item i
    LEFT JOIN user u1 ON u1.login_IDFK = i.creator_IDFK  
    LEFT JOIN user u2 ON u2.login_IDFK = i.assigned_to_IDFK
    LEFT JOIN (
        SELECT MAX(change_date), item_IDFK FROM item_state GROUP BY item_IDFK
    ) AS ist ON ist.item_IDFK = i.ID

我收到以下错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS ist ON ist.item_IDFK = i.ID' at line 2 (Code: 1064)

没有最后一个LEFT JOIN，查询效果很好

Answer 1

(SELECT change_date, item_IDFK FROM item_state GROUP BY item_IDFK)

您正在使用没有聚合的 group by 子句。选择列表中的每个项目必须在 group by 子句中表示，或者是聚合表达式的一部分

即:

(Select Max(Change_Date), item_IDFK from item_state group by item_IDFK)