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

2016-06-17 09:10:07 473 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

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

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

再就是效率很低

100条shshu数据超过1.3秒，不包含这些联合条件只需0.232秒
求大神给优化，
我自己试了几个方式都不行，没想通啊

...全文

STM32F103RC学习（一） STM32F103RC学习（一）阅读详情

wangkuang5

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

Ginnnnnnnn 2016-06-21

打赏
举报

或者你使用 temporary table 先查询出100个病人，然后更新列

wangkuang5 2016-06-21

打赏
举报

不想搞复杂了，就用这种垃圾死办法做的分页模糊查询，目前100条结果需要5秒左右

wangkuang5 2016-06-21

打赏
举报



            string search_sqlcmd = @"SELECT  ""client_SeekhapEHRSN"" 心安EHR编号,
	client_name 姓名,
	client_sex 性别,
	client_gidcardno 身份证,
	client_cellno 手机号码,
	client_blepdoctor 责任经理,
	client_blephelper 健康管家,
	 
	 
	(
		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_FollowTime"" = (
				SELECT
					MAX (""fl_FollowTime"")
				FROM
					seekhap_ehr_followup
				WHERE
					ehr_seekhapsn = ""client_SeekhapEHRSN""
			)
	) 最近随访时间,
	 (
		SELECT
			""fl_FollowRecorder""

        FROM
            seekhap_ehr_followup

        WHERE

            ""fl_FollowTime"" = (
                SELECT

                    MAX(""fl_FollowTime"")

                FROM
                    seekhap_ehr_followup

                WHERE
                    ehr_seekhapsn = ""client_SeekhapEHRSN""
			)
	)  最近随访者,
	(
		SELECT
			""fl_FollowMethod""
		FROM
			seekhap_ehr_followup
		WHERE
			""fl_FollowTime"" = (
				SELECT
					MAX (""fl_FollowTime"")
				FROM
					seekhap_ehr_followup
				WHERE
					ehr_seekhapsn = ""client_SeekhapEHRSN""
			)
	) 最近随访方式,
	(
		SELECT
			COUNT (""fl_FollowCount"")
		FROM
			seekhap_ehr_followup
		WHERE
			ehr_seekhapsn = ""client_SeekhapEHRSN"" --)
	) 随访次数 
,client_doccreatedate 档案登记日期,
	client_membershiplevel 会员等级,
	client_type 会员类型,
	client_level 客户等级,
	client_sourcefrom 业务来源,
	client_blpbiz 归属合作伙伴
    ,id 主键编号               
