以下是我使用的数据集（下次请使用文本而不是图片，图片无法复制/粘贴）：

CREATE TABLE matches ( 
    match_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    home_team_name VARCHAR(30) NOT NULL,
    away_team_name VARCHAR(30) NOT NULL,
    home_score INT NOT NULL,
    away_score INT NOT NULL
); 

INSERT INTO matches (match_id, home_team_name, away_team_name, home_score, away_score) VALUES
(123, "Barcelona", "Paris", 3, 0),
(124, "Barcelona", "Milan", 2, 2),
(125, "Madrid", "Paris", 1, 1),
(126, "Milan", "Madrid", 1, 2),
(127, "Barcelona", "Madrid", 2, 0),
(128, "Milan", "Paris", 3, 4),
(201, "Paris", "Barcelona", 0, 0),
(202, "Paris", "Milan", 1, 2),
(203, "Paris", "Madrid", 2, 3),
(204, "Madrid", "Barcelona", 2, 3),
(205, "Madrid", "Milan", 3, 1),
(206, "Milan", "Barcelona", 2, 0);

您需要像您一样使用更多的计数器来计算胜率。此请求将计算总胜率和平均胜率以及点数：

SELECT
    @rownum := @rownum + 1 as matchNo,
    @rowvictory := (SELECT
     CASE 
        WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 1
        WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 1
        ELSE 0
        END) AS victory,
    @rowpoints := (SELECT
     CASE 
        WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 3
        WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 3
        WHEN home_score > away_score AND away_team_name = 'Barcelona' THEN 0
        WHEN home_score < away_score AND home_team_name = 'Barcelona' THEN 0
        WHEN home_score = away_score AND home_team_name = 'Barcelona' THEN 1
        WHEN home_score = away_score AND away_team_name = 'Barcelona' THEN 1
        END) AS points,
    (SELECT @points := @points + @rowpoints) as total_points,
    (SELECT @points / @rownum) AS average_points,
    (SELECT @victories := @victories + @rowvictory) as total_victories,
    (SELECT @victories / @rownum) AS average_victories
FROM matches
JOIN (SELECT @rownum := 0) AS rownum
JOIN (SELECT @points := 0) AS points
JOIN (SELECT @victories := 0) AS victories
WHERE home_team_name = 'Barcelona'
OR away_team_name = 'Barcelona'

-- matchNo  victory points total_points average_points total_victories average_victories
-- 1        1       3      3            3              1               1
-- 2        0       1      4            2              1               0.5
-- 3        1       3      7            2.33           2               0.66
-- 4        0       1      8            2              2               0.5
-- 5        1       3      11           2.2            3               0.6
-- 6        0       0      11           1.83           3               0.5

现在，您只需将其 Package 在另一个查询中，即可按所需的顺序获得所需的列：

SELECT matchNo, average_victories AS winrate, points AS outcome
FROM (
    SELECT
        @rownum := @rownum + 1 as matchNo,
        @rowvictory := (SELECT
         CASE 
            WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 1
            WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 1
            ELSE 0
            END) AS victory,
        @rowpoints := (SELECT
         CASE 
            WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 3
            WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 3
            WHEN home_score > away_score AND away_team_name = 'Barcelona' THEN 0
            WHEN home_score < away_score AND home_team_name = 'Barcelona' THEN 0
            WHEN home_score = away_score AND home_team_name = 'Barcelona' THEN 1
            WHEN home_score = away_score AND away_team_name = 'Barcelona' THEN 1
            END) AS points,
        (SELECT @points := @points + @rowpoints) as total_points,
        (SELECT @points / @rownum) AS average_points,
        (SELECT @victories := @victories + @rowvictory) as total_victories,
        (SELECT @victories / @rownum) AS average_victories
    FROM matches
    JOIN (SELECT @rownum := 0) AS rownum
    JOIN (SELECT @points := 0) AS points
    JOIN (SELECT @victories := 0) AS victories
    WHERE home_team_name = 'Barcelona'
    OR away_team_name = 'Barcelona'
) AS t

-- matchNo winrate  outcome
-- 1       1        3
-- 2       0.5      1
-- 3       0.66     3
-- 4       0.5      1
-- 5       0.6      3
-- 6       0.5      0