使用GROUP与子查询计算未获正确值的技术问题

原查询中Matches Won数值异常偏高的核心原因：

• 主查询从set表出发，每个赛事（match）对应多条盘次（set）记录
• 计算Matches Won时，每一条set记录都会触发一次子查询判断，导致同一个获胜赛事被重复计数（重复次数等于该赛事的盘次数量）

比如某赛事有3盘，获胜球员的Matches Won会被加3次，而不是预期的1次。

先预计算每个赛事的获胜队伍，再基于赛事维度关联球员数据，避免重复计数：

-- 第一步：预计算每个赛事的获胜party
WITH match_winner AS (
    SELECT 
        idMatch,
        partySetWon AS winning_party
    FROM `tennisdb`.`set`
    GROUP BY idMatch, partySetWon
    ORDER BY idMatch, COUNT(*) DESC
    LIMIT ALL -- 保留每个match的最高盘数party，MySQL 8.0+可用
),
-- 第二步：预计算每个球员在各赛事的胜负盘数
player_set_stats AS (
    SELECT
        m2p.idPlayer,
        m2p.idMatch,
        SUM(IF(m2p.party = s.partySetWon, 1, 0)) AS sets_won_per_match,
        SUM(IF(m2p.party != s.partySetWon, 1, 0)) AS sets_lost_per_match
    FROM `tennisdb`.`match2player` m2p
    JOIN `tennisdb`.`set` s ON m2p.idMatch = s.idMatch
    GROUP BY m2p.idPlayer, m2p.idMatch
)
-- 最终统计
SELECT
    p.idPlayer AS Player,
    COUNT(DISTINCT p.idMatch) AS `Matches played`,
    SUM(IF(p.party = mw.winning_party, 1, 0)) AS `Matches Won`,
    SUM(pss.sets_won_per_match) AS `Sets Won`,
    SUM(pss.sets_lost_per_match) AS `Sets lost`
FROM `tennisdb`.`match2player` p
JOIN `tennisdb`.`match` m ON p.idMatch = m.id
LEFT JOIN match_winner mw ON p.idMatch = mw.idMatch
LEFT JOIN player_set_stats pss ON p.idPlayer = pss.idPlayer AND p.idMatch = pss.idMatch
WHERE m.idEvent = 6 AND m.status IN ('completed','awarded')
GROUP BY p.idPlayer
ORDER BY Player;

如果使用MySQL 5.x不支持CTE，可改用子查询版本：

SELECT
    p.idPlayer AS Player,
    COUNT(DISTINCT p.idMatch) AS `Matches played`,
    SUM(IF(p.party = mw.winning_party, 1, 0)) AS `Matches Won`,
    SUM(pss.sets_won_per_match) AS `Sets Won`,
    SUM(pss.sets_lost_per_match) AS `Sets lost`
FROM `tennisdb`.`match2player` p
JOIN `tennisdb`.`match` m ON p.idMatch = m.id
LEFT JOIN (
    SELECT 
        idMatch,
        partySetWon AS winning_party
    FROM `tennisdb`.`set`
    GROUP BY idMatch, partySetWon
    ORDER BY idMatch, COUNT(*) DESC
    LIMIT ALL
) mw ON p.idMatch = mw.idMatch
LEFT JOIN (
    SELECT
        m2p.idPlayer,
        m2p.idMatch,
        SUM(IF(m2p.party = s.partySetWon, 1, 0)) AS sets_won_per_match,
        SUM(IF(m2p.party != s.partySetWon, 1, 0)) AS sets_lost_per_match
    FROM `tennisdb`.`match2player` m2p
    JOIN `tennisdb`.`set` s ON m2p.idMatch = s.idMatch
    GROUP BY m2p.idPlayer, m2p.idMatch
) pss ON p.idPlayer = pss.idPlayer AND p.idMatch = pss.idMatch
WHERE m.idEvent = 6 AND m.status IN ('completed','awarded')
GROUP BY p.idPlayer
ORDER BY Player;

• 预计算赛事获胜方：通过match_winner模块按赛事维度统计出每个赛事的获胜队伍，确保每个赛事只返回一条获胜记录
• 按球员+赛事维度统计盘数：player_set_stats确保每个球员在单个赛事的盘数只统计一次，避免重复累加
• 从match2player出发关联：以球员-赛事关联表为主表，避免从set表带来的多数据行重复计数问题

内容的提问来源于stack exchange，提问作者formatter