FROM seekhap_ehr_client_baseinfo where (client_available='是')";

         string searchtxtcdn=  string.Format(@"  (
""client_SeekhapEHRSN"" like '%{0}%'  or
client_name  like '%{0}%'  or
client_sex  like '%{0}%'  or
client_gidcardno  like '%{0}%'  or
client_cellno  like '%{0}%'  or
client_blepdoctor  like '%{0}%'  or
client_blephelper  like '%{0}%'  or
----client_doccreatedate  like '%{0}%'  or
client_membershiplevel  like '%{0}%'  or
client_type  like '%{0}%'  or
client_level  like '%{0}%'  or
client_sourcefrom  like '%{0}%'  or
client_blpbiz  like '%{0}%'  or
client_volk  like '%{0}%'  or
----client_birth  like '%{0}%'  or
client_homeaddress  like '%{0}%'  or
client_liveaddress  like '%{0}%'  or
client_jobunit   like '%{0}%'  or
client_jobduty  like '%{0}%'  or
client_salary  like '%{0}%'  or
client_nlt  like '%{0}%'  or
client_origin  like '%{0}%'  or
client_edu  like '%{0}%'  or
client_marriage  like '%{0}%'  or
client_ghemrno  like '%{0}%'  or
client_contactperson  like '%{0}%'  or
client_contactphone  like '%{0}%'  or
client_contactrelation  like '%{0}%'  or
client_contactemail   like '%{0}%'  or
client_contactweichat  like '%{0}%'  or
client_contactqq  like '%{0}%'  or
client_contactalipay  like '%{0}%'
)
", this.tc_ClientSearch.Text);

            if (!string.IsNullOrEmpty(this.tc_ClientSearch.Text.Trim ())  )
            {
                   //全表模糊搜索  搜索算法没做好**********               
                search_sqlcmd += " AND  " + searchtxtcdn;
                setcount_sql += " WHERE  " + searchtxtcdn;
            }

          

            search_sqlcmd += string.Format(@"  ORDER BY client_doccreatedate   DESC  
limit 100 offset ({0}-1)*100 ;", tc_nudPageSize.Value);

wangkuang5 2016-06-21

打赏
举报

postgresql 做的，sql大致通用自己为了图快，又是个简单易用的小程序，就没想那么多范式规范，导致查询统计要在一个表上多次扫描

卖水果的net 2016-06-20

打赏
举报

引用 8 楼 adu_21454 的回复:

不清楚你的是sqlserver还是oracle；之前有过类型的经验，不过我当时是oracle的数据效果非常明显；考虑使用函数，进行返回，根据seekhap_ehr_client_baseinfo的主键client_SeekhapEHRSN，被查询的的seekhap_emr_outpatient和表字段如‘emr_DTDorctor’三个条件进行查询把查询的结果返回，估计可以提高查询的效率；目前主要是多次对表进行过滤检索导致效率底下使用函数后可以减少对查询的多次解析，从而提高查询效率；

最后有一个 --limit 100 offset (1-1)*50 ; 应该是 mysql

adu_21454 2016-06-20

打赏
举报

不清楚你的是sqlserver还是oracle；之前有过类型的经验，不过我当时是oracle的数据效果非常明显；考虑使用函数，进行返回，根据seekhap_ehr_client_baseinfo的主键client_SeekhapEHRSN，被查询的的seekhap_emr_outpatient和表字段如‘emr_DTDorctor’三个条件进行查询把查询的结果返回，估计可以提高查询的效率；目前主要是多次对表进行过滤检索导致效率底下使用函数后可以减少对查询的多次解析，从而提高查询效率；

wangkuang5 2016-06-20

打赏
举报

正答者可以微信红包

wangkuang5 2016-06-20

打赏
举报

引用 4 楼 wmxcn2000 的回复:

多次对 seekhap_emr_outpatient 进行扫描，会影响效率的；

是的，问题就是这，我多次扫描两个表得这个列表结果，还有谁有高招，如果用存储过程，也行，还得实现分页和对seekhap_ehr_client_baseinfo 的全字段模糊like查询

wangkuang5 2016-06-20

打赏
举报


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"

只得到seekhap_ehr_followup 的有效8列数据，仍然需要12秒我要的效果是3100条，每个人不管有没有经历门诊或是随访，都要显示还有人能理解这个查询吗哪怕是做分页能实现接近500毫秒内的高效查询

卖水果的net 2016-06-20

打赏
举报

多次对 seekhap_emr_outpatient 进行扫描，会影响效率的；

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条数据，列表正确

shiyiwan 2016-06-18

打赏
举报

left join得不到正确的列很正常啊，因为你的限制条件写掉了一个比如

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

这个只查了最近一次就诊时的时间，但是这个时间不一定是该病人的。需要加上 ehr_seekhapsn ="client_SeekhapEHRSN" 变为 (SELECT "emr_DTDorctor" FROM seekhap_emr_outpatient WHERE ehr_seekhapsn ="client_SeekhapEHRSN" AND "emr_RecordTime" = (SELECT MAX("emr_RecordTime" ) FROM seekhap_emr_outpatient where ehr_seekhapsn ="client_SeekhapEHRSN")) 接诊医生,

唐诗三百首 2016-06-18

打赏
举报

建议把select里的8个子查询(select..from..)修改为与seekhap_ehr_client_baseinfo表left join的写法..

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

相关推荐