在MySQL中具有ORDER BY的多个LIKE运算符？
以下是使用ORDERBY实现多个LIKE运算符的语法-
select *from yourTableName order by ( yourColumnName like '%yourValue1%' ) + ( yourColumnName like '%yourValue2%' ) + . . N desc;
让我们创建一个表-
mysql> create table demo2 −> ( −> id int not null auto_increment, −> name varchar(100), −> primary key(id) −> );
借助insert命令将一些记录插入表中-
mysql> insert into demo2(name) values('John');
mysql> insert into demo2(name) values('David');
mysql> insert into demo2(name) values('John Smith');
mysql> insert into demo2(name) values('John Doe');
mysql> insert into demo2(name) values('David Miller');
mysql> insert into demo2(name) values('Chris');
mysql> insert into demo2(name) values('Bob Doe');使用select语句显示表中的记录-
mysql> select *from demo2;
这将产生以下输出-
+----+--------------+ | id | name | +----+--------------+ | 1 | John | | 2 | David | | 3 | John Smith | | 4 | John Doe | | 5 | David Miller | | 6 | Chris | | 7 | Bob Doe | +----+--------------+ 7 rows in set (0.00 sec)
以下是对多个LIKE运算符的查询-
mysql> select *from demo2 −> order by −> ( −> name like '%Doe%' −> ) −> + −> ( −> name like '%David%' −> ) desc;
这将产生以下输出-
+----+--------------+ | id | name | +----+--------------+ | 2 | David | | 4 | John Doe | | 5 | David Miller | | 7 | Bob Doe | | 1 | John | | 3 | John Smith | | 6 | Chris | +----+--------------+ 7 rows in set (0.00 sec)