Mysql以匹配 bool 模式选择下一个和上一个ID

Question

我试图在匹配 bool 模式下查找下一个和上一个 ID。这是我试过的:

SELECT id
FROM songs 
WHERE MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE)
ORDER BY MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) DESC

结果:

32212
32138
32221
32235

SQL Fiddle(结果):http://sqlfiddle.com/#!9/d369d/7

然后找到id 32138之后的下一个ID:

SELECT id
FROM songs 
WHERE 
    id < '32138'
  AND 
    MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE)
ORDER BY MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) DESC LIMIT 1

根据上面的结果，下一个id应该是32221但是结果是空的。

SQL Fiddle(下一个 ID):http://sqlfiddle.com/#!9/d369d/9

有什么帮助吗？

Answer 1

您正在 where 子句中比较 *id*s，并且您正在寻找小于最低 id 的 id 值。您想要与 match 结果进行比较:

SELECT s.id
FROM songs s cross join
     (select MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) as matchresult
      from songs s
      where id = 32138
     ) s32138
WHERE MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) < s32138.matchresult and
      MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE)
ORDER BY MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) DESC
LIMIT 1;

编辑:

当然，多首歌曲可以有相同的匹配值。考虑到这一点:

SELECT s.id
FROM songs s cross join
     (select id, MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) as matchresult
      from songs s
      where id = 32138
     ) scomp
WHERE (MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) < scomp.matchresult or
       MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) = scomp.matchresult and s.id < scomp.id

      ) and
      MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE)
ORDER BY MATCH (artist) AGAINST ('alpha delta beta' IN BOOLEAN MODE) DESC
LIMIT 1;