DB2 SQL

统计血糖情况最大记录、最小记录、总数
with cte (PATIENT_ID ,MEASURE_DATE,MONITOR_TIME_CODE,MONITOR_TIME,MEASURE_VALUE,UNITE,rownum)
as
(    
select  
      t.PATIENT_ID,
m.MEASURE_DATE,m.MONITOR_TIME_CODE,m.MONITOR_TIME,m.MEASURE_VALUE,m.UNITE, row_number() 
over(partition by t.PATIENT_ID order by m.MEASURE_DATE desc ) st1
from HR_PATIENT_INFO t 
left join 
MD_BLOOD_SUGAR m 
on t.PATIENT_ID = m.PATIENT_ID
where t.PATIENT_ID in (select PATIENT_ID from DR_BOTH_ATTENTION  where DOCTOR_ID='test_doctor_user'  and PATIENT_ATTENTION='1'))
  
select a.*, hightValue,lowerValue,recordCount from 
(
    (select PATIENT_ID,NAME,SEX_CODE,SEX,BIRTHDAY,PHOTO_URL,TEL_MOBILE
    from HR_PATIENT_INFO  where PATIENT_ID in (select PATIENT_ID from DR_BOTH_ATTENTION  where DOCTOR_ID='test_doctor_user  and PATIENT_ATTENTION='1'')) a
    
    left join
   
    (select max(MEASURE_VALUE) AS lowerValue,MIN(MEASURE_VALUE) as hightValue,count(PATIENT_ID) as recordCount,PATIENT_ID from cte group by PATIENT_ID) b
   
    on a.PATIENT_ID=b.PATIENT_ID

)

效果等同与上面

select 
  info.PATIENT_ID,NAME,SEX_CODE,SEX,BIRTHDAY,PHOTO_URL,TEL_MOBILE,
  summ.hightValue,summ.lowerValue,
  COALESCE(summ.recordCount,0) recordCount
from HR_PATIENT_INFO info
    left join (
      select 
        m.patient_id,
        max(m.MEASURE_VALUE) as hightValue,
        min(m.MEASURE_VALUE) as lowerValue,
        count(m.PATIENT_ID) as recordCount
      from MD_BLOOD_SUGAR m 
      where m.is_del='0'
      and m.patient_id in (
        select patient_id from DR_BOTH_ATTENTION  where DOCTOR_ID='test_doctor_user' and PATIENT_ATTENTION='1'
       )
       --统计最后一个月
      --and m.MEASURE_DATE >= '20150401' and m.MEASURE_DATE < '20150501' 
      group by m.patient_id
    ) summ
    on info.PATIENT_ID = summ.PATIENT_ID
where info.patient_id in (
  select patient_id from DR_BOTH_ATTENTION  where DOCTOR_ID='test_doctor_user' and PATIENT_ATTENTION='1'
 )
order by COALESCE(summ.recordCount,0) desc

统计血糖最近的值

   select PATIENT_ID,NAME,SEX_CODE,SEX,BIRTHDAY,PHOTO_URL,TEL_MOBILE,
    MEASURE_DATE,MONITOR_TIME_CODE,MONITOR_TIME,MEASURE_VALUE,UNITE
 from (
select t.PATIENT_ID,t.NAME,t.SEX_CODE,t.SEX,t.BIRTHDAY,t.PHOTO_URL,t.TEL_MOBILE,
m.MEASURE_DATE,m.MONITOR_TIME_CODE,m.MONITOR_TIME,m.MEASURE_VALUE,m.UNITE, row_number() 
over(partition by t.PATIENT_ID order by m.MEASURE_DATE desc ) st1
from HR_PATIENT_INFO t 
left join 
MD_BLOOD_SUGAR m 
on t.PATIENT_ID = m.PATIENT_ID
where t.PATIENT_ID in 
(select PATIENT_ID from DR_BOTH_ATTENTION 
where DOCTOR_ID=#{doctorId,jdbcType=VARCHAR})
) as r
where r.st1='1'