如何为球员表添加球员参赛不同天数统计列？

我有一张存储球员姓名的表（假设表名为Players），结构如下：

另有一张存储比赛记录的表（假设表名为Matches），结构如下：

我希望在球员表中添加一列，显示每位球员参赛的不同天数，最终效果如下：

我的思路是：

• 遍历每位球员，从比赛表中筛选出包含该球员的所有记录（以Carmen为例，筛选结果如下）：

• 从这些记录中仅保留Date列和当前球员列：

• 去除重复记录：

• 最后统计记录数量

我是新手，无法实现这个思路，请问该如何完成需求？感谢！

你的思路完全正确，用SQL就能实现，步骤和你的思路一一对应：

1. 拆分比赛表的多列球员数据为行 #

首先把Matches表中Player1、Player2、Player3的列数据拆成单独的行，同时保留对应日期，这样方便后续筛选：

SELECT Date, Player1 AS Player FROM Matches WHERE Player1 IS NOT NULL
UNION ALL
SELECT Date, Player2 AS Player FROM Matches WHERE Player2 IS NOT NULL
UNION ALL
SELECT Date, Player3 AS Player FROM Matches WHERE Player3 IS NOT NULL

这段代码会输出每个参赛球员和对应的日期，和你思路里的第二张表结果一致。

2. 去除重复的「日期+球员」组合 #

用DISTINCT关键字去掉同一球员同一天的重复记录：

SELECT DISTINCT Date, Player
FROM (
    SELECT Date, Player1 AS Player FROM Matches WHERE Player1 IS NOT NULL
    UNION ALL
    SELECT Date, Player2 AS Player FROM Matches WHERE Player2 IS NOT NULL
    UNION ALL
    SELECT Date, Player3 AS Player FROM Matches WHERE Player3 IS NOT NULL
) AS PlayerDates

这一步得到的就是你思路里去重后的表。

3. 统计每位球员的参赛天数 #

最后把去重后的记录和Players表关联，按球员分组统计不同日期的数量：

SELECT 
    p.Player,
    COUNT(DISTINCT pd.Date) AS `Days (attendance)`
FROM Players p
LEFT JOIN (
    SELECT Date, Player1 AS Player FROM Matches WHERE Player1 IS NOT NULL
    UNION ALL
    SELECT Date, Player2 AS Player FROM Matches WHERE Player2 IS NOT NULL
    UNION ALL
    SELECT Date, Player3 AS Player FROM Matches WHERE Player3 IS NOT NULL
) AS pd ON p.Player = pd.Player
GROUP BY p.Player
ORDER BY p.Player;

用LEFT JOIN是为了确保即使有球员没参赛，也会显示在结果中（天数为0）；如果你的球员都有参赛记录，换成INNER JOIN也可以。

简化写法 #

可以把DISTINCT直接放到COUNT里，省去子查询的去重步骤，效果完全一样：

SELECT 
    p.Player,
    COUNT(DISTINCT pd.Date) AS `Days (attendance)`
FROM Players p
LEFT JOIN (
    SELECT Date, Player1 AS Player FROM Matches WHERE Player1 IS NOT NULL
    UNION ALL
    SELECT Date, Player2 AS Player FROM Matches WHERE Player2 IS NOT NULL
    UNION ALL
    SELECT Date, Player3 AS Player FROM Matches WHERE Player3 IS NOT NULL
) AS pd ON p.Player = pd.Player
GROUP BY p.Player
ORDER BY p.Player;

内容的提问来源于stack exchange，提问作者G. Lari