使用混合数据类型的mysql自定义顺序

In the following mysql query I'm using a custom order by statement so I can display various sizes in a specific order instead of alphabetical:

在下面的mysql查询中，我使用自定义的order by语句，因此我可以按特定顺序而不是按字母顺序显示各种大小：

select distinct size 
from product p left join productsizes ps 
             on p.productcode = ps.size_prodcode 
order by field(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

In cases where some products also have numeric sizes, how do I write the order by so that it places the numeric sizes in an ascending order along with the custom order?

如果某些产品还具有数字大小，我该如何编写订单，以便将数字大小与自定义顺序一起按升序排列？

An example of the desired output:

所需输出的示例：

30, 32, 34, S, M, L

30,32,34，S，M，L

要么

S, M, L, 30, 32, 34

S，M，L，30,32,34

3 个解决方案

#1

FIELD() returns 0 when the search string is not found. Therefore:

当找不到搜索字符串时，FIELD（）返回0。因此：

ORDER BY FIELD(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'), size

#2

try

尝试

ORDER BY (CASE WHEN sizes REGEXP '(\d)+' THEN 0 ELSE 1 END) ASC,
         field(sizes, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

#3

You should try use an enum field with all your sizes in it. It will maintain its sort order.

您应该尝试使用包含所有大小的枚举字段。它将保持其排序顺序。

CREATE TABLE ... (
  ...
  size ENUM ('30', '32', '34', 'S', 'M', 'L')
  ...
);

要么

CREATE TABLE ... (
  ...
  size ENUM ('S', 'M', 'L', '30', '32', '34')
  ...
);

#1