mysql order by union似乎不起作用 [英] mysql order by with union doesn't seem to work

问题描述

这是我的查询

(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only three doors%" OR `joke` LIKE "%only three doors%") ORDER BY `ups` DESC,`downs` ASC)
UNION
(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only%" OR `joke` LIKE "%only%") ORDER BY `ups` DESC,`downs` ASC)
UNION
(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%three%" OR `joke` LIKE "%three%") ORDER BY `ups` DESC,`downs` ASC)
UNION
(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%doors%" OR `joke` LIKE "%doors%") ORDER BY `ups` DESC,`downs` ASC)
 LIMIT 0, 30

由于某种原因，它似乎并没有按照升序或降序排序……它只是按照结果在数据库中的自然顺序将其扔回给我.

For some reason it doesn't seem to order by ups or downs...it just tosses me back the results in the order they are naturally in the database.

当我只将其缩减为一个查询时，它可以正常工作，但是除此之外，它似乎忽略了它.

When I cut it down to only one query, it works fine, but other than that, it seems to ignore it.

我也不想按整个结果排序，否则我会放LIMIT 0,30 Order By blah

I also don't want to order by the entire results, or I would have put LIMIT 0,30 Order By blah

推荐答案

从MySQL

...对单独的SELECT语句使用ORDER BY意味着 行在最终结果中出现的顺序无关紧要 因为默认情况下UNION会生成无序的行集.

... 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.

基本上，联合中的ORDER唯一有用的时间是同时使用LIMIT.

Basically the only time an ORDER in a union will be useful is if you are using LIMIT as well.

因此，如果您的查询是这样的:

So if you query was like this:

(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only three doors%" OR `joke` LIKE "%only three doors%") ORDER BY `ups` DESC,`downs` ASC LIMIT 10)
UNION ...

然后，您将看到根据该顺序返回的前十条记录，但不一定按顺序显示它们.

Then you would see the first ten records that would be returned based on that order, but they wouldn't necessarily be displayed in order.

更新:

尝试一下-

(SELECT *, 1 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only three doors%" OR `joke` LIKE "%only three doors%") )
UNION
(SELECT *, 2 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only%" OR `joke` LIKE "%only%") )
UNION
(SELECT *, 3 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%three%" OR `joke` LIKE "%three%") )
UNION
(SELECT *, 4 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%doors%" OR `joke` LIKE "%doors%"))
 ORDER BY `ob`, `ups` DESC,`downs` ASC LIMIT 0, 30

这篇关于mysql order by union似乎不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！