按自定义顺序对包含字母和符号的值进行排序 [英] Sort values that contain letters and symbols in a custom order

问题描述

你能改变 MySQL 按功能排序吗?我正在尝试根据任意顺序对我的值进行排序.

Can you change the MySQL sort by function? I am trying to sort my values according to an arbitrary order.

目前正在寻找注入函数的方法，除了添加列和修改导入之外，这可能对我有所帮助.

Currently looking for ways to inject a function that might help me out here short of adding a column and modifying the import.

这是我想要的顺序:

AAA
AA+
AA
AA-
A+
A
A-
BBB+
BBB
BBB-
BB+
BB
BB-
B+
B
B-
CCC+
CCC
CCC-
CC

这是我使用排序的结果:

This is my result using sort by:

A
A+
A-
AA
AA+
AA-
AAA
B
B+
B-
BB
BB+
BB-
BBB
BBB+
BBB-
C
CC
CCC
CCC+
CCC-

尝试但出现语法错误:

CREATE FUNCTION sortRating (s CHAR(20))
RETURNS INT(2)
DECLARE var INT
CASE s
    WHEN 'AAA' THEN SET var = 1
    WHEN 'AA+' THEN SET var = 2
    ELSE
        SET VAR = 3
END CASE
RETURN var
END;

推荐答案

这可以使用以下语法:

ORDER BY FIELD(<field_name>, comma-separated-custom-order)

例如，如果您要排序的表达式称为 rating，则您的 ORDER BY 子句将显示为:

for instance, if the expression you want to order by is called rating, then your ORDER BY clause would read:

ORDER BY FIELD(rating, 'AAA', 'AA+', 'AA', 'AA-', 'A+', 'A', 'A-', 
                       'BBB+', 'BBB', 'BBB-', 'BB+', 'BB', 'BB-', 
                       'B+', 'B', 'B-', 'CCC+', 'CCC', 'CCC-', 'CC')

这是关于场函数

这篇关于按自定义顺序对包含字母和符号的值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！