sql - 在第一个最高分之后找到最低分

Question

我正在寻找 SQL 查询。

表 1:源表

表2:结果表

附上病历表。在这里，我想找到每个患者的最高分数、最高分数日期、最低分数和最低分数日期。棘手的部分是，如果患者在两个不同的日期(10/5/2018 和 8/4/2020)有相同的最高分(这里是 9)，我们需要取最早的高分(10/5/2018) .同样，如果患者在两个不同的日期(3/1/2019 和 4/2/2020)有相同的最低分数(此处为 6)，我们应该取最近的最低分数(4/2/2020)

表 1:源表包含单个患者的所有分数。患者 ID 是该表的主键。我想要一个类似于表 2 的结果表。

我试过了

SELECT distinct
    pat.PAT_NAME 'Patient Name'
    , pat.PAT_ID
    , CAST(pat.CONTACT_DATE AS DATE) 'Service Date'
    , pat.MEAS_VALUE 'Score'
    , [Row Number] = rank() OVER (PARTITION BY pat.PAT_ID ORDER BY CAST(pat.MEAS_VALUE AS int) DESC, CONTACT_DATE asc)
FROM Patient pat            
WHERE pat.PAT_ID = 'A112233'

但是这段代码可以显示最高或最低分数。但它并不能满足我所有的要求。

Answer 1

如果表 t 的列为:PatientName、PatientID、ServiceDate、Score。像这样:

;with high_low_cte(PatientID, high_rn, low_rn) as(
    select
      PatientID,
      row_number() over (partition by PatientID order by Score, ServiceDate asc),
      row_number() over (partition by PatientID order by Score, ServiceDate desc)
    from
      t)
select * from high_low_cte where high_rn=1 and low_rn=1;

问题更新后:

;with high_low_cte([Patient Name], PAT_ID, [Service Date], Score, high_rn, low_rn) as (
    SELECT distinct
      pat.PAT_NAME 'Patient Name'
      ,pat.PAT_ID
      ,CAST(pat.CONTACT_DATE AS DATE) 'Service Date'
      ,pat.MEAS_VALUE 'Score'
      ,high_rn=row_number() OVER (PARTITION BY pat.PAT_ID ORDER BY CAST(pat.MEAS_VALUE AS int) DESC, CONTACT_DATE asc) 
      ,low_rn=row_number() OVER (PARTITION BY pat.PAT_ID ORDER BY CAST(pat.MEAS_VALUE AS int) asc, CONTACT_DATE asc) 
    FROM  
      Patient pat                                 
    WHERE                  
    pat.PAT_ID='A112233')
select hld1.*, hld2.Score [Low_Score], hld2.[Service Date] [Low Service Date]
from high_low_cte hld1 join high_low_cte hld2 on hld1.PAT_ID=hld2.PAT_ID
where
  hld1.high_rn=1
  and hld2.low_rn=1;