MySQL Order by before group by in view (No Sub-Querys)

Я понимаю, что этот вопрос задавался довольно много раз, однако мне не удалось найти рабочее решение для моего случая.

По существу, моя проблема возникает, потому что MySQL не позволяет суб-запросы в представлениях.
Я нашел несколько обходных путей, но они, кажется, не работают.

Более подробный.

Мой первый стол (соревнования) хранит соревнования пользователей:

id_tournament | id_competition | id_user | result
-------------------------------------------------
       1      |        1       |     1   |   10
       1      |        1       |     2   |   30
       1      |        2       |     1   |   20
       1      |        2       |     3   |   50
       1      |        3       |     2   |   90
       1      |        3       |     3   |   100
       1      |        3       |     4   |   85

В этом примере есть три конкурса:

(
    user1 vs. user2,
    user1 vs. user3,
    user2 vs. user3 vs. user4
)

Моя проблема заключается в том, что мне нужно определить вид, который дает мне победителей в каждом конкурсе.

ожидаемый результат:

 id_tournament | id_competition | id_winner
 ------------------------------------------
        1      |        1       |     2   
        1      |        2       |     3  
        1      |        3       |     3   

Это можно решить с помощью запроса:

SELECT 
    id_tournament,
    id_competition,
    id_user as id_winner
FROM (

    SELECT * FROM competitions ORDER BY result DESC

) x GROUP BY id_tournament, id_competition

Этот запрос, однако, использует вложенный запрос( не разрешенный в представлениях), поэтому моим первым решением было определить «вспомогательное представление» как :

CREATE VIEW competitions_helper AS (
    SELECT * FROM competitions ORDER BY result DESC
);

CREATE VIEW competition_winners AS (
    SELECT 
        id_tournament,
        id as id_competition,
        id_user as winner
    FROM competitions_helper GROUP BY id_tournament, id_competition
);

Однако это не дает правильного результата.
Это результат будет тогда:

id_tournament | id_competition | id_winner
------------------------------------------
       1      |        1       |     1   
       1      |        2       |     1
       1      |        3       |     1

Чего я не понимаю, так это почему он работает, когда я использую вложенные запросы, и почему он дает другой результат с тем же самым утверждением в представлении.

Любая помощь ценится, большое спасибо.

1 ответ

  1. Это связано с GROUP BYповедением .

    В этом случае сервер свободен выбирать любое значение из каждой группы, поэтому, если они не совпадают, выбранные значения неопределенны, что, вероятно, не то, что вы хотите.

    Я бы решил проблему таким образом:

    CREATE VIEW competitions_helper AS (
        SELECT      id_tournament,
                    id_competition,
                    MAX(result) as winning_result
            FROM    competitions
        GROUP BY    id_tournament,
                    id_competition
    );
    
    CREATE VIEW competition_winners AS (
        SELECT      c.id_tournament,
                    c.id_competition,
                    c.id_user
            FROM    competitions c
        INNER JOIN  competitions_helper ch
                ON  ch.id_tournament = c.id_tournament
                AND ch.id_competition = c.id_competition
                AND ch.winning_result = c.result
    );