mysql - 当使用 UNION 时，ORDER BY 仅影响其中 1 个查询

Question

我有这个查询:

SELECT customer.CustomerID, customer.Firstname, customer.LastName, orders.DateOrdered AS LastOrdered, DATEDIFF( CURDATE( ) , MAX( orders.DateOrdered ) ) AS DaysSinseOrdered
FROM customer
LEFT JOIN orders ON customer.CustomerID = orders.CustomerID
where orders.OrdersID IS NULL 
GROUP BY customer.CustomerID, customer.FirstName, customer.LastName
UNION
SELECT customer.CustomerID, customer.Firstname, customer.LastName, orders.DateOrdered AS LastOrdered, DATEDIFF( CURDATE( ) , MAX( orders.DateOrdered ) ) AS DaysSinseOrdered
FROM customer
JOIN orders ON customer.CustomerID = orders.CustomerID
GROUP BY customer.CustomerID, customer.FirstName, customer.LastName
ORDER BY DaysSinseOrdered DESC 
LIMIT 0 , 30;

它显示了我需要的信息，但是我可以在没有工会的情况下做到这一点。我希望它首先显示 NULL 值，然后显示应用了 ORDER BY 的第二个查询中的值。现在它的值 NULL 为 0，并通过 ORDER BY 对所有结果进行排序；但我希望它位于较大数字之前的顶部，就像在这种情况下，距上次订购以来的天数，如果您从未订购过，那么它应该位于我的书中的顶部。

非常感谢您的帮助 - 汤姆

Answer 1

实际上，您并不需要使用 UNION 来首先对空值进行排序。您只需首先将查询更改为 ORDER BY 即可:

SELECT customer.CustomerID, customer.Firstname, customer.LastName, orders.DateOrdered AS LastOrdered, DATEDIFF( CURDATE( ) , MAX( orders.DateOrdered ) ) AS DaysSinseOrdered
FROM customer
JOIN orders ON customer.CustomerID = orders.CustomerID
GROUP BY customer.CustomerID, customer.FirstName, customer.LastName
ORDER BY orders.OrdersID IS NULL DESC, DaysSinseOrdered DESC
LIMIT 0, 30

因此该查询将首先对空值进行排序，然后对非空值进行排序。然后，它将保留 DaysSinseOrdered DESC 作为二级订单 -