如何用SQL查询个人值班班次及同班次搭档信息？

你的问题核心是宽表转窄表后自连接查询，原表结构（每列对应一个人）不便于直接匹配同班次搭档，我们可以通过以下步骤实现需求：

1. 数据结构转换：宽表转窄表 #

原表是"宽表"，需先转换成"窄表"（每行记录一个人的单日班次），这样更容易找到同日期同班次的搭档。

通用转换SQL（适配多数数据库） #

-- 生成窄表：每个人的每日班次单独成一行
SELECT Week, Date, 'Jim' AS Name, Jim AS Shift FROM schedule
UNION ALL
SELECT Week, Date, 'Chad' AS Name, Chad AS Shift FROM schedule
UNION ALL
SELECT Week, Date, 'Ernest' AS Name, Ernest AS Shift FROM schedule
UNION ALL
SELECT Week, Date, 'Rogier' AS Name, Rogier AS Shift FROM schedule

如果使用SQL Server，可通过内置UNPIVOT语法简化：

SELECT Week, Date, Name, Shift
FROM schedule
UNPIVOT (
    Shift FOR Name IN (Jim, Chad, Ernest, Rogier)
) AS unpivoted_schedule;

2. 查询指定日期的值班及搭档 #

以查询Jim在2025-04-14的信息为例，用CTE（公共表表达式）封装窄表，再通过自连接匹配同班次搭档：

WITH unpivoted_schedule AS (
    SELECT Week, Date, 'Jim' AS Name, Jim AS Shift FROM schedule
    UNION ALL
    SELECT Week, Date, 'Chad' AS Name, Chad AS Shift FROM schedule
    UNION ALL
    SELECT Week, Date, 'Ernest' AS Name, Ernest AS Shift FROM schedule
    UNION ALL
    SELECT Week, Date, 'Rogier' AS Name, Rogier AS Shift FROM schedule
)
SELECT 
    s.Week,
    s.Date,
    s.Shift AS Jim,
    partner.Name AS Partner
FROM unpivoted_schedule s
-- 自连接匹配同日期、同班次、非本人的搭档
JOIN unpivoted_schedule partner 
    ON s.Date = partner.Date 
    AND s.Shift = partner.Shift 
    AND s.Name = 'Jim'
    AND partner.Name != 'Jim'
WHERE s.Date = '2025-04-14';

执行结果：

3. 查询个人所有有效值班记录 #

若要查询Jim所有非N（不值班）的班次及搭档，去掉日期筛选并添加班次过滤：

WITH unpivoted_schedule AS (
    SELECT Week, Date, 'Jim' AS Name, Jim AS Shift FROM schedule
    UNION ALL
    SELECT Week, Date, 'Chad' AS Name, Chad AS Shift FROM schedule
    UNION ALL
    SELECT Week, Date, 'Ernest' AS Name, Ernest AS Shift FROM schedule
    UNION ALL
    SELECT Week, Date, 'Rogier' AS Name, Rogier AS Shift FROM schedule
)
SELECT 
    s.Week,
    s.Date,
    s.Shift AS Jim,
    partner.Name AS Partner
FROM unpivoted_schedule s
JOIN unpivoted_schedule partner 
    ON s.Date = partner.Date 
    AND s.Shift = partner.Shift 
    AND s.Name = 'Jim'
    AND partner.Name != 'Jim'
WHERE s.Shift != 'N'; -- 过滤不值班的N班次

关键逻辑说明 #

• 窄表转换：把每个人的列拆成独立行，让数据结构更适合关联查询。
• 自连接：通过同一日期、同一班次的条件，找到和目标用户同值班的搭档，同时排除用户本人。
• 灵活性：替换'Jim'为其他名字即可查询对应人员的值班信息；调整WHERE条件可筛选特定日期或班次。

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