MySQL Order by 在 View 中的 group by 之前(无子查询)

Question

我意识到这个问题已经被问过很多次了，但是我还没有找到适合我的情况的可行解决方案。

本质上我的问题是因为 MySQL 不允许在 View 中使用子查询。我找到了一些解决方法，但它们似乎不起作用。

更详细...

我的第一个表(竞赛)存储用户竞赛:

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
)

我的问题是我需要定义一个 View ，为我提供每次比赛的获胜者。

预期结果:

 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

但是此查询使用子查询( View 中不允许)，因此我的第一个解决方案是将“帮助 View ”定义为:

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

我不明白的是为什么它在我使用子查询时起作用，以及为什么它在 View 中使用完全相同的语句给出不同的结果。

感谢任何帮助，非常感谢。

Answer 1

这是由于 GROUP BY behaviour .

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want.

我会这样解决这个问题:

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
);