mysql order by with union 似乎不起作用

发布于 2024-12-23 13:39:41 字数 782 浏览 1 评论 0原文

这是我的查询，

(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

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

当我将其减少到只有一个查询时，它工作得很好，但除此之外，它似乎忽略了它。

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

原文

Here is my query

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风吹雨成花 2024-12-30 13:39:41

来自 MySQL 文档：

...对各个 SELECT 语句使用 ORDER BY 意味着
与行在最终结果中出现的顺序无关
因为 UNION 默认情况下会生成一组无序的行。

基本上，联合中的 ORDER 唯一有用的时候是您同时使用 LIMIT 的情况。

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

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

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

更新：

试试这个 -

(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

From MySQL documentation:

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

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.

UPDATE:

Try this -

(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

回复收藏 0 原文

饭团 2024-12-30 13:39:41

我得到了解决方案：

SELECT *
FROM (
    (SELECT 1 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE city=".$city." AND region=".$region." AND cantone=".$cantone.")
     UNION
    (SELECT 2 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE region=".$region." AND cantone=".$cantone.")
    union all
    (SELECT 3 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE cantone=".$cantone.")
) As u
GROUP BY uid 
ORDER BY SortRank,state=2, title ASC
LIMIT 0,10

在上面的查询中我想要结果，例如。首先显示包含城市、地区和广东省的所有记录，然后如果城市不可用，则显示包含地区和广东省的所有记录，然后显示包含城市广东省的所有记录。
因此，删除重复记录时我使用了 GROUP BY 子句，它将根据查询组对所有记录进行排序，然后对状态 = 2 的所有记录进行排序。

I got solution for this:

SELECT *
FROM (
    (SELECT 1 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE city=".$city." AND region=".$region." AND cantone=".$cantone.")
     UNION
    (SELECT 2 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE region=".$region." AND cantone=".$cantone.")
    union all
    (SELECT 3 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE cantone=".$cantone.")
) As u
GROUP BY uid 
ORDER BY SortRank,state=2, title ASC
LIMIT 0,10

In above query i want result eg. first show all records with city, region and cantone then if city not available then show all records with region and cantone and then all records with cantone of city.
So, removing repeating records i used GROUP BY clause, it will sort all records based on query group then all records with state=2.

回复收藏 0 原文

那支青花 2024-12-30 13:39:41

查询的作用是分别对每个子查询进行排序并将所有子查询统一起来。不保证结果会被订购。

您需要做的是将统一查询排序如下：

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

What the query does, is to order each sub-query separately and unifying all of them. There is no guarantee the result would be ordered.

what you need to do is to order the unified query as such:

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

回复收藏 0 原文

节枝 2024-12-30 13:39:41

也许这与问题有点无关，但对于那些想要联合两个查询或按不同列排序的更多查询的人来说：

因为您无法使用一个order by因为两个查询中的列不相同，您可能会考虑添加“假”列以使两个查询中的列数相等，这是我对在 HackerRank 上进行练习，类似于此处提出的问题

WITH sub AS 
(
(SELECT CONCAT(Name , "(" , SUBSTRING(OCCUPATION,1,1) , ")") as first , Name , OCCUPATION , -1 as counts FROM OCCUPATIONS)
UNION ALL
(SELECT CONCAT("There are a total of " , COUNT(OCCUPATION) , " " , LOWER(OCCUPATION) , "s" , ".") as first , "z" as Name , OCCUPATION , COUNT(OCCUPATION) as counts FROM OCCUPATIONS
GROUP BY OCCUPATION)
)

SELECT first FROM sub
ORDER BY Name ASC , counts ASC , OCCUPATION ASC

注意：您应该为假列选择适当的值，以使您的行处于正确的位置（注意< code>-1 作为 FROM OCCUPATIONS 的计数，"z" 作为查询中的 Name）

我最初误解了 HackerRank 上的问题，因为它不需要我合并两个查询但无论如何我最终还是学到了一些新东西，希望这有帮助（：

Maybe this is a bit unrelated to the question, but for those who want union two queries or more ordered by different columns:

Since there is no way you could use one order by statement because the columns in both queries are not the same, you might consider adding "fake" columns to make the number of columns equal in both queries, this is my answer to an excerise on HackerRank similar to the question asked here

WITH sub AS 
(
(SELECT CONCAT(Name , "(" , SUBSTRING(OCCUPATION,1,1) , ")") as first , Name , OCCUPATION , -1 as counts FROM OCCUPATIONS)
UNION ALL
(SELECT CONCAT("There are a total of " , COUNT(OCCUPATION) , " " , LOWER(OCCUPATION) , "s" , ".") as first , "z" as Name , OCCUPATION , COUNT(OCCUPATION) as counts FROM OCCUPATIONS
GROUP BY OCCUPATION)
)

SELECT first FROM sub
ORDER BY Name ASC , counts ASC , OCCUPATION ASC

Note : You should choose the appropriate values for the fake columns to get yours rows in the correct position (notice -1 as counts FROM OCCUPATIONS , "z" as Name in my query)

I initially misunderstood the question on HackerRank as it did not require me to union the two queries but i ended up learning something new anyways , hope that was helpful (:

回复收藏 0 原文