我有一个有3列的表
id | name | description
------------------------------------------
1 | Service marketing | Description for the marketing
2 | Marketing | Service marketing
3 | Great customer Service | Helpful shows your customers that you really do care about them.
我想从列名和描述中搜索基于"服务"的值，还需要按名称和描述按asc顺序排列值。我期望结果如下。
id | name | description
------------------------------------------
1 | Service marketing | Description for the marketing
3 | Great customer Service | Helpful shows your customers that you really do care about them.
2 | Marketing | Service marketing
我已经尝试了以下查询，但不工作
SELECT * FROM search where `name` LIKE '%service%'
OR `description` LIKE '%service%'
ORDER BY name,description;
上述查询的结果
id | name | description
------------------------------------------
3 |Great customer Service | Helpful shows your customers that you really do c...
2 |Marketing | Service marketing
1 |Service marketing | Description for the marketing
帮我一下。感谢
如果您想按列中"服务"的位置排序，您应该使用此
select position('Service' IN name) as p1, name, position('Service' IN description) as p2, description
from my table
where position('Service' IN name) != 0
and position('Service' IN description) != 0
order by p1 desc, p2 desc;
您可以使用类似的东西
SELECT S.*,
case
WHEN position('service', name) = 0 THEN 65535
ELSE position('service', name)
END as p1,
case
WHEN position('service', description) = 0 THEN 65535
ELSE position('service', description)
END as p2
FROM search as S where
name LIKE '%service%' OR description LIKE '%service%'
ORDER BY p1, p2
因为您需要ASC订单
嘿，您可以使用以下查询来获得您期望的结果
select position('Service' in name) as p1, name,position('Service' in description) as p2,description from dd order by p2,name desc
您的预期结果
id | name | description
------------------------------------------
1 | Service marketing | Description for the marketing
3 | Great customer Service | Helpful shows your customers that you really do care about them.
2 | Marketing | Service marketing