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

wangkuang5 2016-06-17 09:10:07 
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秒
求大神给优化，
我自己试了几个方式都不行，没想通啊
...全文
392 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
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的写法..
CVX使用手册
介绍了Matlab上凸优化包CVX的概况以及使用说明，指导编写凸优化程序
最全的HFSS 仿真实例模型（60个）下载
最全的HFSS 仿真实例模型（60个）下载
探究SLS求解器与预处理器的优化策略
本文探讨了约束程序设计中的SLS求解器性能优化，重点分析了不同预处理器对求解器的改进效果，并讨论了多个预处理器联合使用时的潜在性能提升。通过具体案例，展示了在结构化问题上预处理器选择的重要性，以及在随机3-SAT问题上不同求解器对预处理器的偏好。同时，本文也对COMET系统进行了介绍，探讨了其面向对象编程语言如何支持高级局部搜索，并提供了丰富的建模与搜索环境。
MATLAB优化工具箱的实用指南-解决工程问题的前沿技术
MATLAB的优化工具箱包含多种算法和函数，旨在帮助用户解决线性、非线性、整数和约束优化问题。线性和非线性优化整数和混合整数优化约束优化全局优化在MATLAB中，优化问题通常可以表达为以下形式：其中，(f(x)) 是目标函数，(g_i(x)) 和 (h_j(x)) 分别是不等式约束和等式约束。在某些复杂应用中，用户可能需要定义自定义目标函数。例如，用户可以在目标函数中嵌入其他计算，或者从文件中读取数据进行优化。% 假设从某个文件中读取数据f = ... % 自定义的目标计算。
商业决策优化求解器软件，继芯片与操作系统之后的国之重器
日前，来自中国自主研发的两款商业决策优化求解器软件成功登顶国际权威数学决策软件测评排行榜，杉数科技拔得头筹，阿里紧随其后，引发了国人对于决策优化求解器的关注。此前，由于国际竞争，芯片和操作系统已经成为中国要举全国之力突破的“卡脖子”工程，而Matlab等商业数学及科学仿真计算软件被禁用，让商业决策优化求解器软件成为了芯片与操作系统之后必须要掌握自主知识产权的国之重器。 商业决策优化求解器软件主要以工程软件的形式，把数学优化算法用于公共机构与商业大规模复杂问题的优化求解，例如输电网络规划与发电机组组合优..
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章