我想你可以用这个。使用lag函数检查以前的记录。我在sql server中运行了它。此外，请使用代码进行观察，因为拼写可能会导致问题，例如肯定而不是肯定。

SELECT m.patient_id, m.med , m.given_day
FROM medications m
JOIN (SELECT patient_id, 
       CASE WHEN ob = 'negative'
             AND lag(ob) OVER (partition by patient_id order by l.ob_day) = 'postive'
            THEN 1
            ELSE 0 
            END as qualify
      FROM lab_obs l) l ON l.patient_id = m.patient_id AND l.qualify = 1

使用postgresql进行了尝试（不确定您使用的是哪种rdbms）。为此，您需要窗口函数和CTE：

WITH lagged_obs AS (
    SELECT patient_id,
           ob_day,
           ob,
           LAG(ob) OVER (PARTITION BY patient_id ORDER BY ob_day) AS lagged_ob
    FROM lab_obs
)
, patient_status_change AS (
    -- Look for status changes, useful for consecutive tests with same result
    SELECT patient_id,
           ob_day AS ob_change_day,
           ob
    FROM lagged_obs
    WHERE ob IS DISTINCT FROM lagged_ob
)
, patient_infected_periods AS (
    -- Build time periods
    SELECT patient_id,
           ob_change_day AS start_date,
           LEAD(ob_change_day)
               OVER (PARTITION BY patient_id ORDER BY ob_change_day) AS end_date,
           ob AS period_status
    FROM patient_status_change
)
SELECT pip.patient_id, m.given_day, m.med
FROM patient_infected_periods pip
    INNER JOIN medications m
    ON pip.patient_id=m.patient_id AND pip.start_date <= m.given_day AND m.given_day < pip.end_date
WHERE pip.period_status='postive'

这有点棘手。如果有人只有一次检测呈阳性，那么：

select m.*
from medications m
where m.given_day >= (select min(l.ob_day)
                      from lab_obs l
                      where l.patient_id = m.patient_id and l.ob = 'positive'
                     ) and
      m.given_day < (select min(l.ob_day)
                     from (select l.*, min(case when ob = 'positive' then ob_day end) as pos_ob_day
                           from lab_obs l
                          ) l
                     where l.patient_id = m.patient_id and
                           l.ob = 'negative' and
                           l.ob_date > pos_ob_day
                        );

这适用于消极和积极-消极情景。
如果有人可以来回切换，那么我建议使用 lag() 这样地：

select m.*, l.*
from medications m join
     (select l.*,
             lead(ob_day) over (partition by patient_id order by ob_day) as next_ob_day
      from (select l.*,
                   lag(ob) over (partition by patient_id order by ob_day) as prev_ob
           from lab_obs l
          ) l
      where prev_ob is null or prev_ob <> ob
     ) l
     on m.patient_id = l.patient_id and
        m.given_date >= l.ob_day and
        m.given_date < l.next_ob_day and
        l.ob = 'positive';

这个 l 子查询确定某人何时第一次测试为阳性，然后使用不同的测试值确定下一个测试日期。