MySQL多列重复值查询：统计球员单场多次进球次数

一、核心思路 #

你的表结构将单场比赛的进球球员存放在S1-S6列中，要统计单场多次进球的情况，首先需要把列数据转为行数据（将每个S列的球员ID和对应比赛ID拆成独立行），再通过分组聚合统计每场每个球员的进球数，最后再次聚合得到每个球员符合条件的场次数量。

二、完整SQL实现 #

1. 统计所有球员单场多次进球的场次（输出类似「球员11-3，球员10-2」） #

如果要将所有结果拼接成一个字符串：

SELECT
    GROUP_CONCAT(player_result SEPARATOR '，') AS final_output
FROM (
    SELECT
        CONCAT('球员', player_id, '-', COUNT(*)) AS player_result
    FROM (
        -- 列转行，提取所有非0的进球球员与对应比赛ID
        SELECT ID AS match_id, S1 AS player_id FROM matchscorer WHERE S1 != 0
        UNION ALL
        SELECT ID AS match_id, S2 AS player_id FROM matchscorer WHERE S2 != 0
        UNION ALL
        SELECT ID AS match_id, S3 AS player_id FROM matchscorer WHERE S3 != 0
        UNION ALL
        SELECT ID AS match_id, S4 AS player_id FROM matchscorer WHERE S4 != 0
        UNION ALL
        SELECT ID AS match_id, S5 AS player_id FROM matchscorer WHERE S5 != 0
        UNION ALL
        SELECT ID AS match_id, S6 AS player_id FROM matchscorer WHERE S6 != 0
    ) AS player_goals
    -- 按球员+比赛分组，筛选单场进球>=2的场次
    GROUP BY player_id, match_id
    HAVING COUNT(*) >= 2
) AS multi_goal_matches
-- 按球员分组，统计符合条件的场次数量
GROUP BY player_id;

如果要每个球员单独一行显示结果：

SELECT
    CONCAT('球员', player_id, '-', COUNT(*)) AS player_multi_goal_games
FROM (
    SELECT
        player_id,
        match_id
    FROM (
        SELECT ID AS match_id, S1 AS player_id FROM matchscorer WHERE S1 != 0
        UNION ALL
        SELECT ID AS match_id, S2 AS player_id FROM matchscorer WHERE S2 != 0
        UNION ALL
        SELECT ID AS match_id, S3 AS player_id FROM matchscorer WHERE S3 != 0
        UNION ALL
        SELECT ID AS match_id, S4 AS player_id FROM matchscorer WHERE S4 != 0
        UNION ALL
        SELECT ID AS match_id, S5 AS player_id FROM matchscorer WHERE S5 != 0
        UNION ALL
        SELECT ID AS match_id, S6 AS player_id FROM matchscorer WHERE S6 != 0
    ) AS player_goals
    GROUP BY player_id, match_id
    HAVING COUNT(*) >= 2
) AS multi_goal_matches
GROUP BY player_id;

2. 筛选单场进球2次、3次等特定次数的情况 #

只需修改中间分组的HAVING条件即可：

• 统计单场进2球的场次：

SELECT
    CONCAT('球员', player_id, '-', COUNT(*)) AS player_2goal_games
FROM (
    SELECT
        player_id,
        match_id
    FROM (
        SELECT ID AS match_id, S1 AS player_id FROM matchscorer WHERE S1 != 0
        UNION ALL
        SELECT ID AS match_id, S2 AS player_id FROM matchscorer WHERE S2 != 0
        UNION ALL
        SELECT ID AS match_id, S3 AS player_id FROM matchscorer WHERE S3 != 0
        UNION ALL
        SELECT ID AS match_id, S4 AS player_id FROM matchscorer WHERE S4 != 0
        UNION ALL
        SELECT ID AS match_id, S5 AS player_id FROM matchscorer WHERE S5 != 0
        UNION ALL
        SELECT ID AS match_id, S6 AS player_id FROM matchscorer WHERE S6 != 0
    ) AS player_goals
    GROUP BY player_id, match_id
    HAVING COUNT(*) = 2 -- 指定单场进球数
) AS two_goal_matches
GROUP BY player_id;

• 统计单场进3球的场次，只需将HAVING COUNT(*) = 2改为HAVING COUNT(*) = 3即可。

三、关于SELECT DISTINCT、COUNT DISTINCT、GROUP BY的说明 #

• GROUP BY是必须的：需要两次分组，第一次按「球员ID+比赛ID」聚合得到单场进球数，第二次按「球员ID」聚合得到符合条件的场次数量，这两步都依赖GROUP BY完成分组统计。
• COUNT DISTINCT不需要：S1-S6的非0值都是独立的进球记录，直接用COUNT(*)就能准确统计单场进球数，不需要去重。
• SELECT DISTINCT不需要：GROUP BY已经会自动确保分组后的结果无重复，不需要额外去重。

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