mysql - 需要加入3张表。需要按 DateTime 字段排序并限制为 1

Question

我有这些表:

CUSTOMERS

Customer ID | Customer Name
----------------------------
1000        | Jonny Ltd.
1001        | James Ltd.

TICKETS

Ticket ID | Ticket Customer | Ticket Subject
---------------------------------------------
10         | 1000            | Testing Sub
11         | 1001            | Testing Sub 2 
12         | 1001            | Testing Sub 3 

Notes

Note ID | Note Ticket ID | Note Content   | Note Created
---------------------------------------------------------------
1       | 10             | Testing Note1. | 2019-04-20 13:38:16
2       | 10             | Testing Note2. | 2019-04-20 12:52:36
3       | 11             | Testing Note3. | 2019-04-19 10:21:54

我想显示每张票，按他们最近的票据排序。
当我运行此命令时，它没有给我正确的响应。

SELECT `Ticket ID`,`Customer Name`, `Ticket Subject`, `Note Content`, `Note Created`
FROM TICKETS t, CUSTOMERS c, NOTES n
WHERE t.`Ticket Customer` = c.ID AND n.`Note Ticket ID` = t.`Ticket ID`
ORDER BY n.`Note Created` DESC;

我希望它输出:

Ticket ID  | Company Name | Ticket Subject | Note Content   | Note Created
---------------------------------------------------------------------------
10         | Jonny Ltd.   | Testing Sub    | Testing Note1. | 2019-04-20 13:38:16
11         | James Ltd.   | Testing Sub 2  | Testing Note3. | 2019-04-19 10:21:54
12         | James Ltd.   | Testing Sub 3  | NULL           | NULL

Answer 1

您需要一个子查询来识别每张票的最新注释。此版本将子查询放在 ON 子句中:

SELECT `Ticket ID`, `Customer Name`, `Ticket Subject`, `Note Content`, `Note Created`
FROM TICKETS t JOIN
     CUSTOMERS c
     ON t.`Ticket Customer` = c.ID LEFT JOIN
     NOTES n
     ON n.`Note Ticket ID` = t.`Ticket ID` AND
        n.`Note Created` = (SELECT MAX(n2.`Note Created`)
                            FROM NOTES n2
                            WHERE n2.`Note Ticket ID` = t.`Ticket ID`
                           )
ORDER BY (n.`Note Created` IS NOT NULL) DESC,
         n.`Note Created` DESC;

注意事项:

• 永远不要在FROM 子句中使用逗号。 始终使用正确、明确、标准 JOIN 语法。
• 使用 LEFT JOIN 这样您就可以获得没有注释的票。
• 我建议重命名列，使它们不包含空格。不必转义标识符将使编写查询变得更简单。
• ORDER BY 需要确保 NULL 值排在最后。 DESC 确实将 NULL 值放在最后，但为了以防万一，我为此添加了明确的逻辑。

在 MySQL 8+ 中，您将改用窗口函数:

SELECT `Ticket ID`, `Customer Name`, `Ticket Subject`, `Note Content`, `Note Created`
FROM TICKETS t JOIN
     CUSTOMERS c
     ON t.`Ticket Customer` = c.ID LEFT JOIN
     (SELECT n.*,
             ROW_NUMBER() OVER (PARTITION BY n.`Note Ticket ID` ORDER BY n.`Note Created` DESC) as seqnum
      FROM NOTES n
     ) n
     ON n.`Note Ticket ID` = t.`Ticket ID` AND
        n.seqnum = 1
ORDER BY (n.`Note Created` IS NOT NULL) DESC,
         n.`Note Created` DESC;