mysql order by with union 似乎不起作用
这是我的查询
(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
相关文章