MySQL ORDER BY或GROUP BY [英] MySQL ORDER BY or GROUP BY

问题描述

关于： MySQL ORDER BY Customize
我还有一个问题。
我们有一个带有各种分数的 id_competitor 。

In relation to: MySQL ORDER BY Customized I have another question. We have an id_competitor with various scores.

id_competitor   score
1               WIN
2               50+
3               90+
4               90+
1               50
2               WIN
3               40
4               40+

我想用顺序，但按以下顺序：

I would like to use order by but in the following order:

id_competitor
2
1
4
3

我不知道该怎么做，用 SELECT DISTINCT使用ORDER BY或GROUP BY

I dont know how i should do it, with SELECT DISTINCT with ORDER BY or GROUP BY

推荐答案

根据其他答案， p>

based on other answer, I would do something like

SELECT s.id_competitor 
FROM (
SELECT 
  id_competitor, 
  SUM(CASE
       WHEN score = 'WIN' THEN 100000
       WHEN score = 'LOSER' THEN -100000
       WHEN score LIKE '%+' THEN score * 100 + 99
       ELSE score * 100
       END) as score
FROM myTable
GROUP BY id_competitor) as s
ORDER BY s.score DESC

SqlFiddle

这篇关于MySQL ORDER BY或GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！