(SELECT "emr_DTDorctor" FROM seekhap_emr_outpatient WHERE "emr_RecordTime" =

(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 接诊医生,

SELECT  "client_SeekhapEHRSN" 心安EHR编号,

client_name 姓名, client_sex 性别,client_gidcardno 身份证, client_cellno 手机号码, client_blepdoctor 责任经理,client_blephelper 健康管家,



(SELECT "emr_DTDorctor" FROM seekhap_emr_outpatient WHERE "emr_RecordTime" =(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 接诊医生,

(SELECT "emr_sampleManager" FROM seekhap_emr_outpatient WHERE "emr_RecordTime" =(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 基因解码业务经理,

(SELECT "emr_RecordTime" FROM seekhap_emr_outpatient WHERE "emr_RecordTime" =(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 接诊记录日期,

(SELECT  COUNT("emr_RecordTime") FROM seekhap_emr_outpatient WHERE   "emr_RecordTime" =(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 就诊记录次数 ,

               

(Select   "fl_FollowTime" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select MAX("fl_FollowTime") FROM seekhap_ehr_followup  where ehr_seekhapsn= "client_SeekhapEHRSN")  )   随访时间 ,               

(Select   "fl_FollowRecorder" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select MAX("fl_FollowTime") FROM seekhap_ehr_followup  where ehr_seekhapsn= "client_SeekhapEHRSN")  )  "随访者/基因经理",                 

 (Select   "fl_FollowMethod" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select MAX("fl_FollowTime") FROM seekhap_ehr_followup  where ehr_seekhapsn= "client_SeekhapEHRSN")  )  随访方式,   

 (Select  COUNT("fl_FollowCount") FROM seekhap_ehr_followup where "fl_FollowCount" =(Select MAX("fl_FollowTime") FROM seekhap_ehr_followup  where ehr_seekhapsn= "client_SeekhapEHRSN")  )  随访次数,  



client_doccreatedate 档案登记日期, client_membershiplevel 会员等级, client_type 会员类型, client_level 客户等级, client_sourcefrom 业务来源, client_blpbiz 归属合作伙伴, 

client_volk 民族, client_birth 出生, client_homeaddress 住址, client_liveaddress 现住址,client_jobunit 工作单位, client_jobduty 职务, client_salary 收入水平, client_nlt 常住类型, client_origin 籍贯, client_edu 学历, client_marriage 婚姻状况, client_ghemrno 国家健康档案编码, 

client_contactperson 联系人,  client_contactphone 联系电话, client_contactrelation 联系人关系, client_contactemail  电子邮件, client_contactweichat 微信,client_contactqq QQ, client_contactalipay 支付宝,

client_available 有效, id 主键编号                          

FROM seekhap_ehr_client_baseinfo where (client_available='是')  ORDER BY client_doccreatedate   DESC  

--limit 100 offset (1-1)*50 ;

SELECT  "client_SeekhapEHRSN" 心安EHR编号,

client_name 姓名, client_sex 性别,client_gidcardno 身份证, client_cellno 手机号码, client_blepdoctor 责任经理,client_blephelper 健康管家 



  ,aa."emr_DTDorctor"  

 , aa."emr_sampleManager"  

, aa."emr_RecordTime" 

  -- COUNT("emr_RecordTime") FROM seekhap_emr_outpatient WHERE   "emr_RecordTime" =(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 就诊记录次数 ,

             

   ,   ac."fl_FollowTime"   

  ,"fl_FollowRecorder"  

  , "fl_FollowMethod"  

 ,COUNT("fl_FollowCount")  



FROM   seekhap_ehr_client_baseinfo LEFT OUTER   JOIN seekhap_emr_outpatient aa   ON aa.ehr_seekhapsn= "client_SeekhapEHRSN"

LEFT OUTER   JOIN seekhap_ehr_followup ac   ON ac.ehr_seekhapsn= "client_SeekhapEHRSN"

 

 WHERE   

aa."emr_RecordTime" =(SELECT MAX(ad."emr_RecordTime" ) FROM seekhap_emr_outpatient ad where ad.ehr_seekhapsn =aa. ehr_seekhapsn)

AND



ac."fl_FollowTime"  =(SELECT MAX( ab."fl_FollowTime")  FROM seekhap_ehr_followup ab  WHERE ab.ehr_seekhapsn=ac.ehr_seekhapsn)

GROUP BY "client_SeekhapEHRSN"  ,

client_name  , client_sex  ,client_gidcardno  , client_cellno  , client_blepdoctor  ,client_blephelper   

 ,aa."emr_DTDorctor"  

 , aa."emr_sampleManager"  

, aa."emr_RecordTime" 

 ,   ac."fl_FollowTime"   

  ,"fl_FollowRecorder"  

  , "fl_FollowMethod"