在 MySQL 中对联合查询进行排序 [英] Sorting union queries in MySQL

问题描述

我正在为职位列表网站制作搜索功能.为此，我需要首先显示具有匹配标题的列表，然后显示具有匹配描述的职位列表.这是查询，我现在正在使用:

I am making a search feature for a Job listing website. For that I need to first show the listings with matching titles and then the job listings with matching description. Here is the query, I am using right now:

示例:

(SELECT * FROM `jobs` WHERE title LIKE '%java%developer%') 
UNION DISTINCT 
(SELECT * FROM `jobs` WHERE description LIKE '%java%developer%')

但是，我还需要按时间戳对结果进行排序，以便先显示最新的结果.就像它应该给出按时间戳排序的匹配标题的结果，然后给出按时间戳排序的匹配描述的列表.

However, I also need to sort the results by the timestamp so as to show the latest results first. Like it should give the results with matching titles sorted by timestamp and then the listings with matching description sorted by timestamp.

推荐答案

我可能会编写类似于以下内容的查询:

i would probably write the query similar to:

  select *, ((title like '%…%')*2 + (description like '%…%')) as rank
    from jobs
   where title like '%…%'
      or description like '%…%'
order by rank desc, time desc

这样，标题和描述匹配的行将首先出现，然后是标题匹配，然后是描述匹配.我还没有测试过，但通常 mysql 可以很好地将 bool 转换为 int (true: 1, false: 0)

this way, rows where both title and description match will appear first, then title-matches, then description-matches. i haven't tested it, but usually mysql does a good job converting bool to int (true: 1, false: 0)

这篇关于在 MySQL 中对联合查询进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！