多个LIKE运算子ORDER BY最强 [英] Multiple LIKE Operator ORDER BY Strongest
本文介绍了多个LIKE运算子ORDER BY最强的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下查询:
SELECT * FROM table_name
WHERE (genre LIKE '%romance%'
OR genre LIKE '%comedy%'
OR genre LIKE '%horror%')
ORDER BY *the column that has more*
或类似的东西
$sql = "SELECT * FROM table WHERE
(genre LIKE '%romance%' AND genre LIKE '%comedy%' AND genre LIKE '%horror%')
#If result < 12
(genre LIKE '%romance%' AND genre LIKE '%comedy%') OR (genre LIKE '%romance%' AND genre LIKE '%horror%') OR (genre LIKE '%comedy%' AND genre LIKE '%horror%')
#If result < 12
(genre LIKE '%romance%' OR genre LIKE '%comedy%' OR genre LIKE '%horror%')";
我的意思是,如果有一部电影具有这三种类型,我想首先获得它,否则我获得一部具有浪漫和喜剧或浪漫与恐怖或喜剧与恐怖的电影.
I mean if there is a movie has these three genres I want to get it first else I get movie which has romance and comedy or romance and horror or comedy and horror.
推荐答案
条件运算符为true时返回1
,为false时返回0
.因此,将匹配项的总数加起来.
A conditional operator returns 1
when it's true, 0
when it's false. So add up the number of matches.
ORDER BY (genre LIKE '%romance%')
+ (genre LIKE '%comedy%')
+ (genre LIKE '%horror%') DESC
标准化表格会更好.添加表movie_genre
如
It would be better to normalize your tables. Add a table movie_genre
like
CREATE TABLE movie_genre (
movie_id INT(11) NOT NULL, # foreign key to movie.id
genre_id INT(11) NOT NULL # foreign key to genre.id
);
然后您将执行以下查询:
Then you would do a query like:
SELECT m.*
FROM movie AS m
JOIN movie_genre AS mg ON m.id = mg.movie_id
JOIN genre AS g ON g.id = mg.genre_id
WHERE g.name IN ('comedy', 'romance', 'horror')
GROUP BY m.id
ORDER BY COUNT(*) DESC
这篇关于多个LIKE运算子ORDER BY最强的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文