问题描述
我想在 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
这篇关于在 mysql 中使用 union 和 order by 子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!