SQL Server中员工活动与应用使用时间关联表连接问题排查

需求说明 #

需要关联两张表：

• Employee_Data（约1万行）：存储员工每日各项活动的时段信息
• Application_Data（约6万行）：存储对应时段内员工各应用的使用明细
  目标是将每个应用使用记录匹配到所属的员工活动时段中。

表结构与示例数据 #

Employee_Data表 #

Application_Data表 #

期望关联结果 #

尝试的SQL（返回空表） #

SELECT 
    A.Employee,
    A.Activity,
    A.Date,
    A.[Start Time],
    A.[End Time],
    B.Application,
    B.[Start Time Stamp],
    B.[End Time Stamp]
FROM Employee_Data A 
RIGHT OUTER JOIN Application_Data B ON A.[Employee] = B.[Employee Name]
    AND A.Date = B.Date
    AND B.[Start Time Stamp] >= A.[Start Time]
    AND B.[End Time Stamp] <= A.[End Time]
ORDER BY
    A.[Employee],
    A.Date,
    A.[Start Time],
    A.[End Time];

问题原因 #

1. 字段类型错误：日期和时间字段都使用了varchar类型，字符串比较逻辑与日期时间的实际逻辑不符，导致时段匹配条件失效。
2. 连接类型选择不当：RIGHT OUTER JOIN会保留Application_Data的所有行，但如果匹配条件不满足，Employee_Data的字段会返回NULL，而排序字段依赖Employee_Data的非NULL值，最终导致无有效结果返回。

修正后的SQL #

将日期和时间字段转换为datetime类型进行时段匹配，同时使用INNER JOIN获取匹配成功的关联记录：

SELECT 
    A.Employee,
    A.Activity,
    A.Date,
    A.[Start Time],
    A.[End Time],
    B.Application,
    B.[Start Time Stamp],
    B.[End Time Stamp]
FROM Employee_Data A 
INNER JOIN Application_Data B 
    ON A.Employee = B.[Employee Name]
    AND A.Date = B.Date
    -- 将日期与时间拼接后转换为datetime进行比较
    AND CAST(B.Date + ' ' + B.[Start Time Stamp] AS DATETIME) >= CAST(A.Date + ' ' + A.[Start Time] AS DATETIME)
    AND CAST(B.Date + ' ' + B.[End Time Stamp] AS DATETIME) <= CAST(A.Date + ' ' + A.[End Time] AS DATETIME)
ORDER BY
    A.Employee,
    A.Date,
    A.[Start Time],
    B.[Start Time Stamp];

优化建议 #

长期来看，建议修改表结构，将日期和时间字段改为date和time类型，或者直接使用datetime类型，避免字符串转换带来的性能损耗和逻辑错误：

-- 修改Employee_Data表结构
ALTER TABLE Employee_Data
ALTER COLUMN [Date] DATE;
ALTER TABLE Employee_Data
ALTER COLUMN [Start Time] TIME;
ALTER TABLE Employee_Data
ALTER COLUMN [End Time] TIME;

-- 修改Application_Data表结构
ALTER TABLE Application_Data
ALTER COLUMN [Date] DATE;
ALTER TABLE Application_Data
ALTER COLUMN [Start Time Stamp] TIME;
ALTER TABLE Application_Data
ALTER COLUMN [End Time Stamp] TIME;

修改后，关联SQL可以简化为：

SELECT 
    A.Employee,
    A.Activity,
    A.Date,
    A.[Start Time],
    A.[End Time],
    B.Application,
    B.[Start Time Stamp],
    B.[End Time Stamp]
FROM Employee_Data A 
INNER JOIN Application_Data B 
    ON A.Employee = B.[Employee Name]
    AND A.Date = B.Date
    AND B.[Start Time Stamp] >= A.[Start Time]
    AND B.[End Time Stamp] <= A.[End Time]
ORDER BY
    A.Employee,
    A.Date,
    A.[Start Time],
    B.[Start Time Stamp];

示例数据创建脚本 #

CREATE TABLE Employee_Data(
Employee varchar(25),
Activity varchar(25),
[Date] varchar(10),
[Start Time] varchar(10),
[End Time] varchar(10))
 
insert into Employee_Data values 
('Jeff','Call', '12/12/2020','10:00','10:15'),
('Jeff','Break','12/12/2020','10:15','10:30')


CREATE TABLE Application_Data(
[Employee Name] varchar(25),
[Application] varchar(25),
[Date] varchar(10),
[Start Time Stamp] varchar(10),
[End Time Stamp] varchar(10))
 
INSERT INTO Application_Data values 
('Jeff','AWS',  '12/12/2020','10:00','10:05'),
('Jeff','Outlook','12/12/2020','10:06','10:10'),
('Jeff','Teams','12/12/2020','10:11','10:15'),
('Jeff','Chrome','12/12/2020','10:16','10:23'),
('Jeff','Teams','12/12/2020','10:24','10:30')

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