在 mysql 中使用 union 和 order by 子句

Using union and order by clause in mysql

提问by Aditya

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data related to the exact place search . The 2nd select query returns data related to distance within 5 kms from the place searched. The 3rd select query returns data related to distance within 5-15 kms from the place searched.

我想在 mysql 查询中使用 order by 和 union。我根据我网站上的搜索距离，根据不同标准从表格中获取不同类型的记录。第一个选择查询返回与确切地点搜索相关的数据。第二个选择查询返回与搜索地点 5 公里内的距离相关的数据。第三个选择查询返回与距离搜索地点 5-15 公里范围内的距离相关的数据。

Then i m using union to merge all results and show on a page with paging. Under appropriate heading as 'Exact search results', 'Results within 5 kms'etc

然后我使用 union 合并所有结果并在页面上显示分页。在“精确搜索结果”、“5 公里范围内的结果”等适当标题下

Now i want to sort results based on id or add_date. But when i add order by clause at the end of my query ( query1 union query 2 union query 3 order by add_date). It sorts all results. But what i want is it should sort under each heading.

现在我想根据 id 或 add_date 对结果进行排序。但是当我在查询末尾添加 order by 子句时（query1 union query 2 union query 3 order by add_date）。它对所有结果进行排序。但我想要的是它应该在每个标题下排序。

回答by RedFilter

You can do this by adding a pseudo-column named rank to each select, that you can sort by first, before sorting by your other criteria, e.g.:

您可以通过向每个选择添加一个名为 rank 的伪列来完成此操作，您可以先按其排序，然后再按其他条件排序，例如：

select *
from (
    select 1 as Rank, id, add_date from Table 
    union all
    select 2 as Rank, id, add_date from Table where distance < 5
    union all
    select 3 as Rank, id, add_date from Table where distance between 5 and 15
) a
order by rank, id, add_date desc

回答by rickythefox

You can use subqueries to do this:

您可以使用子查询来做到这一点：

select * from (select values1 from table1 order by orderby1) as a
union all
select * from (select values2 from table2 order by orderby2) as b

回答by Mitali

(select add_date,col2 from table_name) 
  union 
(select add_date,col2 from table_name) 
  union 
(select add_date,col2 from table_name) 

order by add_date

回答by Gerard ONeill

Don't forget, union all is a way to add records to a record set without sorting or merging (as opposed to union).

不要忘记，union all 是一种无需排序或合并即可将记录添加到记录集的方法（与并集相反）。

So for example:

例如：

select * from (
    select col1, col2
    from table a
    <....>
    order by col3
    limit by 200
) a
union all
select * from (
    select cola, colb
    from table b
    <....>
    order by colb
    limit by 300
) b

It keeps the individual queries clearer and allows you to sort by different parameters in each query. However by using the selected answer's way it might become clearer depending on complexity and how related the data is because you are conceptualizing the sort. It also allows you to return the artificial column to the querying program so it has a context it can sort by or organize.

它使各个查询更加清晰，并允许您按每个查询中的不同参数进行排序。但是，通过使用所选答案的方式，根据复杂性和数据的相关性，它可能会变得更加清晰，因为您正在概念化排序。它还允许您将人工列返回到查询程序，以便它具有可以排序或组织的上下文。

But this way has the advantage of being fast, not introducing extra variables, and making it easy to separate out each query including the sort. The ability to add a limit is simply an extra bonus.

但是这种方式的优点是速度快，不会引入额外的变量，并且可以很容易地分离出每个查询，包括排序。添加限制的能力只是额外的奖励。

And of course feel free to turn the union all into a union and add a sort for the whole query. Or add an artificial id, in which case this way makes it easy to sort by different parameters in each query, but it otherwise is the same as the accepted answer.

当然，您可以随意将联合全部转换为联合，并为整个查询添加排序。或者添加一个人工 id，在这种情况下，这种方式可以轻松地按每个查询中的不同参数进行排序，但其他方面与接受的答案相同。

回答by user151841

A union query can only have one master ORDER BYclause, IIRC. To get this, in each query making up the greater UNIONquery, add a field that will be the one field you sort by for the UNION's ORDER BY.

联合查询只能有一个主ORDER BY子句 IIRC。为此，在构成更大UNION查询的每个查询中，添加一个字段，该字段将作为您对UNION's 进行排序的一个字段ORDER BY。

For instance, you might have something like

例如，你可能有类似的东西

SELECT field1, field2, '1' AS union_sort
UNION SELECT field1, field2, '2' AS union_sort
UNION SELECT field1, field2, '3' AS union_sort
ORDER BY union_sort

