如何编写SQL查询指定比赛日期下无日程封禁的裁判员名单

核心筛选逻辑 #

我们只需要排除「对应比赛当日存在封禁记录」的裁判即可，用NOT EXISTS实现该逻辑性能更稳定，不会出现空值导致NOT IN失效的问题。

通用基础查询（返回单行列的裁判姓名） #

如果你的入参是选中的赛事ID@GameId，关联Games表匹配对应比赛日期即可：

SELECT o.RefId, o.Name
FROM Officials o
WHERE NOT EXISTS (
    SELECT 1
    FROM Blocks b
    INNER JOIN Games g ON b.BlockDate = g.GameDate
    WHERE g.GameId = @GameId
      AND b.RefId = o.RefId
)

如果你的入参是直接传入比赛日期@GameDate，可以简化逻辑：

SELECT o.Name
FROM Officials o
WHERE NOT EXISTS (
    SELECT 1 FROM Blocks b
    WHERE b.RefId = o.RefId AND b.BlockDate = @GameDate
)

按要求拼接为字符串输出 #

不同数据库的字符串拼接函数有差异，以下是主流数据库的实现：

MySQL 版本 #

SELECT GROUP_CONCAT(
    CASE WHEN rn = total THEN CONCAT('and ', Name) ELSE Name END
    ORDER BY o.RefId
    SEPARATOR ', '
) AS available_referees
FROM (
    SELECT 
        o.Name,
        ROW_NUMBER() OVER (ORDER BY o.RefId) AS rn,
        COUNT(*) OVER () AS total
    FROM Officials o
    WHERE NOT EXISTS (
        SELECT 1
        FROM Blocks b
        INNER JOIN Games g ON b.BlockDate = g.GameDate
        WHERE g.GameId = @GameId
          AND b.RefId = o.RefId
    )
) t

SQL Server 版本 #

SELECT STRING_AGG(
    CASE WHEN rn = total THEN 'and ' + Name ELSE Name END,
    ', '
) WITHIN GROUP (ORDER BY o.RefId) AS available_referees
FROM (
    SELECT 
        o.Name,
        o.RefId,
        ROW_NUMBER() OVER (ORDER BY o.RefId) AS rn,
        COUNT(*) OVER () AS total
    FROM Officials o
    WHERE NOT EXISTS (
        SELECT 1
        FROM Blocks b
        INNER JOIN Games g ON b.BlockDate = g.GameDate
        WHERE g.GameId = @GameId
          AND b.RefId = o.RefId
    )
) t

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