MySQL Order By in Right Join 子句

Question

我正在尝试像这样在 MySQL 中进行正确的连接:

SELECT customers.id,customers.firstname,customers.lastname,customers.email,orders.time,orders.notes,pendings.date_updated,pendings.issue,appointments.closed,appointments.job_description,backup_plans.expiration FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id
ORDER BY orders.time DESC LIMIT 1
RIGHT JOIN pendings
ON customers.id = pendings.customer_id
ORDER BY pendings.date_updated DESC LIMIT 1
RIGHT JOIN appointments
ON customers.id = appointments.customer_id
ORDER BY appointments.closed DESC LIMIT 1
RIGHT JOIN backup_plans
ON customers.id = backup_plans.customer_id
ORDER BY backup_plans.expiration DESC LIMIT 1

我的意图是:选择客户的姓名和电子邮件，以及最近的订单、待处理、约会和备用计划探索。当我执行这个时，我得到一个语法错误:

#1064 - 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 'RIGHT JOIN pendings
ON customers.id = pendings.customer_id
ORDER BY pendings.d' at line 5

我不熟悉联接，希望得到任何帮助。

---

编辑 1:

看来我需要按照 DanK 的建议进行子查询，如下所示:

SELECT customers.id,customers.firstname,customers.lastname,customers.email,orderstmp.time,orderstmp.notes FROM customers
RIGHT JOIN (
    SELECT orders.time,orders.notes,orders.customer_id FROM orders ORDER BY orders.time DESC LIMIT 1
) as orderstmp ON orderstmp.customer_id = customers.id

但是当我这样做时，我只得到一行结果，而我想要所有客户信息。

---

编辑 2:

根据 Tom H 的建议，我构建了这个查询:

SELECT
    customers.id,
    SQ_O.time,
    SQ_O.notes
FROM customers
LEFT JOIN (
    SELECT
        customers.id,
        orders.time,
        orders.notes
    FROM customers
    LEFT JOIN orders ON orders.customer_id = customers.id
    ORDER BY orders.time DESC LIMIT 1
) AS SQ_O ON SQ_O.id = customers.id

其中包含所有空白时间和备注字段

和

SELECT
    customers.id,
    O1.time,
    O1.notes
FROM customers
LEFT JOIN orders AS O1 ON O1.customer_id = O1.id
LEFT JOIN orders AS O2 ON O2.customer_id = customers.id AND O2.time > O1.time WHERE O2.customer_id IS NULL

达到最大执行时间。我猜这是因为与其他方言相比，我对 MySQL 的可能性不熟悉。

我也试过这样的相关子查询:

SELECT
    customers.firstname,
    customers.lastname,
    customers.email,
    (
        SELECT CONCAT(orders.time,': ',orders.notes)
        FROM orders
        WHERE orders.customer_id = customers.id
        ORDER BY orders.time DESC LIMIT 1
    ) as last_order
FROM customers

但是“last_order”列为空白。

---

最终的，令人失望的编辑

在尝试了很多帮助我显着学习 SQL 的非常出色的建议之后，我决定编写一个 PHP 脚本来获得我想要的东西。该项目的最后期限有点紧，所以无论做什么，都行。谢谢大家!

Answer 1

每个查询只能有一个 ORDER BY 语句。您当然可以使用子查询并将结果集引用为虚拟表，但最终在单个 SELECT 中您只能有一个 ORDER BY。

例如:

SELECT something
FROM table
ORDER BY something -- One order By

以子查询作为虚拟表:

SELECT something
FROM (SELECT anotherthing, something
      FROM table
      ORDER BY anotherthing) -- this is an order by in a separate select statement..
ORDER BY something -- still only one Order by

------编辑--------

要获得连接语法方面的帮助，请尝试这样的操作:

SELECT --fields,    
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id
RIGHT JOIN pendings ON customers.id = pendings.customer_id
RIGHT JOIN appointments ON customers.id = appointments.customer_id
RIGHT JOIN backup_plans ON customers.id = backup_plans.customer_id
ORDER BY orders.time DESC, pendings.date_updated DESC, appointments.closed DESC, backup_plans.expiration DESC
LIMIT 1