Mysql使用MATCH AGAINST限制LIMIT

Question

我有一个SQL查询。如果我不加LIMIT地运行这个查询，那么匹配结果是正常的。但是，当我加上limit（用于实现无限滚动）时，我不能得到最佳匹配结果，只能得到限制搜索中最佳匹配结果，而不是整个搜索中的第一个：

    SELECT videos.id_video as idVideo, 1 as idCanal, MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela') as relevancia FROM videos
INNER JOIN programas_videos ON videos.id_video = programas_videos.id_video
INNER JOIN programas ON programas_videos.id_prog = programas.id_prog
WHERE MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela' IN BOOLEAN MODE)
ORDER BY relevancia DESC LIMIT 5,5

我该如何纠正这个问题？

谢谢！

- Martin

Answer 1

尝试这个。它将首先返回子选择的所有结果，然后应用限制。

SELECT  *

FROM 

(
SELECT videos.id_video as idVideo, 1 as idCanal, MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela') as relevancia FROM videos
INNER JOIN programas_videos ON videos.id_video = programas_videos.id_video
INNER JOIN programas ON programas_videos.id_prog = programas.id_prog
WHERE MATCH (nombre_prog, programas.descrip_larga) AGAINST ('escuela' IN BOOLEAN MODE)
ORDER BY relevancia DESC 
) AS x

LIMIT 5,5

- Tom

---

太好了！！非常感谢您！！ - Martin