mysql - 如何搜索两个表

Question

我得到一个按标题、 Actor 、描述等搜索电影的查询，并按标题中包含搜索词的电影对结果进行排序。

select movie.id, movie.title  
from movie 
where 
title like '%searchTerm%' or
cast like '%searchTerm%' or
director like '%searchTerm%' or 
FullPlot like '%searchTerm%' 
order by (title like '%searchTerm%') desc ## this gives priority to results thatr have the search term in the title

现在我有了一个名为“AlsoKnownAs”的新表。此表包含有关其他语言调用的电影的数据。 Cidade De Deus (2003) 又名上帝之城 (2003)

我在搜索电影时也需要搜索这个新表并将其作为次要排序进行排序。因此在 AKA 表中包含 searchTerm 的电影将排在标题中包含 searchTerm 的电影之后。

我不知道该怎么做。似乎我需要将找到的术语存储在一个变量中，以便我可以按它排序。

欢迎任何帮助

AlsoKownAs table
'id', 'int(11)', 'NO', 'PRI', NULL, 'auto_increment'
'movieId', 'varchar(10)', 'NO', '', NULL, ''
'aka', 'varchar(305)', 'NO', '', NULL, ''
'country', 'varchar(100)', 'YES', '', NULL, ''

Answer 1

如果您只想返回独特的电影，那么除了左联接之外，您还需要按 ID 和标题进行分组，这样您就不会得到具有多个别名的电影的重复项。

您可以使用条件聚合来确定是否有任何 aka 匹配并将其用于您的订单

select id, title from (
    select t1.id, t1.title,
        sum(aka like '%searchTerm%') aka_count
    from movie t1
    left join AlsoKnownAs t2 on t1.id = t2.movieId
    where title like '%searchTerm%' 
    or cast like '%searchTerm%' 
    or director like '%searchTerm%'  
    or FullPlot like '%searchTerm%' 
    or aka like '%searchTerm%'
    group by t1.id, t1.title
) order by (title like '%searchTerm%') desc, (aka_count > 0) desc

编辑

对 aka 表进行单表扫描并通过派生表左连接这些结果可能会更快:

select movie.id, movie.title  
from movie 
left join (
    select distinct movieId from AlsoKnownAs where aka like '%searchTerm%'
) t1 on t1.movieId = movie.id
where 
title like '%searchTerm%' or
cast like '%searchTerm%' or
director like '%searchTerm%' or 
FullPlot like '%searchTerm%' or
t1.movieId IS NOT NULL
order by (title like '%searchTerm%'), (t1.movieId IS NOT NULL) desc