mysql - 如何使用 SQL 中上一行的值

Question

以下 SQL 查询给出如下所示的输出:

SELECT claim_id,person_id,service_date,readmission_in_60_days 
FROM xyz WHERE person_id IN("00026cb6","021fb6bd") GROUP BY person_id,service_date
ORDER BY service_date ASC



 claim_id   person_id   service_date  readmission_in_60_days
    8       021fb6bd    2015-01-01             NULL
    304     021fb6bd    2015-01-05             NULL
    296     021fb6bd    2015-01-06             NULL
    11888   021fb6bd    2015-01-07             NULL
    23928   021fb6bd    2015-01-08             NULL
    265442  00026cb6    2016-07-20             NULL
    518062  00026cb6    2016-09-28             NULL
    579739  00026cb6    2016-10-02             NULL

在这里，我想检查特定 person_id 的 n 和 n-1 行 service_date。如果 n 和 n-1 service_date 之间的差异小于 60 天，则 n 行列 readmission_in_60_days="TRUE"否则为"FALSE"。

因此 SQL 查询的期望输出应该是:

claim_id   person_id   service_date  readmission_in_60_days
    8       021fb6bd    2015-01-01             FALSE
    304     021fb6bd    2015-01-05             TRUE
    296     021fb6bd    2015-01-06             TRUE
    11888   021fb6bd    2015-01-07             TRUE
    23928   021fb6bd    2015-01-08             TRUE
    265442  00026cb6    2016-07-20             FALSE
    518062  00026cb6    2016-09-28             FALSE
    579739  00026cb6    2016-10-02             TRUE

Answer 1

请试试这个。

Declare @tblTest as table
(
  claim_id   int,
  person_id  varchar(50),
  service_date  datetime,
  readmission_in_60_days varchar(10) null
)

insert into @tblTest values (304   , '021fb6bd','2015-01-05', NULL)
insert into @tblTest values (296   , '021fb6bd','2015-01-06', NULL)
insert into @tblTest values (11888 , '021fb6bd','2015-01-07', NULL)
insert into @tblTest values (23928 , '021fb6bd','2015-01-08', NULL)
insert into @tblTest values (265442, '00026cb6','2016-07-20', NULL)
insert into @tblTest values (518062, '00026cb6','2016-09-28', NULL)
insert into @tblTest values (579739, '00026cb6','2016-10-02', NULL)

SELECT t.claim_id, 
    t.person_id, 
    t.service_date,
    t.PreviousDate,
    DATEDIFF(DAY, t.service_date, t.PreviousDate) AS TotalDiffDays,
    CASE WHEN ISNULL(t.PreviousDate,'') = '' THEN 'FALSE' 
         WHEN DATEDIFF(DAY, t.PreviousDate, t.service_date) > 60 THEN 'FALSE'
         WHEN DATEDIFF(DAY, t.PreviousDate, t.service_date) < 60 THEN 'TRUE' 
         END AS readmission_in_60_days
FROM
    (SELECT 
        claim_id, 
        person_id, 
        service_date,
        LAG(service_date, 1, null) OVER (ORDER BY claim_id ASC) AS PreviousDate from @tblTest
    ) t