mysql - 使用通配符按大小写排序

Question

我有一个看起来像这样的表格:

 | id | name | price | number |
 ---------------------------
 | 4  | fr-1 | 1,00  |  9984  |
 | 5  | fr-2 | 5,20  |  5421  |
 | 6  | fr-3 | 3,40  |  7845  |
 | 7  | de-1 | 6,20  |  9494  |
 | 8  | de-3 | 3,80  |  1254  |
 | 9  | de-6 | 3,90  |  3197  |

我想像这样对它们进行排序:(显然这是错误的，但它希望显示我想要实现的目标)

 ORDER BY CASE
 WHEN name LIKE 'fr-%' THEN name
 WHEN name LIKE 'de-%' THEN price
 END ASC;

我期望这个结果:

 FRENCH PRODUCT
 name: fr-1  |  price: 1,00
 name: fr-2  |  price: 5,20
 name: fr-3  |  price: 3,40

 GERMAN PRODUCT
 name: de-3  |  price: 3,80
 name: de-6  |  price: 3,90
 name: de-1  |  price: 6,20

Answer 1

你想要这样的东西:

ORDER BY CASE
            WHEN name LIKE 'fr-%' THEN 0
            WHEN name LIKE 'de-%' THEN 1
         END ASC,
         CASE
            WHEN name LIKE 'fr-%' THEN name
            WHEN name LIKE 'de-%' THEN price
         END ASC;

'fr-%' like 记录将首先出现，然后是 'de-%' 记录。然后，第一组将按 name 排序，而第二组将按 price 排序。

Demo here