具有特定行值的特定顺序的订单记录

Question

  Name     | Faction     |   Score
----------------------------------------
  Alpha    | Viva        |   7000
  Beta     | Rico        |   8000
  Bravo    | Morgan      |   9000
  Active   | Rico        |   10000
  Delta    | Edison      |   11000
  Siphon   | Viva        |   12000
  Taximus  | More        |   13000
  Ariticus | Viva        |   14000
  Ariticus | Havoc       |   15000

在这个表中，我尝试首先按特定Faction的数据排序，然后像下面的一样按Score DESC继续排序[它们都是

SELECT Name, Score FROM memebers WHERE type = all GROUP BY id ORDER BY Faction = 'Viva' DESC, Faction = 'Rico' DESC, Score DESC

我希望得到的是

  Name     | Faction     |   Score
----------------------------------------
  Ariticus | Viva        |   14000
  Siphon   | Viva        |   12000
  Alpha    | Viva        |   7000
  Active   | Rico        |   10000
  Beta     | Rico        |   8000
  Ariticus | Havoc       |   15000
  Taximus  | More        |   13000
  Bravo    | Morgan      |   9000

但我一直只得到Score DESC的订单。

Answer 1

使用case表达式。

ORDER BY case when Faction = 'Viva' then 1
              when Faction = 'Rico' then 2
         else 3 end,
Score DESC

Answer 2

对于mysql，如果派系值列表很小，并且可以在参数中提供，您也可以尝试ORDER BY FIELD()，如下所示：

SELECT Name, Score 
FROM memebers 
WHERE type = all 
GROUP BY id 
ORDER BY FIELD(Faction, 'Viva', 'Rico', 'Havoc', 'More','Morgan'), Score DESC;