MySQL SELECT和ORDER BY [英] MySQL SELECT and ORDER BY

问题描述

关于: MySQL ORDER BY或GROUP BY ，它更复杂.我有一个id_competitor，在不同的路线上得分都不同.

In relation to: MySQL ORDER BY or GROUP BY, I do it more complicated. I have an id_competitor with various scores in different routes.

id_competitor   score    id_route
1                WIN        2
2                WIN        2
3                50+        2
4                50+        2
1                70         1
2                70+        1
3                70         1
4                WIN        1

这是表格路线"

id       name
1      semi-final
2        final

结果应按以下顺序:

id_competitor
2
1
4
3

因为他们在决赛中势均力敌，并且为了打破僵局，所以我们进入了半决赛

Because they equalized in the final and to break a deadlock we look the semi-final

推荐答案

请考虑以下数据集...

Consider the following data set...

DROP TABLE IF EXISTS results;

CREATE TABLE results
(id_competitor   INT NOT NULL
,score    INT NOT NULL
,id_route INT NOT NULL
,PRIMARY KEY(id_competitor,id_route)
);

INSERT INTO results VALUES
(1,100,2),
(2,100,2),
(3,60,2),
(4 ,60,2),
(1,70,1),
(2,80,1),
(3,70,1),
(4,100,1);

SELECT * FROM results;
+---------------+-------+----------+
| id_competitor | score | id_route |
+---------------+-------+----------+
|             1 |    70 |        1 |
|             1 |   100 |        2 |
|             2 |    80 |        1 |
|             2 |   100 |        2 |
|             3 |    70 |        1 |
|             3 |    60 |        2 |
|             4 |   100 |        1 |
|             4 |    60 |        2 |
+---------------+-------+----------+

我们希望按照id_route = 2上的最高得分来安排竞争对手.如果得分相乘，则应考虑来自id_route 1的结果.正确的顺序应该是2、1、4、3.

We want to arrange competitors by highest score on id_route=2. Where scores are tied, results from id_route 1 should be taken into account. The correct order should be 2,1,4,3.

中间解决方案...

SELECT * 
  FROM results x 
  JOIN results y 
    ON y.id_competitor = x.id_competitor 
   AND y.id_route = 1 
 WHERE x.id_route = 2;
+---------------+-------+----------+---------------+-------+----------+
| id_competitor | score | id_route | id_competitor | score | id_route |
+---------------+-------+----------+---------------+-------+----------+
|             1 |   100 |        2 |             1 |    70 |        1 |
|             2 |   100 |        2 |             2 |    80 |        1 |
|             3 |    60 |        2 |             3 |    70 |        1 |
|             4 |    60 |        2 |             4 |   100 |        1 |
+---------------+-------+----------+---------------+-------+----------+

完整的解决方案...

Complete solution...

SELECT x.id_competitor
     , x.score final_score
     , y.score semi_final_score 
  FROM results x 
  JOIN results y 
    ON y.id_competitor = x.id_competitor 
   AND y.id_route = 1 
 WHERE x.id_route = 2 
 ORDER 
    BY final_score DESC
     , semi_final_score DESC;
+---------------+-------------+------------------+
| id_competitor | final_score | semi_final_score |
+---------------+-------------+------------------+
|             2 |         100 |               80 |
|             1 |         100 |               70 |
|             4 |          60 |              100 |
|             3 |          60 |               70 |
+---------------+-------------+------------------+

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