联合查询优化的高级问题,急求解

wangkuang5 2016-06-17 07:16:31

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 ehr_seekhapsn ="client_SeekhapEHRSN") 就诊记录次数 ,

(Select "fl_FollowCount" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select max("fl_FollowCount") FROM seekhap_ehr_followup where ehr_seekhapsn= "client_SeekhapEHRSN") ) ,
(Select "fl_FollowTime" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select max("fl_FollowCount") FROM seekhap_ehr_followup where ehr_seekhapsn= "client_SeekhapEHRSN") ) ,
(Select "fl_FollowRecorder" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select max("fl_FollowCount") FROM seekhap_ehr_followup where ehr_seekhapsn= "client_SeekhapEHRSN") ) ,
(Select "fl_FollowMethod" FROM seekhap_ehr_followup where "fl_FollowCount" =(Select max("fl_FollowCount") FROM seekhap_ehr_followup where ehr_seekhapsn= "client_SeekhapEHRSN") )

FROM seekhap_ehr_client_baseinfo

LIMIT 100 OFFSET 0
...全文
165 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
wangkuang5 2016-06-20
  • 打赏
  • 举报
回复


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 ;

18秒 3100行,显示每个人是否有门诊或是随访次数,且最后一次门诊或 随访时间,人的数据 求效率
wangkuang5 2016-06-20
  • 打赏
  • 举报
回复
问题同上 有木有大师啊
wangkuang5 2016-06-20
  • 打赏
  • 举报
回复
http://bbs.csdn.net/topics/391969245?page=1#post-401268919 高级SQL优化联合查询,求解答
trainee 2016-06-18
  • 打赏
  • 举报
回复
搞不清楚,怎么会有这么奇葩的写法。
wangkuang5 2016-06-17
  • 打赏
  • 举报
回复
再就是效率很低 100条shshu数据超过1.3秒,不包含这些联合条件只需0.232秒 求大神给优化, 我自己试了几个方式都不行,没想通啊
wangkuang5 2016-06-17
  • 打赏
  • 举报
回复
where "fl_FollowCount" =(Select max("fl_FollowCount") FROM seekhap_ehr_followup where ehr_seekhapsn= "client_SeekhapEHRSN") "emr_RecordTime" =(SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN") 这两个条件在left out join 得不到正确的列,有类似 随访/记录次数 时间 1 2016-02-12 2 2016-03-15 3 2016-04-23

972

社区成员

发帖
与我相关
我的任务
社区描述
PostgreSQL相关内容讨论
sql数据库数据库架构 技术论坛(原bbs)
社区管理员
  • PostgreSQL社区
  • yang_z_1
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