在mysql中使用union和order by子句 [英] Using union and order by clause in mysql

问题描述

我想在MySQL查询中使用带有联合的order. 我正在根据基于距离的表从不同的条件中获取不同类型的记录，该表基于距离在我的网站上进行搜索. 第一个选择查询返回与精确位置搜索有关的数据. 第二个选择查询返回与距搜索位置5公里以内的距离有关的数据. 第三个选择查询返回与距搜索位置5至15公里之内的距离有关的数据.

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.

然后，我使用并集合并所有结果，并在带有分页的页面上显示.在适当的标题下，例如确切搜索结果" ，"5公里范围内的结果" 等

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

现在，我想根据ID或add_date对结果进行排序.但是，当我在查询末尾添加order by子句时(query1联合查询2联合查询3由add_date排序).它对所有结果进行排序.但是我想要的是应该在每个标题下进行排序.

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.

推荐答案

您可以通过向每个选择添加一个名为rank的伪列来进行此操作，在按照其他条件进行排序之前，可以首先对其进行排序，例如:

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

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

这篇关于在mysql中使用union和order by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！