如何用T-SQL统计满足指定日期区间跨组行级比较的人员数量

数据表定义与数据 #

Create Table Emps(person VARCHAR(50), started DATE, stopped DATE);

Insert Into Emps Values
('p1','2015-10-10','2016-10-10'),
('p1','2016-10-11','2017-10-11'),
('p1','2017-10-12','2018-10-13'),
('p2','2019-11-13','2019-11-13'),
('p2','2019-11-14','2020-10-14'),
('p3','2020-07-15','2021-08-15'),
('p3','2021-08-16','2022-08-16');

需求说明 #

使用T-SQL统计满足以下条件的人员数量（同一人员满足多次仅计1次）：

• 该人员的某一started日期（记为s1）大于其至少一个stopped日期（记为e1）；
• s1与e1处于手动指定的同一年份区间（如'2021-01-01'至'2022-01-01'）。

示例：当指定区间为'2016-01-01'至'2017-01-01'时，输出应为1——仅p1满足条件：其s1='2016-10-11'大于e1='2016-10-10'，且二者均在指定区间内。

核心困惑 #

如何实现同一person分组内的跨行列值比较（即同一person下，用某一行的started和另一行的stopped做对比）。

解决方案 #

可以通过自连接实现组内跨行比较，具体代码如下：

-- 指定查询区间
DECLARE @start_range DATE = '2016-01-01';
DECLARE @end_range DATE = '2017-01-01';

-- 统计符合条件的人员数量
SELECT COUNT(DISTINCT e1.person) AS qualified_person_count
FROM Emps e1
JOIN Emps e2 ON e1.person = e2.person
WHERE 
    -- s1大于e1
    e1.started > e2.stopped
    -- s1在区间内
    AND e1.started BETWEEN @start_range AND @end_range
    -- e1在区间内
    AND e2.stopped BETWEEN @start_range AND @end_range;

代码说明 #

1. 自连接逻辑：将Emps表自身连接，通过person字段关联，实现同一人员的不同行数据对比；
2. 条件筛选：同时满足s1 > e1、s1在指定区间、e1在指定区间三个条件；
3. 去重统计：使用COUNT(DISTINCT person)确保同一人员即使满足多次条件也只被计数一次。

示例验证 #

当设置@start_range='2016-01-01'、@end_range='2017-01-01'时，查询返回结果为1，符合需求预期。

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