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