按文字排序SQL查询

Question

我正在使用mysql数据库，我有下一个查询：

SELECT DISTINCT OP1.meta_value AS meta_value 
FROM  wp_postmeta OP1 WHERE OP1.meta_key like 'size' 
and OP1.meta_value is not null and OP1.meta_value <> 'null' 
ORDER BY OP1.meta_value DESC

结果是:小，中，大，大，

我需要这样点菜:小，中，大，大.

是否有任何解决方案，以“文字”。我想是这样的，但不管用：

SELECT DISTINCT OP1.meta_value AS meta_value FROM
wp_postmeta OP1 WHERE OP1.meta_key like 'size' 
and OP1.meta_value is not null 
and OP1.meta_value <> 'null' 
ORDER BY ('small', 'medium', 'big', 'huge')

提前感谢

Answer 1

您可以使用字段函数来完成它。

SELECT DISTINCT 
    OP1.meta_value AS meta_value 
FROM
    wp_postmeta OP1 
WHERE OP1.meta_key like 'size' 
    and OP1.meta_value is not null 
    and OP1.meta_value <> 'null' 
ORDER BY FIELD (meta_value, 'small', 'medium', 'big', 'huge')

Answer 2

字段函数正是您要寻找的：

SELECT DISTINCT OP1.meta_value AS meta_value FROM
wp_postmeta OP1 WHERE OP1.meta_key like 'size' 
and OP1.meta_value is not null 
and OP1.meta_value <> 'null' 
ORDER BY FIELD (meta_value, 'small', 'medium', 'big', 'huge')

顺便说一句，注意使用LIKE是多余的-因为搜索中没有通配符，所以可以只使用OP1.meta_key = 'size'。

Answer 3

 SELECT DISTINCT OP1.meta_value AS meta_value FROM
 wp_postmeta OP1 WHERE OP1.meta_key like 'size' 
 and OP1.meta_value is not null 
 and OP1.meta_value <> 'null' 
 ORDER BY (CASE 
    WHEN meta_value='small' THEN 1 
    WHEN meta_value='medium' THEN 2
    WHEN meta_value='big' THEN 3
    WHEN meta_value='hude' THEN 4
END)