mysql - UNION 中的 GROUP BY 和 ORDER BY

Question

我有这个查询:

SELECT category, description, price, date_added, datetime_created
FROM vc_expense
WHERE trip_id=? AND description LIKE ?
GROUP BY description, price    

UNION SELECT category, description, NULL, NULL, NULL
FROM vc_expense_default
WHERE description LIKE ?    

ORDER BY CASE
  WHEN description LIKE ? AND price THEN 1
  WHEN price THEN 2
  ELSE 3
END, date_added DESC, datetime_created DESC
LIMIT 5

我的问题是，当我对第 4 行的描述+价格进行分组时，它没有考虑到我想要最新的结果:

date_added DESC, datetime_created DESC

有没有办法在第 4 行之后使用 ORDER BY，以便我只能获取最新的项目，由于 UNION，它似乎不起作用

谢谢!

编辑: UNION 是无关紧要的，我只是将它放在括号内，并仅从每个组中获取最新的项目，如下所示:

SELECT e1.category, e1.description, e1.price, e1.date_added, e1.datetime_created
FROM vc_expense e1
LEFT JOIN vc_expense e2 ON (
    e1.description=e2.description AND
    e1.price=e2.price AND
    e1.date_added < e2.date_added
)
WHERE e2.id IS NULL AND e1.trip_id = ? AND e1.description LIKE ?

Answer 1

来自文档: https://dev.mysql.com/doc/refman/5.7/en/union.html

Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows

这就是您需要的:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);