MySQL WHERE IN Query - ORDER BY 匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9800997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL WHERE IN Query - ORDER BY Match
提问by richie
I'm trying to rephrase my question, cause my last one wasn't clear to everyone.
我试图重新表述我的问题，因为我的最后一个问题对每个人都不是很清楚。
This is my Test Table
这是我的测试表
+----------+---------+-------+
| rel_id | genre | movie |
+----------+---------+-------+
| 1 | 1 | 3 |
| 2 | 8 | 3 |
| 3 | 3 | 3 |
| 4 | 2 | 5 |
| 5 | 8 | 5 |
| 6 | 3 | 5 |
| 7 | 1 | 8 |
| 8 | 8 | 8 |
| 9 | 3 | 8 |
| 10 | 5 | 9 |
| 11 | 7 | 9 |
| 12 | 9 | 9 |
| 13 | 4 | 9 |
| 14 | 12 | 9 |
| 15 | 1 | 10 |
| 16 | 8 | 10 |
| 17 | 3 | 10 |
| 18 | 5 | 10 |
| 19 | 1 | 11 |
| 20 | 2 | 11 |
| 21 | 8 | 11 |
| 22 | 5 | 11 |
| 23 | 3 | 11 |
+----------+---------+-------+
Result should be in the following order if I look for movies with genre 1, 8, 3 : Movie no. 3, 8, 10, 5, 11 (9 is out).
如果我查找类型为 1、8、3 的电影，结果应按以下顺序排列：电影编号。3、8、10、5、11（9 出局）。
If it's not possible then I just want all with the exact match "1, 8, 3", in that case I just would get movie no. 3 AND 8.
如果不可能，那么我只想要完全匹配“1、8、3”的所有内容，在这种情况下，我只会得到电影编号。3 和 8。
回答by Nikola Markovinovi?
If I understand correctly you want to sort results by number of matches in descending order. To do so, you might try:
如果我理解正确，您想按降序按匹配数对结果进行排序。为此，您可以尝试：
SELECT movie
FROM genre_rel
WHERE genre IN (1, 8, 3)
GROUP BY movie
order by count(movie) desc
And if you want movies that match all the criteria, you might use:
如果您想要符合所有条件的电影，您可以使用：
SELECT movie
FROM genre_rel
WHERE genre IN (1, 8, 3)
GROUP BY movie
HAVING count(movie) = 3
UPDATE:
更新：
This is the best I can do in MySql. You cannot use IN because you cannot extract information about order of filters. If you add derived table as a means of filtering, you can append this information and use it to show results by positional matches. Note that you do not provide any ordering info in genre_rel table so you don't really know the importance of genres per movie. This query will give you matching movies by descending order of importance of genres in criteria:
这是我在 MySql 中能做的最好的事情。您不能使用 IN，因为您无法提取有关过滤器顺序的信息。如果您添加派生表作为过滤手段，您可以附加此信息并使用它来按位置匹配显示结果。请注意，您没有在流派_rel 表中提供任何排序信息，因此您并不真正了解每部电影流派的重要性。此查询将按标准中流派重要性的降序为您提供匹配的电影：
SELECT movie
FROM genre_rel
INNER join
(
select 1 genre, 1000 weight
union all
select 8, 100
union all
select 3, 10
) weights
on genre_rel.genre = weights.genre
GROUP BY movie
order by sum(weight) desc
Note that all the movies except 5 belong to all 3 genres. If you add a column to genre_rel representing order of importance you might devise some mathematics (weight - importance or something similar).
请注意，除 5 以外的所有电影都属于所有 3 种类型。如果您向表示重要性顺序的流派添加一列，您可能会设计一些数学（权重 - 重要性或类似的东西）。
回答by richie
You can do do this by following way:
您可以通过以下方式执行此操作：
SELECT distinct movie FROM genre_rel WHERE genre IN (1, 8, 3);
回答by kandarp
You had given In criteria on genre column. If you movie 5 also contain genre 8 & 3 also. Because of that reason, you are getting movie 5 as well.
您已在流派列中给出了 In 条件。如果您的电影 5 还包含类型 8 和 3。由于这个原因，您也将获得电影 5。
回答by Tomasz Machura
How about something like:
怎么样：
SELECT movie, SUM(gentre) AS count
FROM genre_rel
WHERE genre IN (1, 8, 3)
GROUP BY movie
ORDER BY count DESC
回答by Devart
Try this query -
试试这个查询 -
SELECT movie FROM genre_rel
GROUP BY movie
ORDER BY
IF(COUNT(IF(genre IN (1, 8, 3), 1, NULL)) = 3, genre, genre + 1000)
Where 1000 is a order offset for movies that don't match, increase this value if you need.
其中 1000 是不匹配电影的订单偏移量，如果需要，请增加此值。
Also, you may try this ORDER BY -
另外，你可以试试这个 ORDER BY -
ORDER BY IF(COUNT(IF(genre IN (1, 8, 3), 1, NULL)) = 3, genre, MAX(genre))