That union_sortfield can be anything you may want to sort by. In this example, it just happens to put results from the first table first, second table second, etc.

该union_sort字段可以是您想要排序的任何内容。在此示例中，恰好将第一个表的结果放在第一位，第二个表的结果放在第二位，依此类推。

回答by Robert Saylor

I got this working on a join plus union.

我在 join plus union 上进行了这项工作。

(SELECT 
   table1.column1,
   table1.column2,
   foo1.column4
 FROM table1, table2, foo1, table5
 WHERE table5.somerecord = table1.column1
 ORDER BY table1.column1 ASC, table1.column2 DESC
)

UNION

(SELECT
    ... Another complex query as above
)

ORDER BY column1 DESC, column2 ASC

回答by hdifen

When you use an ORDER BYclause inside of a sub query used in conjunction with a UNIONmysql will optimise away the ORDER BYclause.

当您ORDER BY在与UNIONmysql结合使用的子查询中使用ORDER BY子句时，将优化该子句。

This is because by default a UNIONreturns an unordered list so therefore an ORDER BYwould do nothing.

这是因为默认情况下 aUNION返回一个无序列表，因此 aORDER BY什么也不做。

The optimisation is mentioned in the docsand says:

优化在文档中提到并说：

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);

However, 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. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

要将 ORDER BY 或 LIMIT 应用于单个 SELECT，请将子句放在包含 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);

但是，对单个 SELECT 语句使用 ORDER BY 并不意味着行在最终结果中出现的顺序，因为 UNION 默认生成一组无序的行。因此，在此上下文中使用 ORDER BY 通常与 LIMIT 结合使用，以便它用于确定要为 SELECT 检索的所选行的子集，即使它不一定影响这些行在最终的 UNION 结果。如果 ORDER BY 在 SELECT 中没有 LIMIT，它会被优化掉，因为它无论如何都不会产生任何影响。

The last sentence of this is a bit misleading because it should have an effect. This optimisation causes a problem when you are in a situation where you need to order within the subquery.

这最后一句有点误导，因为它应该有效果。当您需要在子查询中进行排序时，此优化会导致问题。

To force MySQL to not do this optimisation you can add a LIMIT clause like so:

要强制 MySQL 不进行此优化，您可以添加一个 LIMIT 子句，如下所示：

(SELECT 1 AS rank, id, add_date FROM my_table WHERE distance < 5 ORDER BY add_date LIMIT 9999999999)
UNION ALL
(SELECT 2 AS rank, id, add_date FROM my_table WHERE distance BETWEEN 5 AND 15 ORDER BY rank LIMIT 9999999999)
UNION ALL
(SELECT 3 AS rank, id, add_date from my_table WHERE distance BETWEEN 5 and 15 ORDER BY id LIMIT 9999999999)

A high LIMITmeans that you could add an OFFSETon the overall query if you want to do something such as pagination.

高LIMIT意味着OFFSET如果您想执行诸如分页之类的操作，则可以在整个查询中添加一个。

This also gives you the added benefit of being able to ORDER BYdifferent columns for each union.

这也为您提供了能够ORDER BY为每个联合使用不同列的额外好处。

回答by Mike C

I tried adding the order by to each of the queries prior to unioning like

我尝试在合并之前将顺序添加到每个查询中

(select * from table where distance=0 order by add_date) 
union 
(select * from table where distance>0 and distance<=5 order by add_date)

but it didn't seem to work. It didn't actually do the ordering within the rows from each select.

但它似乎没有用。它实际上并没有在每个选择的行内进行排序。

I think you will need to keep the order by on the outside and add the columns in the where clause to the order by, something like

我认为您需要将 order by 保留在外面，并将 where 子句中的列添加到 order by 中，例如

(select * from table where distance=0) 
union 
(select * from table where distance>0 and distance<=5) 
order by distance, add_date

This may be a little tricky, since you want to group by ranges, but I think it should be doable.

这可能有点棘手，因为您想按范围分组，但我认为这应该是可行的。

回答by André Hoffmann

Try:

尝试：

SELECT result.* 
FROM (
 [QUERY 1]
 UNION
 [QUERY 2]
) result
ORDER BY result.id

Where [QUERY 1] and [QUERY 2] are your two queries that you want to merge.

其中 [QUERY 1] 和 [QUERY 2] 是您要合并的两个查询。

回答by canni

This is because You're sorting entire result-set, You should sort, every part of union separately, or You can use ORDER BY (Something ie. subquery distance) THEN (something ie row id) clause

这是因为您正在对整个结果集进行排序，您应该分别对联合的每个部分进行排序，或者您可以使用 ORDER BY (Something ie. subquery distance) THEN (something ie row id) 子句