sql - 如何在 SQL Server 中使用日期范围连接两个表？

Question

我有 2 张桌子：

• 约会

• 病人访问。

我想根据日期加入这两个表。尽管每个表中的日期完全不同。我想要的是加入紧随其后的vital date那个appt date。

就像我希望 appt-date'22-07-2020'与'23-07-2020'和'22-09-2020'一起加入'25-09-2020'。

简单的加入patient id是不够的。

解决方案不应包含游标。

标签： sqlsql-serverdatejoin

Answer 1

这是您可以尝试的示例...

IF OBJECT_ID('tempdb..#Appointments') IS NOT NULL DROP TABLE #Appointments
IF OBJECT_ID('tempdb..#Visits') IS NOT NULL DROP TABLE #Visits

SELECT * INTO #Appointments FROM (VALUES
    (6, 'Fred', CAST('2020-07-22' as datetime)),
    (6, 'Fred', '2020-09-22'),
    (6, 'Fred', '2015-05-14'),
    (7, 'Barney', '2020-07-25'),
    (8, 'Wilma', '2020-08-15'),
    (9, 'Betty', '2020-09-10')
) S(patient_id, patient_name, appt_date)

SELECT * INTO #Visits FROM (VALUES
    (6, CAST('2020-07-23' as datetime), 'BP: 51, HR: 56', 1),
    (6, '2020-09-25', 'BP: 52, HR: 56', 2),
    (6, '2015-05-22', 'BP: 53, HR: 56', 3),
    (7, '2020-07-27', 'BP: 54, HR: 56', 4),
    (7, '2020-08-22', 'BP: 55, HR: 56', 5),
    (8, '2020-08-22', 'BP: 56, HR: 56', 6),
    (8, '2020-09-02', 'BP: 57, HR: 56', 7),
    (8, '2020-10-31', 'BP: 58, HR: 56', 8),
    (9, '2020-09-11', 'BP: 59, HR: 56', 9),
    (9, '2020-10-21', 'BP: 60, HR: 56', 10)
) S(patient_id, vitals_date, vitals_info, visit_id)

SELECT * FROM #Appointments
SELECT * FROM #Visits

SELECT
    A.*,
    V.*
FROM
    #Appointments A
    LEFT JOIN #Visits V ON
        V.visit_id = (
            SELECT TOP 1 V2.visit_id FROM #Visits V2 WHERE V2.patient_id = A.patient_id AND V2.vitals_date >= A.appt_date ORDER BY V2.vitals_date
        )

-- 更新以更改日期格式