mysql - ORDER BY mysql 逗号分隔值

Question

您好，我正在尝试按逗号分隔值对我的数据库进行排序，但它对我来说似乎无法正常工作。这是我的 mysql 查询。

   SELECT *
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID=wp_postmeta.post_id
LEFT JOIN wp_term_relationships ON wp_posts.ID=wp_term_relationships.object_id
LEFT JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id=wp_term_taxonomy.term_id
LEFT JOIN wp_terms ON wp_term_taxonomy.term_id=wp_terms.term_id
WHERE wp_terms.name = 'Dimmers'
AND meta_key = 'feature_number_of_channels'
GROUP BY wp_posts.ID
ORDER BY meta_value  ASC

这是截图。 正如您从屏幕截图中看到的那样，它们似乎无法正常工作，请提供有关如何正确订购这些 ASC 的任何帮助？？？

我正在寻找的正确顺序是所有顺序中最高的，然后是这样的 ASC

24,48 - 4,6,12,24 - 12 - 6 - 6 - 3

Answer 1

更改您的 ORDER BY 以查看字符串的整数值:

ORDER BY CONVERT(SUBSTRING_INDEX(meta_value, ',', -1), SIGNED) DESC

这是一个例子:

SELECT "24,38" AS meta_value UNION
SELECT 1 AS meta_value UNION
SELECT 30 AS meta_value
ORDER BY CONVERT(SUBSTRING_INDEX(meta_value, ',', -1), SIGNED) DESC

返回:

Rows = 3
meta_value
24,38
30
3

祝你好运!