mysql - 按文字排序 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

结果是:small, medium, huge, big,

我需要这样排序:小，中，大，大...

按“字面”排序有什么解决办法吗？我想是这样的，但它不起作用:

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

您可以使用 FIELD 来做到这一点功能

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')