在 mysql 中使用 union 和 order by 子句

Using union and order by clause in mysql(在 mysql 中使用 union 和 order by 子句)
本文介绍了在 mysql 中使用 union 和 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

这篇关于在 mysql 中使用 union 和 order by 子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)