问题描述:

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.

This is the order I want:

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-

EDIT:

Attempting but getting syntax errors:

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;

网友答案:

This is possible using the following syntax:

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

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

Here's documentation on the FIELD FUNCTION

网友答案:

I see a pattern here:

BBB+
BBB
BBB-
BB+
BB
BB-
B+
B
B-

Think of each character as a column and sort each column in this order:

  1. Letters
  2. +
  3. empty string
  4. -
SELECT rating
FROM test
ORDER BY
         MID(rating, 1, 1),
    CASE MID(rating, 2, 1) WHEN '+' THEN 2 WHEN '' THEN 3 WHEN '-' THEN 4 ELSE 1 END, 
    CASE MID(rating, 3, 1) WHEN '+' THEN 2 WHEN '' THEN 3 WHEN '-' THEN 4 ELSE 1 END,
    CASE MID(rating, 4, 1) WHEN '+' THEN 2 WHEN '' THEN 3 WHEN '-' THEN 4 ELSE 1 END

SQL Fiddle

相关阅读:
Top