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

时间:2022-06-25 15:33:21

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

or

要么

S, M, L, 30, 32, 34

S,M,L,30,32,34

3 个解决方案

#1


4  

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


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


0  

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

Or

要么

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

#1


4  

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


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


0  

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

Or

要么

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