mysql - SQL - 使用 UNION 的优先级

Question

我目前正在为网站开发搜索引擎。我有一张 table 电影:

| id_movie | title                    | subtitle                   | primary_keyword     | keywords              | reference |
|-----------|--------------------------|----------------------------|----------------------|-----------------------|-----------|
| 1         | The Lord of the Rings    | The Fellowship of the Ring | lord rings           | lord rings fellowship | LORFR     |
| 2         | The Lord of the Rings    | The Two Towers             | lord rings           | lord two towers       | LORTT     |
| 3         | The Lord of the Rings    | The Return of the King     | lord                 | return king           | LORRK     |
| 4         | The Shawshank Redemption |                            | shawshank redemption | shawshank redemption  | SSR       |

(这只是一个例子)

我想用这个优先级进行搜索:1 - 引用2 - 标题3 - 关键词4 - 主关键字5 - 副标题

这是我的:

SELECT * FROM (
SELECT 1 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  reference LIKE '%rings%'
UNION 
SELECT 2 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  title LIKE '%rings%'
UNION 
SELECT 3 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  keywords LIKE '%rings%'
UNION 
SELECT 4 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  primary_keyword LIKE '%rings%'
UNION 
SELECT 5 as prio, title, subtitle, reference
 FROM   movies 
 WHERE  subtitle LIKE '%rings%') as BigSelect
 ORDER BY prio ASC

它有效，但我想要的是:如果两部电影的标题中有“环”，那么下一个优先级是关键字然后，如果他们在关键字中有两个“环”，请检查 primary_keyword ....

我想要的最终结果是:

| title                     | subtitle                   | reference |
|---------------------------|----------------------------|-----------|
| The Lord of the Rings     | The Fellowship of the Ring | LORFR     |
| The Lord of the Rings     | The Two Towers             | LORTT     |
| The Lord of the Rings     | The Return of the King     | LORRK     |

三部《指环王》的片名中都有“戒指”。那么只有“指环王”在关键字中有“戒指”然后'The Two Towers'在primary_keyword中有'rings'(不是王者归来)

谢谢!

Answer 1

将 order by 与多个键一起使用:

SELECT title, subtitle, reference
FROM movies
ORDER BY (reference LIKE '%rings%') DESC,
         (title LIKE '%rings%') DESC,
         (keywords LIKE '%rings%') DESC, 
         (primary_keyword LIKE '%rings%') DESC,
         (subtitle LIKE '%rings%') DESC;

MySQL 方便地将 bool 表达式视为数字，“1”表示真，“0”表示假。因此，DESC 将匹配放在首位。