phpmyadmin 修改MySQL查询以生成正确的数据

mum43rcc  于 2022-11-09  发布在  PHP
关注(0)|答案(1)|浏览(139)

我正在尝试从足球桌上生成一个数据。
下表中突出显示了所需的数据。

需要生成的数据如下所示。

现在我已经做了几天了,我已经设法取得了一些进展,尽管我离期望的输出还很远。

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

我得到的结果如下

我尝试做的是将winrate移动为第二个字段,将outcome移动为最后一个字段,显然得到了正确的winrate。提前感谢大家。

nbewdwxp

nbewdwxp1#

以下是我使用的数据集(下次请使用文本而不是图片,图片无法复制/粘贴):

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

相关问题