多个LIKE运算子ORDER BY最强

本文介绍了多个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