mysql根据相关性案例问题对结果进行排序

Question

我正在尝试根据电影的标题相关性对其进行排序。

以下是原始的Mysql查询结果。

21126   A Frozen Flower 
4557    Cashback 
4504    Frozen 
36645   Frozen
509     Frozen Assets 
20649   Frozen Flesh 
24752   Frozen Kiss 
25675   Frozen Land 
4783    Frozen River 
11798   Frozen River 
11856   Frozen Scream
21755   Frozen Silence 
26333   Frozen Thrills 
8532    Frozen Water 
16196   God Don't Make the Laws 
6158    The Frozen Ground 
28160   The Thaw 
7320    Winter of Frozen Dreams

当前代码我正在使用

SELECT * FROM `movies` 
    WHERE `Title` LIKE '%Frozen%' 
    OR `other_title` LIKE '%Frozen%'  
    GROUP BY `Title` ORDER BY CASE 
    WHEN `Title` = 'Frozen' THEN 0
    WHEN `Title` like 'Frozen%' THEN 1
    WHEN `Title` like '% %Frozen% %' THEN 2
    WHEN `Title` like '%Frozen' THEN 3
    ELSE 4
    END, `Title`

这是的结果

4504    Frozen
509     Frozen Assets
20649   Frozen Flesh
24752   Frozen Kiss
25675   Frozen Land
4783    Frozen River
11856   Frozen Scream
21755   Frozen Silence
26333   Frozen Thrills
8532    Frozen Water
21126   A Frozen Flower
6158    The Frozen Ground
7320    Winter of Frozen Dreams
4557    Cashback
16196   God Don't Make the Laws
28160   The Thaw

好的，结果看起来很好，除了有重复标题的地方没有显示。

知道如何解决这个问题吗？？

Answer 1

group by正在删除重复项。只需删除：

SELECT *
FROM `movies` 
WHERE `Title` LIKE '%Frozen%' OR `other_title` LIKE '%Frozen%'  
ORDER BY (CASE WHEN `Title` = 'Frozen' THEN 0
               WHEN `Title` like 'Frozen%' THEN 1
               WHEN `Title` like '% %Frozen% %' THEN 2
               WHEN `Title` like '%Frozen' THEN 3
               ELSE 4
          END), `Title`

Answer 2

在我看来，您可能想要在MySQL中使用自然语言搜索功能。这将允许您将搜索条件与多个字段相匹配，并使它们按相关性自动排序。

有关详细信息，请参阅此链接：http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html