Mysql在匹配布尔模式下选择next和previous ID

I'm trying to find next and previous ID in Match boolean mode. This is what i tried:

我正在尝试在匹配布尔模式下找到next和previous 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

Result:

结果:

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

SQL小提琴(结果):http://sqlfiddle.com/ ! 9 / d369d / 7

Then find the next ID after id 32138:

然后在id32138之后找到下一个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

According to above result, the next id should be 32221 but the result is empty.

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

SQL Fiddle(Next ID): http://sqlfiddle.com/#!9/d369d/9

SQL小提琴(下一个ID):http://sqlfiddle.com/ ! 9 / d369d / 9

Any help please?

任何帮助吗?

1 个解决方案

#1

You are comparing *id*s in the where clause, and you are looking for an id value less than the lowest id. You want to compare to the match result:

您正在比较where子句中的*id*s，并且正在寻找一个小于最小id的id值。

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;

EDIT:

编辑:

Of course, multiple songs can have the same match value. To take this into account:

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

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;

#1