我有一个作业的DB表。该表的列"featured"=NULL或1。
用户可以使用关键字搜索表格。我按照相关性、日期或薪水对它们进行排序,如下所示(相关性):
SELECT SQL_CALC_FOUND_ROWS *, MATCH(`title`)
AGAINST ("*'.$keywords.'*")
AS Relevance
FROM jobs2
WHERE MATCH(`title`)
AGAINST ("*'.$keywords.'*")
ORDER BY Relevance
LIMIT 0,50
我希望任何"特色"的工作在每个页面上都是第一位的(但每个特色工作仍然按顺序排列)。
例如,如果自然搜索要返回(按顺序):
Job1 not featured
Job2 not featured
Job3 featured
Job4 not featured
Job5 featured
我想要顶部的特色工作,顺序如下:
Job3 featured
Job5 featured
Job1 not featured
Job2 not featured
Job4 not featured
我怎样才能做到这一点?理想情况下,如果可能的话,在SQL查询本身中
好吧,如果你只想在所有列表中首先列出所有的特色列表,你只需要按两列排序:
SELECT SQL_CALC_FOUND_ROWS *, MATCH(`title`)
AGAINST ("*'.$keywords.'*")
AS Relevance
FROM jobs2
WHERE MATCH(`title`)
AGAINST ("*'.$keywords.'*")
ORDER BY featured desc, Relevance desc -- <-- This here
LIMIT 0,50
相反,如果你想在每个页面上首先列出特色列表,但不影响整体排序,你可以在应用程序逻辑中这样做,也可以使用与子查询相同的查询并重新排序其结果:
SELECT * from
(SELECT SQL_CALC_FOUND_ROWS *, MATCH(`title`)
AGAINST ("*'.$keywords.'*")
AS Relevance
FROM jobs2
WHERE MATCH(`title`)
AGAINST ("*'.$keywords.'*")
ORDER BY Relevance desc
LIMIT 0,50) AS X
ORDER BY X.featured desc, X.Relevance desc;