MySQL的高级排序

mysql数据库排序进阶

在mysql的高级排序中可以根据其相内容相关度进行排序

例如：

SELECT
a.product_id,
a.category_id,
a.product_key,
a.product_name,
a.product_desc,
a.price,
a. STATUS,
a.verify_status,
a.type_id,
a.created_at,
b.category_name
FROM
market_product AS a
LEFT JOIN market_product_category AS b ON b.category_id = a.category_id
LEFT JOIN market_product_attribute_value AS av ON av.product_key = a.product_key
WHERE
(
a.product_name LIKE ?
OR a.product_desc LIKE ?
OR b.category_name LIKE ?
OR (
attribute_code = ?
AND av.attribute_value LIKE ?
)
)
AND a.type_id = ?
AND a.verify_status = ?
AND a.status = ?
ORDER BY
(
CASE
WHEN (
product_name LIKE ‘%北京市朝阳区东三环%’
) THEN
2
WHEN (
product_desc LIKE ‘%北京市朝阳区东三环%’
OR category_name LIKE ‘%北京市朝阳区东三环%’
OR (
attribute_code = ‘address’
AND attribute_value LIKE ‘%北京市朝阳区东三环%’
)
) THEN
1
ELSE
0
END
) DESC,
a.created_at DESC
LIMIT 10 OFFSET 0

注释：其中？用相应的变量替代

其中使用了order by 后有
(
CASE
WHEN (
product_name LIKE ‘%北京市朝阳区东三环%’
) THEN
2
WHEN (
product_desc LIKE ‘%北京市朝阳区东三环%’
OR category_name LIKE ‘%北京市朝阳区东三环%’
OR (
attribute_code = ‘address’
AND attribute_value LIKE ‘%北京市朝阳区东三环%’
)
) THEN
1
ELSE
0
END
) DESC
它是使用了相对复杂的排序，和case when 和or ,and 连用的方式，使MySQL的排序更加多样，可以满足更多的需求，同时要特别注意%的两端单引号 ’ ,一定不要掉了，否则会出现语法错误