确定字段值是否满足一年范围内的最小连续年份

Question

给定下表，

 PersonID     Year
---------- ----------
1          1991
1          1992
1          1993
1          1993
2          1990
2          1991
3          1991
3          1992
3          1994

有没有办法使用 SQL 选择查询来获取 PersonID，其中它至少有 3 行，连续年份在 1990 到 1995 年之间？在这种情况下，它应该只找到人员 ID 1。

感谢您的帮助。

Answer 1

我认为应该可以像下面这样的事情：

SELECT personID
FROM
    (
        SELECT
            CASE WHEN year - min(year) OVER (PARTITION BY PersonID ORDER BY YEAR ASC ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) = 2 THEN 'x' END as ConsecutiveFlag,
            PersonID
        FROM 
            (SELECT personID, year FROM table GROUP BY personID,year) subquery
        WHERE year BETWEEN 1990 and 1995   
    ) t1
WHERE ConsecutiveFlag = 'x'
GROUP BY personID

它使用窗口函数按日期排序并查看两行。它将当前年份与前两行的值进行比较，如果差值为 2，则我们知道我们有连续的年份。

外部查询筛选标记的记录并返回人员 ID。

Answer 2

尝试：

select a.PersonID from Table a
left join Table b
    on a.Personid = b.Personid and a.year = b.year + 1
left join Table c
    on a.Personid = c.Personid and b.year = c.year + 1
where b.year is not null 
    and c.year is not null
    and a.year <= 1995 and c.year >= 1990

Answer 3

您可以将表与自身连接起来：

declare @ConsecutiveYears as integer
set @ConsecutiveYears = 3
select distinct PersonYear.PersonID
from PersonYear
join PersonYear Consecutives
on PersonYear.PersonID = Consecutives.PersonID
    and Consecutives.Year between PersonYear.Year and PersonYear.Year + @ConsecutiveYears - 1
where PersonYear.Year >= 1990 and Consecutives.Year <= 1995
Group by PersonYear.PersonID, PersonYear.Year
Having COUNT(distinct Consecutives.Year) = @ConsecutiveYears

Answer 4

您可以使用 2 个self joins来完成此操作（您将在同一personId上加入表格 3 次，并在每个连接中year递增 1 以查找每个人的序列年份 3 次）

图式：

create table tbl_so(PersonID int,[Year] int)
insert into tbl_so values
(1,          1991),
(1,          1992),
(1,          1993),
(1,          1993),
(2,          1990),
(2,          1991),
(3,          1991),
(3,          1992),
(3,          1994)

查询：

 select distinct t1.personId
 from tbl_so t1
 join tbl_so t2 on t1.[year]+1=t2.[year] and t1.personId=t2.personId
 join tbl_so t3 on t2.[year]+1=t3.[year] and t2.personId=t3.personId

输出：

personId
1