在 mysql 中使用 union 和 order by 子句

2021-11-20 00:00:00 sql union mysql sql-order-by

我想在 mysql 查询中使用 order by 和 union.我根据我网站上的搜索距离，根据不同标准从表格中获取不同类型的记录.第一个选择查询返回与确切地点搜索相关的数据.第二个选择查询返回与距离搜索地点 5 公里内的距离相关的数据.第 3 次选择查询返回与搜索地点 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.

然后我使用 union 合并所有结果并在页面上显示分页.在适当的标题下，例如'精确搜索结果'、'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 union query 2 union query 3 order by 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

相关文章