修改MySQL查詢以生成正確的資料

我正在嘗試從足球桌上生成資料。

可以在下面看到該表，并突出顯示所需的資料。

需要生成的資料如下所示。

現在我已經做了幾天了，我已經設法到達了某個地方，即使我離想要的輸出還很遠。這是我目前的查詢。

SET @rownum := 0;
SELECT (@rownum := @rownum   1) as matchNo,
(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 outcome,
(SELECT SUM(outcome) / 3) AS winrate
FROM matches
ORDER BY matches.match_id

我得到的結果如下

我要做的是將勝率作為第二個欄位，將結果作為最后一個欄位，顯然得到正確的勝率。謝謝大家。

uj5u.com熱心網友回復：

這是我使用的資料集（下次請使用文本而不是圖片，不能復制/粘貼）：

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

現在您可以簡單地將其包裝在另一個查詢中，以按照您想要的順序獲取您想要的列：

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

標籤：mysql phpmyadmin

上一篇：連接到使用Docker安裝的Laravel9中的mySQL

下一篇：通過Gorm查詢模型