我正在尝试比较同一表中的数据行

如果您只想列出这些录取与附近的匹配项，则可以使用谓词。EXISTS()

SELECT D.*
FROM Data D
WHERE EXISTS (
    SELECT D2.*
    FROM Data D2
    WHERE D2.ID = D.ID
    AND D2.Date BETWEEN DATEADD(day, -30, D.Date) AND DATEADD(day, 30, D.Date)
    AND D2.Date <> D.Date -- Using a unique admission ID here would be better
)
ORDER BY D.ID, D.Date

结果：

如果您还想包括入院前或入院后最接近的细节，则可以使用 .an 就像 a 对子选择。OUTER APPLY(SELECT TOP 1 ... ORDER BY ...)OUTER APPLYLEFT JOIN

SELECT D.*, PRIOR.Date as PriorDate, NEXT.Date as NextDate
FROM Data D
OUTER APPLY (
    SELECT TOP 1 D2.*
    FROM Data D2
    WHERE D2.ID = D.ID
    AND D2.Date >= DATEADD(day, -30, D.Date)
    AND D2.Date < D.Date
    ORDER BY D2.Date DESC
) PRIOR
OUTER APPLY (
    SELECT TOP 1 D2.*
    FROM Data D2
    WHERE D2.ID = D.ID
    AND D2.Date > D.Date
    AND D2.Date <= DATEADD(day, 30, D.Date)
    ORDER BY D2.Date
) NEXT
-- WHERE (PRIOR.Date IS NOT NULL OR NEXT.Date IS NOT NULL)
ORDER BY D.ID, D.Date

结果：

也可以使用 和 窗口函数。LAG()LEAD()

SELECT D.*
FROM (
    SELECT *,
        LAG(Date) OVER(PARTITION BY ID ORDER BY Date) AS PriorDate,
        LEAD(Date) OVER(PARTITION BY ID ORDER BY Date) AS NextDate
    FROM Data
) D
WHERE DATEDIFF(day, D.PriorDate, D.Date) <= 30
OR DATEDIFF(day, D.Date, D.NextDate) <= 30
ORDER BY D.ID, D.Date

结果：

请参阅此 db<>fiddle。

这只是一个提示。希望有一个例子来说明如何在这里列出你的问题以获得更好的答案。dbfiddle 笔记本总是很可爱。https://dbfiddle.uk/PahXd98E

一个可重现的例子可以澄清许多细微差别。我可以用 5 种不同的方式解释您的问题和预期结果。

摘要：这将为您提供所有ID，对于相同的ID，N行数。对于具有相同 ID 且未来 30 天小于或等于 30 天的_date的每条记录，一个记录。

输入：

id  _date
1   2023-05-01
2   2023-06-12
2   2023-05-17
2   2023-05-15
2   2023-08-01
3   2023-06-01
3   2023-06-15
3   2023-07-10

帮助重现的代码：

create table id_date (
  id integer,
  _date date
);

insert into id_date (id,_date) values (1, cast('2023-05-01' as date));
insert into id_date (id,_date) values (2, cast('2023-06-12' as date));
insert into id_date (id,_date) values (2, cast('2023-05-17' as date));
insert into id_date (id,_date) values (2, cast('2023-05-15' as date));
insert into id_date (id,_date) values (2, cast('2023-08-01' as date));
insert into id_date (id,_date) values (3, cast('2023-06-01' as date));
insert into id_date (id,_date) values (3, cast('2023-06-15' as date));
insert into id_date (id,_date) values (3, cast('2023-07-10' as date));

预期：

select t1.id,
       t1._date as original_date,
       t2._date as later_date_within_30_days
  from id_date t1
 inner
  join id_date t2 
    on t1.id = t2.id 
   and t2._date > t1._date -- strict inequality so avoid the record picking
                             -- up itself.
   and t2._date <= DATEADD(day, 30, t1._date)

结果

id  original_date   later_date_within_30_days
2   2023-05-17      2023-06-12
2   2023-05-15      2023-05-17
2   2023-05-15      2023-06-12
3   2023-06-01      2023-06-15
3   2023-06-15      2023-07-10

下面是使用 SQL Server 的解决方案。虽然你的要求不是很清楚，但我还是尝试并想出了一些可能有助于你得到答案的东西。first_id ， second_id ， first_date 在这里second_date说的是两个 id 及其日期之间的比较。如果这是你要找的，请告诉我。

    with prepare_data as
    (
    select a.id as first_id , b.id as second_id ,  
           a.admit_date as first_date , b.admit_date as second_date ,
           abs(DATEDIFF(day,a.admit_date,b.admit_date)) as diff  from
        (
            select id , convert(date , dt , 101) as admit_date from admit_dt
        ) as a , 
        (
            select id , convert(date , dt , 101) as admit_date from admit_dt
        ) as b
    where a.id <> b.id and abs(DATEDIFF(day,a.admit_date,b.admit_date))  <= 30
    )
    select 
           distinct case when first_id < second_id then first_id else second_id end as first_id ,
           case when first_id > second_id then first_id else second_id end as second_id ,
           case when first_date < second_date then first_date else second_date end as first_date , 
           case when first_date > second_date then first_date else second_date end as second_date ,  
           diff
    from prepare_data;