请大家帮我优化这条SQl语句

[code=SQL]rnSELECT * FROM T_Resume WHERE rn(OpenLevel=1 and Deleted =0 and WorkState='3536') and (NowArea='125') and (WorkKind='2') and (Degree='3') rnand (ExpectSalaryType ='1000') and (MajorCategory ='220') and (Experience ='4') rnand (RefreshTime >'2008-4-29') and (UserSex =1) and (Birthday between 1978-7-28and 1973-7-28) rnand ( charindex('125',SelectedArea) > 0 or charindex('16020',SelectedArea) > 0 ) and ( charindex('1009999',rnSelectedCategory)>0 ) and ( charindex('1107000',SelectedIndustry)>0 ) and ( rn cast(WorkFunction as nvarchar)+cast(SelfValue as nvarchar)+cast(SkillSpec as nvarchar)like '%cnc%') rn[/code]rn我用的是SQl2005数据库，只<em>条</em>查询语句查询的效率很低，严重影响了查询，请大家<em>帮</em>忙<em>帮</em>我<em>优化</em>一下rn先谢谢各位了

如何优化这条sql

select count(1) as cntrnfrom STCMOVTBL inner productmst on productmst.productcd = STCMOVTBL.productcd rn and productmst.trademarkcd in ('10','11') rnwhere UPDATETIME >= @ChkStartTime rn and UPDATETIME < @ChkEndTime rn and REDBLKNO = '0'rn and MOVADMQNTY <> 0rn and [color=#FF0000](MOVFRMCD in (select distinct placecd from customermst where SALEPATN = '2') or rn MOVTOCD in (select distinct placecd from customermst where SALEPATN = '2') )[/color]rn由于数据量比较大，所以希望<em>优化</em>下这<em>条</em>语句，领导说红色的可以<em>优化</em>，希望大家<em>帮</em><em>帮</em>忙。

如何优化这条SQL

如何<em>优化</em>SQL: select * from t where id in (1,2,3) order by s desc;rnrnverycd.cd的面试题。

这条SQL怎么优化

[code=SQL]rnSELECT cls.*, yplan.*rnFROM Mpt_CostItem cls INNER JOINrn Mpt_YearPlan yplan ON cls.CostItem_ID = yplan.YearPlan_ItemIDrnGROUP BY yplan.YearPlan_ItemIDrnORDER BY yplan.YearPlan_IDrn[/code]rn上面的语运行肯定是通不过的如果改成这样rn[code=SQL]rnSELECT max(cls.字段), max(yplan.字段)rnFROM Mpt_CostItem cls INNER JOINrn Mpt_YearPlan yplan ON cls.CostItem_ID = yplan.YearPlan_ItemIDrnGROUP BY yplan.YearPlan_ItemIDrnORDER BY yplan.YearPlan_IDrn[/code]rn可以满足我的要求，但是用到了聚合函数，“字段”的类型是nvarchar.上面的效率是不是很差。rn如果<em>优化</em>一下

这条sql如何优化

explain select art.article_id,art.title ,art.link2 from article art , (select a.article_id from article_goods a , (select b.goods_id from article_goods b where b.article_id='133') c where a.goods_id=c.goods_id and a.article_id <> '133' group by article_id) tmp where art.article_id= tmp.article_id limit 4 rnrnrn1 PRIMARY ALL 5 rn1 PRIMARY art eq_ref PRIMARY PRIMARY 3 tmp.article_id 1 rn2 DERIVED ALL 3 Using temporary; Using filesortrn2 DERIVED a range article_id,article_id_gid article_id_gid 3 114 Using where; Using index; Using join bufferrn3 DERIVED b ref article_id,article_id_gid article_id_gid 3 5 Using indexrn

优化这条sql语句

declare @d datetimernrnset @d=getdate()rnrnselect top 100 cast((b.num*0.01/a.num)*10000 as int) as num ,a.namefromrn(rnselect count(id) as num ,name from forum where msglevel=1 and (preref=0 or preref=1) group by namern)arn,rn(rnselect count(id) as num ,namefrom forum where msglevel=1 and ( preref=1) group by namern)brnrnwhere a.name=b.name order by num descrnrnselect [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())rnrnrn在100000<em>条</em>数据下,用时1100毫秒左右,能否<em>帮</em>忙<em>优化</em>一下,谢谢

怎么优化这条SQL？

下面的SQL怎么<em>优化</em>？rn[code=SQL]rnSELECT a.managecom 机构代码,rn a.sx 销售渠道,rn COUNT(distinct a.appno) 申请人数量,rn SUM(functiona( b.pno, rn b.riskcode, rn b.paymoney, rn b.dutycode rn )) 累计标准保费rn FROM table1 a, table2 brn WHERE a.pno = b.pnorn AND a.sx IN (1, 4)rn AND b.paymode = 1rn AND a.cdate ))rn GROUP BY a.managecom, rn a.sxrn[/code]rn

谁来帮我优化下这条语句

超市销售一天30多万<em>条</em>流水小票销售，查询一次3-4分钟。rn机构表 tb_store rn销售商品流水表 tb_o_sgrn这个是查询日期内，销售≥66的笔数，≥66的总金额，只取超市1XXX开头的是超市，不要烟酒课1100烟酒课，不要烟酒店1101烟酒店，c_store_id % 2<>0 排除机构2开头的是烟酒店rnrn[code=<em>sql</em>]select ts.c_name as 店名,b.笔数,b.总额 fromrn (select a.c_store_id,count(a.c_id) as 笔数,sum(zk) as 总额 from rn (select c_id,c_store_id,sum(c_amount) as zk from tb_o_sg(nolock)rn where c_adno like '1___' --只取超市rn and c_adno <> '1100' --是否排除1100烟酒课rn and c_adno <> '1101' --是否排除1101清洁卫生课rn and c_store_id % 2<>0 --是否排除烟酒店rn and c_datetime>='2014-09-29 00:00:00'rn and c_datetime=66) arn group by a.c_store_id) brn left join tb_store ts on b.c_store_id=ts.c_id[/code]

请教：如何优化这条sql？

我有倆个表、一个是全部城市的表（数据量3K左右）、一个是所有ip段的表（数据量为47W左右）、rnrnrn 就是想知道一个ip是哪里的城市、 给于显示 该城市id 、名、和大区rnrnrnrn[code=SQL]select district1_.CN_ID , district1_.CN_BIG_AREA , district1_.CN_NAME from MD_IP_ADDR ipaddress0_ rn cross join MD_DISTRICT district1_ rnwhere ipaddress0_.CN_CITY_ID=district1_.CN_ID rnand INET_ATON('202.96.64.68')>=ipaddress0_.CN_IP1_LONG rnand INET_ATON('202.96.64.68')<=ipaddress0_.CN_IP2_LONG limit 1[/code]rnrnrn这<em>条</em>语句在my<em>sql</em>里查询第一次是3秒多、第二次才是0.3秒左右、rnrn所以想问下有没有<em>优化</em>该<em>sql</em>的途径？rnrn另外该语句写在java的程序里、使用基本的connection 和perparstatement 进行查询、每次都是3秒左右、rn感觉perparstatement 根本没有预编译过、rnrn如果有<em>优化</em>的方式可以告诉我下、先谢谢各位了、

如何优化这条sql语句

[code=SQL]rnSELECT /*+RULE*/class2, RIGHT, w2isborrowed FROM pdmm31a.assmstrc WHERE left='sieczKcpdmtstpdmm31a-bJ1'rn[/code]rn我执行这句<em>sql</em>语句时，执行计划如下：rn[code=SQL]rnPlan rnSELECT STATEMENT HINT: RULE rn2 TABLE ACCESS BY INDEX ROWID PDMM31A.ASSMSTRC rn1 INDEX RANGE SCAN NON-UNIQUE PDMM31A.ASSMSTRC_RLTLEFTINDEX rn[/code]rn平均耕时在[color=#FF0000]150ms[/color]左右。rnrn上面的执行计划我看不太懂，之前我对LEFT建立了一个索引，LEFT有一定的重复性，pdmm31a.assmstrc表中有7421<em>条</em>记录rnrn我觉得这个查询执行的速度很慢，请大家<em>帮</em>我分析一下，看看有什么<em>优化</em>方案没有？rn

请优化这条SQL语句

语句如下:rnselect e.idrnfrom ext e where (day('2007-04-05 17:21:32') - day(e.add_time) =1)rnand e.clbj !='1' order by e.wfsj desc limit 0,2;rnrn加上order by后通过网闸查询MySql要50多秒,不加又不行，这个语句的意思是查询日期的前天的记录中的前两<em>条</em>，高手<em>帮</em>着<em>优化</em>一下rnrn

这条SQL还能优化吗?

select tbl1.* rnfrom tbl_rjyls_today tbl1rnwhere not exists (select key from tbl_yscc tbl2 rn where tbl1.key=tbl2.key rn and tbl2.jyrq = '20060517')rnrn功能:取出存在表tbl1中而不存在于tbl2中的记录,tbl1中6W多<em>条</em>,tbl2中60w<em>条</em>

这条sql语句如何优化?

系统：vb+access,piii 800rnz_arrange_list 记录数 496rnz_class 记录 28rnz_attend_clock 记录 1900rnrn方案一：rnSELECT aa.empl_id, aa.c_day, aa.on_duty, aa.off_duty rnfrom (SELECT a.empl_id, day(a.t_date) AS c_day, b.card_begin, b.on_duty, b.off_duty, b.card_endrnFROM z_arrange_list AS a INNER JOIN z_class AS b ON a.class_id=b.class_id) aa rnWHERE not (aa.empl_id & aa.c_day & aa.on_duty & off_duty) in rn(select card_id & c_day & on_duty & off_duty from z_attend_clock where valid_duty=true and year_mon=200407 group by card_id,c_day, on_duty,off_duty)rnrn执行时间 3分钟以上rnrn方案二：rn创建１个临时表rn 1、SELECT a.empl_id, day(a.t_date) AS c_day, b.card_begin, b.on_duty, b.off_duty, b.card_end into Tmp01 FROM z_arrange_list AS a INNER JOIN z_class AS b ON a.class_id=b.class_idrnrn 2、SELECT aa.empl_id, aa.c_day, aa.on_duty, aa.off_duty rnfrom tmp01 aa rnWHERE not (aa.empl_id & aa.c_day & aa.on_duty & off_duty) in rn(select card_id & c_day & on_duty & off_duty from z_attend_clock where valid_duty=true and year_mon=200407 group by card_id,c_day, on_duty,off_duty)rnrn*执行时间 1.1分rnrn方案三：rn创建2个临时表rn 1、SELECT a.empl_id, day(a.t_date) AS c_day, b.card_begin, b.on_duty, b.off_duty, b.card_end into Tmp01 FROM z_arrange_list AS a INNER JOIN z_class AS b ON a.class_id=b.class_idrn 2、select (card_id & c_day & on_duty & off_duty) as sect into Tmp02 from z_attend_clock where valid_duty=true and year_mon=200407 group by card_id,c_day, on_duty,off_dutyrnrn 3、SELECT aa.empl_id, aa.c_day, aa.on_duty, aa.off_duty rn from tmp01 aa rn WHERE not (aa.empl_id & aa.c_day & aa.on_duty & off_duty) in rn (select sect from tmp02)rn rn*执行时间 25秒rnrn'************************************rn感觉速度太慢,谁能给我一个好的<em>优化</em>方案rn

求优化这条SQL语句

RTrn[code=SQL]rnselect ct.ct_id,sc.scid,tea_name,cau_name,ct_time from SetCause as sc,CauseTime as ct,CauseInfo as ci,TeaAccount as ta where ct.scid = sc.scid and sc.cau_id = ci.cau_id and sc.tea_id = ta.tea_id and ci.cau_id not in (select ci.cau_id from SetCauseTime as sct,SetCause as sc,CauseTime as ct,CauseInfo as ci where sct.ct_id = ct.ct_id and ct.scid = sc.scid and sc.cau_id = ci.cau_id and sct.stu_id = @stu_id) and ci.cau_name like '%'+@causeValue+'%'rn[/code]rn可以拆分，越效率越好

【急】怎样优化这条sql

我这里有一个表TABA，有三个字段，四百多万数据，字段分别是：rn起始数字start_num，结束数字end_num,描述信息desc_info；这两个数字字段上都有唯一索引；rn然后有一个表TABB，里面有六百多万数据。这个表里有一个数字字段my_num;对应这个数字，要找出它对应的描述信息，如下：rnrnselect my_number,desc_info from taba A,tabb B where B.my_num between a.start_num and a.end_num;rnrn但是现在执行起来很慢。应该怎么<em>优化</em>呢？麻烦指点一下，谢谢各位高手~~rnrn

怎么优化这条sql语句

select code ,dele='删除',name,yznum,ddnum,unit,guige from tb_farmer_pru where tjid=1 rnunion select code=0,dele='无',name,yznum=0,ddnum=0,unit,id as guige from tb_union_guige where id not in (select code from tb_farmer_pru where tjid=1)rnrn其实tjid=变量 ，不是一定的，我暂时这么写rn由于tb_farmer_pru 表的数据量想当多。所以觉得性能可能有问题。rn我总觉得应该可以<em>优化</em>一下，但是不知道如何<em>优化</em>。rn

求这条SQL语句的优化

select username,joindate,lstlogindate,supticket,referral,onlinetimern [color=#FF0000],(select count(*) from buylog where a.id = usedid ) as sumcnt[/color] ,[color=#FF0000]isnull((select sum(gross) rn from buylog rn where a.id = usedid),0)rn as tmpgross[/color] from v_userdetail as arn where referral = @idrn order by supticket descrnrn我用SQL 2005的执行计划看了一下rn这<em>条</em>语句红色部分分别要读两次表rn为了改进，我把两<em>条</em>语句中的变量合在一起形成了如下语句rnselect username,joindate,lstlogindate,supticket,referral,onlinetimern ,(select count(*) as sumcnt,isnull(sum(gross),0) as sumgross from buylog where a.id = usedid ) from v_userdetail as arn where referral = @idrn order by supticket descrn但是该语句由于在select中包含两列，所以<em>sql</em>报错rn然后既然要包含一页，我有改写成rnselect username,joindate,lstlogindate,supticket,referral,onlinetime,sumcnt,sumgrossrnfrom v_userdetail as a inner joinrn([color=#FF0000]select count(*) as sumcnt,sum(gross) as sumgross,b.usedid as idc rnfrom buylog as b where a.id = b.usedid group by b.usedid [/color]) as crnon a.id = c.idcrnwhere referral = @idrnorder by supticket descrn这次虽然成功了，但是我在显示执行计划时看到由于红色语句没有用where导致了表扫描没用索引,这样性能反而没前面的高，请问各位还有什么好方法能把对buylog两列的统计只用一次查询就可实现，希望大家能<em>帮</em>忙，小生先谢过rn

如何优化这条SQL语句？

[code=SQL]rn如何<em>优化</em>这<em>条</em>SQL语句？rn用户打开这个报表的时间很慢，有时候根本打不来。rnrn str<em>sql</em>.Append("select a.rec_id,a.cls_num,a.emp_id,a.taken_hr,a.exam_result,")rnrn str<em>sql</em>.Append("b.dept_nam as dept_num,c.fac_num as fac_num,d.crs_nam as crs_num,h.title_nam as title_id,i.code_value as atn_status,")rnrn str<em>sql</em>.Append(" CONVERT(varchar(10), m.cls_start_date, 23) as finasst_apr_date,CONVERT(varchar(10) , m.cls_end_date, 23) as finasst_act_date,m.center_venue as remark from TRAIN_REC a ")rnrn str<em>sql</em>.Append("left join dept b on a.dept_num=b.dept_num and a.fac_num=b.fac_num left join facility c on a.fac_num=c.fac_num left join course d on a.crs_num=d.crs_num ")rnrn str<em>sql</em>.Append(" left join title h on a.title_id=h.title_id and h.fac_num=a.fac_num left join CodeTable i on a.atn_status=i.code_id and i.table_id='CrsStatus' ")rnrn str<em>sql</em>.Append("left join (select r1.cls_num,r1.cls_start_date,r1.cls_end_date,r2.center_venue from classes r1 left join train_center r2 on r1.center_id=r2.center_id ) m on a.cls_num=m.cls_num where 1=1")rnrnrn[/code]

这条SQL语句如何优化？？？

SELECT rn DISTINCT ESTPERSONAL.EST_PROJECT_PRIMARY_ID rnFROM rn ECC_EST.ECC_EST_PROJECT_PRODUCTS AS PRODUCTS, rn ECC_EST.ECC_EST_PROJECT_ESTPERSONAL AS ESTPERSONAL rn INNER JOIN (SELECT PROJECT_ID,PRODUCT_ID,MIN(LAST_UPDATE_DATE) AS LAST_UPDATE_DATE rn FROM ECC_EST.ECC_EST_PROJECT_PRODUCTS rn GROUP BY PROJECT_ID,PRODUCT_ID) AS D_PRODUCTS rn ON ESTPERSONAL.EST_PROJECT_PRIMARY_ID=D_PRODUCTS.PROJECT_ID rnWHERE rn ESTPERSONAL.EST_TYPE = 1 rn AND PRODUCTS.PROJECT_ID IN rn (36443 , 37354 , 35833 , 10848 , 35208 , 41529 , 42387 , 2388 , 10561 , 36388 , 36393 , 36442 , 36444 , 36716 , 36717 , 40198 ,41717 , 10521 , 5148 , 5229 , 37966 , 38894 , 5143 , 5144 , 5313 , 37828 , 37888 , 39220 , 39385 , 40523 , 41511 , 42471 , 42869 , 43301 , 43302 , 5128 , 10160 , 11084 , 11087 , 35830 , 35832 , 5231 , 35207 , 37414 , 39878 , 40520 , 40781 , 40945 , 40948 , 42394 , 42690 , 42691 , 43116 , 43374 , 40342 , 40792 , 42224 , 43303 , 43433 , 10120 , 38720 , 39687 , 39949) rn AND DATE(D_PRODUCTS.LAST_UPDATE_DATE) rn BETWEEN DATE('2004-05-10') AND DATE('2004-05-16') rn

如何优化这条SQL语句！

如题：rnrn[code=SQL]rnselect a.custname,a.sid,b.MappingCode,b.buyername,left(round(cast(dbo.f_StrComparenew(a.custname,b.buyername) as float)/cast(((len(b.buyername)-1)+(len(a.custname)-1)) as float),4),6) * 2.0000 as 相似百分比 rnfrom dbo_GI_Customer a,SorgZ brnwhere left(round(cast(dbo.f_StrComparenew(a.custname,b.buyername) as float)/cast(((len(b.buyername)-1)+(len(a.custname)-1)) as float),4),6) * 2.0000 =rn(select max(left(round(cast(dbo.f_StrComparenew(x.custname,y.buyername) as float)/cast(((len(y.buyername)-1)+(len(x.custname)-1)) as float),4),6) * 2.0000) from dbo_GI_Customer x,SorgZ y where y.buyername=b.buyername group by buyername)rnrn[/code]rnrnrn如何<em>优化</em>这<em>条</em>SQL语句，请高手赐教！！

这条SQL如何优化呢

select art.article_id,art.title from mcuhome_article art where article_id in (select a.article_id from mcuhome_article_goods a where goods_id in (select b.goods_id from mcuhome_article_goods b where b.article_id=59) group by article_id)rnrnrn1 PRIMARY art ALL 3 Using wherern2 DEPENDENT SUBQUERY a index article_id 3 1 Using where; Using filesortrn3 DEPENDENT SUBQUERY b index_subquery article_id,goods_id,goods_article goods_article 6 func,const 2 Using index; Using wherern

这条sql语句怎么优化?

每次都是插5000+<em>条</em>记录进去,都要等个4-5秒,请问下有什么办法可以<em>优化</em>吗?rnrn[code=csharp]rnfor (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)rn rn string str = "insert into TEimpdatas Values('" + this.label3.Text + "','" + this.dataGridView1.Rows[i].Cells[0].Value.ToString() + "','"rn + this.dataGridView1.Rows[i].Cells[1].Value.ToString() + "','"rn + this.dataGridView1.Rows[i].Cells[2].Value.ToString() + "','"rn + this.dataGridView1.Rows[i].Cells[3].Value.ToString() + "','" + DateTime.Now.ToLocalTime().ToString() + "','')";rn SqlCommand objcom = new SqlCommand(str, objcon);rn objcom.ExecuteNonQuery();rn rn[/code]

这条SQL语句如何优化。。

SELECTrn c.channel_id channelId,rn (rn SELECTrn COUNT(customer_id) sumpsacidrn FROMrn product_sub_account psa1rn LEFT JOIN customer c1 ON c1.id = psa1.customer_idrn WHERErn 1 = 1rn AND c1.id = c.idrn AND c1.reg_time = '2017-07-01 00:00:00'rnAND psa.buy_time 1

加急！！！这条sql怎么优化

select sitename,sitehost from b_sitelist where id in (select site_id from b_usersitelist where user_id='6' and isSelect='1')rnrnrn查询的时候太慢太慢了 怎么可以<em>优化</em>

帮我看看这条SQL怎样写？

rnvar ss:string;rnrnss:='abcdefghijklmnopq'rnrnselect * From Table where Field in ssrnrn这样是错的， 那应该怎样写呢？rn

帮我看看这条SQL语句.

list控件中有rn 班次1rn 班次2rn 班次3rn ……rn 班次nrn由用户选择个数 rnFor i = 0 To List1.ListCount - 1rn If List1.Selected(i) Thenrn selbanci = selbanci + "'" + List1.List(i) + "',"rn rn End Ifrn rn selbanci = Left(selbanci, Len(selbanci) - 1) '除去末尾的,号rn str<em>sql</em> = "insert into 班天 values('" & Text1 & "',rn'" & selbanci & "')"rnrn因为有单引号' 所以该语句有错，请告诉我一个可行的方法，但必须要求保存到rn数据库中的数据在 单引号 内

帮我看看这条sql语句》》》》》》》》

<em>帮</em>忙看看下面这<em>条</em>语句有什么错误。先谢啦 ^_^rnrn－－－－－－－－－－－－－－－－－－－－－－－－－rnSQLcmd="Insert into News (News_title,News_type_ID,News_reporter_ID,News_from,News_date,News_content,News_img) values"rnSQLcmd=SQLcmd &"('" & request("News_title") & "','" & request("News_type_ID") & "','" & request("News_reporter_ID") & "','" & request("News_from") & "','" & request("News_date") & "','" & request("News_content") & "','" & request("News_img") & "')"rnconn.execute(SQLcmd)

帮我看看这条SQL语句

<em>帮</em>我看看这<em>条</em>SQL语句rnDECLARE @PageNum INTrnSET @PageNum=5rnDECLARE @UserID VARCHAR(20)rnSET @UserID='ningbin'rnEXEC('select top '+@PageNum+' LogID,LogTitle,LogContent,UserName,LogMenuName,LogDateTime rn from tbl_log inner join tbl_User on tbl_User.UserID=tbl_log.UserID rn inner join tbl_LogMenu on tbl_LogMenu.LogMenuID=tbl_log.LogMenuID rn where tbl_log.UserID='+ @UserID +'rn ORDER BY LogDateTime DESC')rnrn错误 rn服务器: 消息 207，级别 16，状态 3，行 1rn列名 'ningbin' 无效。rnrn表中 是有数据的

谁能帮我优化一下这条oracle 的sql语句?谢谢大家!

[color=#FF0000]SELECT[/color] *rn [color=#FF0000]FROM[/color] tpi_pay_amount p1rn [color=#FF0000]WHERE[/color] p1.paydate =rn ([color=#FF0000]SELECT MAX [/color](p.paydate)rn [color=#FF0000]FROM[/color] tpi_pay_amount prn [color=#FF0000]WHERE[/color] p.worklist_id = '402880791ad407a2011ad40a9fea0001'rn [color=#FF0000]AND[/color] p.paydate < ([color=#FF0000]SELECT MAX [/color](p3.paydate)rn [color=#FF0000] FROM [/color]tpi_pay_amount p3))rn [color=#FF0000] AND [/color]p1.worklist_id = '402880791ad407a2011ad40a9fea0001'rnrn我想在表tpi_Pay_Amount中得到worklist_Id='402880791ad407a2011ad40a9fea0001'的记录集,并且在此基础上找到paydate第二大的那<em>条</em>记录.其中worklist_Id不是表tpi_Pay_Amount 的主键.谁能<em>帮</em>我写一<em>条</em>效率高的<em>sql</em>语句.谢谢. rn十份感谢!!rn有人说用rownum做,但是不行呀:rnSELECT * FROM (rnSELECT rownum rn, t.* FROM tpi_pay_amountrnWHERE worklist_id = '402880791ad407a2011ad40a9fea0001'rnORDER BY paydate DESC) t rnWHERE rn = 2rnrn原因:rnrn关于oracle子查询以及rownum的有关经验: rnrownum含义解释： rn 1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2， rn 　 依此类推，这个伪字段可以用于限制查询返回的总行数。 rn 2、rownum不能以任何基表的名称作为前缀。 rn rnrn 使用方法： rnSQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回记录<em>条</em>数的地方，保证不出错，如：隐式游标） rn rn rownum MONTH SELL rn --------- ------ --------- rn 1 200001 1000 rn rn SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到记录） rn rn 没有查到记录 rn rn SQL> select rownum,month,sell from sale where rownum>5; rn（由于rownum是一个总是从1开始的伪列，Oracle 认为这种<em>条</em>件不成立，查不到记录） rnoracle的rownum是不能等于2的!!!!!! rn

帮我优化一下这条oracle 的sql语句?谢谢大家!

SELECT * rn FROM tpi_pay_amount p1 rnWHERE p1.paydate = rn (SELECT MAX (p.paydate) rn FROM tpi_pay_amount p rn WHERE p.worklist_id = '402880791ad407a2011ad40a9fea0001' rn AND p.paydate < (SELECT MAX (p3.paydate) rn FROM tpi_pay_amount p3)) rn AND p1.worklist_id = '402880791ad407a2011ad40a9fea0001' rnrn我想在表tpi_Pay_Amount中得到worklist_Id='402880791ad407a2011ad40a9fea0001'的记录集,并且在此基础上找到paydate第二大的那<em>条</em>记录.其中worklist_Id不是表tpi_Pay_Amount 的主键.谁能<em>帮</em>我写一<em>条</em>效率高的<em>sql</em>语句.谢谢. rn十份感谢!! rn有人说用rownum做,但是不行呀: rnSELECT * FROM ( rnSELECT rownum rn, t.* FROM tpi_pay_amount rnWHERE worklist_id = '402880791ad407a2011ad40a9fea0001' rnORDER BY paydate DESC) t rnWHERE rn = 2 rnrn原因: rnrn关于oracle子查询以及rownum的有关经验: rnrownum含义解释： rn 1、rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2， rn 　 依此类推，这个伪字段可以用于限制查询返回的总行数。 rn 2、rownum不能以任何基表的名称作为前缀。 rn rnrn 使用方法： rnSQL> select rownum,month,sell from sale where rownum=1;（可以用在限制返回记录<em>条</em>数的地方，保证不出错，如：隐式游标） rn rn rownum MONTH SELL rn --------- ------ --------- rn 1 200001 1000 rn rn SQL> select rownum,month,sell from sale where rownum=2;（1以上都查不到记录） rn rn 没有查到记录 rn rn SQL> select rownum,month,sell from sale where rownum>5; rn（由于rownum是一个总是从1开始的伪列，Oracle 认为这种<em>条</em>件不成立，查不到记录） rnoracle的rownum是不能等于2的!!!!!!

帮我看下这条sql还有优化的可能吗？

rnrnexplain rnselect <em>sql</em>_no_cache rn 'ddd' aac3qwe,ii.item_id, ii.item_code, ii.item_cn_name, ii.item_en_name,rn year(mda.period_start_date) y, month(mda.period_end_date) m, rn mda.metadata_type, mda.metadata_code,rn mda.period_type, mda.data_id, md.data_value, mda.data_attr_code, rn mda.data_scale_code, mda.data_unit_codernfrom csf_maec.maec_data_attr mda left join csf_maec.item_info ii rn on mda.item_id=ii.item_idrnleft join csf_maec.maec_data md on mda.data_id = md.data_idrnWHERE ii.item_code ='001.001.001.002'rnAND mda.period_type = 'Y' rnand mda.data_attr_code = 'CV'rnand( rn (mda.metadata_type = 'PRICE' AND mda.metadata_code='CURRENT')rn or rn (mda.metadata_type = 'AREA' AND mda.metadata_code='PBJ')rn orrn (mda.metadata_type = 'INDUSTRY' AND mda.metadata_code='INDUSTRY_1ST')rn)rnGROUP BY mda.data_id rnHAVING count(mda.data_id)=3 ;rnrnrn[b]分析结果如下：[/b]rn'1', 'SIMPLE', 'ii', 'const', 'PRIMARY,idx_item_info_unq,idx_item_code', 'idx_item_info_unq', '62', 'const', '1', 'Using temporary; Using filesort'rn'1', 'SIMPLE', 'mda', 'ref', 'item_id', 'item_id', '4', 'const', '46339', 'Using where'rn'1', 'SIMPLE', 'md', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'csf_maec.mda.data_id', '1', ''rnrn大家<em>帮</em>我看看，还有<em>优化</em>的可能吗？现在需要0.250秒（[color=#FF0000]开发人员要求在小于0.1秒就可以了[/color]），查询最后的记录为32<em>条</em>。maec_data_attr表180W记录，maec_data表150W记录，item_info表1000多记录。rnrn

谁能帮我优化这条SQL语句 另开帖加200

关联到六个表做统计,30694<em>条</em>记录,放在存储过程中运行也要花费4秒多.头都大了,这才是四个月是数据 如果是一年...我不能想像 rnrn大侠们给我提好的建议也有分 多谢了 rnrnSELECT dbo.procedure_code.modality, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 1 THEN 1 ELSE 0 END) AS Jan, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 2 THEN 1 ELSE 0 END) AS Feb, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 3 THEN 1 ELSE 0 END) AS Mar, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 4 THEN 1 ELSE 0 END) AS Apr, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 5 THEN 1 ELSE 0 END) AS May, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 6 THEN 1 ELSE 0 END) AS Jun, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 6 THEN 1 ELSE 0 END) AS Jul, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 6 THEN 1 ELSE 0 END) AS Aug, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 9 THEN 1 ELSE 0 END) AS Sep, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 10 THEN 1 ELSE 0 END) AS Oct, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 11 THEN 1 ELSE 0 END) AS Nov, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 12 THEN 1 ELSE 0 END) AS Dec FROM dbo.resource INNER JOINrn dbo.paticipant_list INNER JOINrn dbo.activity_list ON dbo.paticipant_list.al_guid = dbo.activity_list.al_guid INNER JOINrn dbo.workflow_instance ON rn dbo.activity_list.wfi_guid = dbo.workflow_instance.wfi_guid ON rn dbo.resource.resource_id = dbo.paticipant_list.resource_id INNER JOINrn dbo.procedure_code INNER JOINrn dbo.requested_procedure ON rn dbo.procedure_code.procedure_code = dbo.requested_procedure.procedure_code ON rn dbo.workflow_instance.fo_guid = dbo.requested_procedure.fo_guid where (dbo.paticipant_list.resource_type = 'staff') AND rn (dbo.activity_list.performed_startdt IS NOT NULL) and rn (dbo.activity_list.Activity_code='CHECK-IN') and dbo.activity_list.activity_code='CHECK-IN' and dbo.procedure_code.modality in ('CR','CT','DX','MR','OT','RF','US','XA') and dbo.paticipant_list.performed_enddt between '2005-1-1 00:00:00' and '2005-12-31 23:59:59' GROUP BY Modality union select 'Total', SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 1 THEN 1 ELSE 0 END) AS Jan, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 2 THEN 1 ELSE 0 END) AS Feb, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 3 THEN 1 ELSE 0 END) AS Mar, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 4 THEN 1 ELSE 0 END) AS Apr, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 5 THEN 1 ELSE 0 END) AS May, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 6 THEN 1 ELSE 0 END) AS Jun, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 6 THEN 1 ELSE 0 END) AS Jul, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 6 THEN 1 ELSE 0 END) AS Aug, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 9 THEN 1 ELSE 0 END) AS Sep, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 10 THEN 1 ELSE 0 END) AS Oct, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 11 THEN 1 ELSE 0 END) AS Nov, SUM(CASE DATEPART(MM, dbo.paticipant_list.performed_enddt) WHEN 12 THEN 1 ELSE 0 END) AS Dec FROM dbo.resource INNER JOINrn dbo.paticipant_list INNER JOINrn dbo.activity_list ON dbo.paticipant_list.al_guid = dbo.activity_list.al_guid INNER JOINrn dbo.workflow_instance ON rn dbo.activity_list.wfi_guid = dbo.workflow_instance.wfi_guid ON rn dbo.resource.resource_id = dbo.paticipant_list.resource_id INNER JOINrn dbo.procedure_code INNER JOINrn dbo.requested_procedure ON rn dbo.procedure_code.procedure_code = dbo.requested_procedure.procedure_code ON rn dbo.workflow_instance.fo_guid = dbo.requested_procedure.fo_guid where (dbo.paticipant_list.resource_type = 'staff') AND rn (dbo.activity_list.performed_startdt IS NOT NULL) and rn (dbo.activity_list.Activity_code='CHECK-IN') and dbo.activity_list.activity_code='CHECK-IN' and dbo.procedure_code.modality in ('CR','CT','DX','MR','OT','RF','US','XA') and dbo.paticipant_list.performed_enddt between '2005-1-1 00:00:00' and '2005-12-31 23:59:59' rn

哪位大哥能帮我优化一下这条SQL语句

[b]语句：[/b]rnSELECT T3.ID,T3.REGCODE,T3.SUBCODE,T4.REGNAME,T4.SUBNAME,T4.LEVELCODE,T4.LEVELDESC,T4.CLASSCODE,T4.CLASSDESC,T4.AGECODE,T4.AGEDESCrnFROM (SELECT T1.SEQID AS ID,T2.NUM AS REGCODE,T1.NUM AS SUBCODE FROM CDI_CHILD T1,CDI_MAIN T2 WHERE T1.MAINID=T2.SEQID ORDER BY T1.SEQID) T3rnLEFT JOIN ARTICLE_CATALOG T4 ON T3.REGCODE=T4.REGCODE AND (T3.SUBCODE=T4.SUBCODE OR (T4.SUBCODE IS NULL AND T3.SUBCODE='0') OR (T4.SUBTYPE='ZH' AND T3.SUBCODE='0'))；rn[b]表结构[/b]rn[color=#FF0000]CDI_CHILD[/color]rnSEQID Number(12)rnMAINID Number(12)rnNUM VARCHAR(16)rnrn[color=#FF0000]CDI_MAIN[/color]rnSEQID Number(12)rn NUM VARCHAR(32)rnrn[color=#FF0000]ARTICLE_CATALOG[/color]rnSEQID NUMBER(12) not null,rn REGCODE VARCHAR2(32 CHAR),rn REGNAME VARCHAR2(300 CHAR),rn SUBTYPE CHAR(2 CHAR),rn SUBCODE VARCHAR2(16 CHAR),rn SUBNAME VARCHAR2(240 CHAR),rn ORIGINALNAME VARCHAR2(240 CHAR),rn LEVELCODE CHAR(2 CHAR),rn LEVELDESC VARCHAR2(60 CHAR),rn CLASSCODE VARCHAR2(2 CHAR),rn CLASSDESC VARCHAR2(60 CHAR),rn AGECODE VARCHAR2(12 CHAR),rn AGEDESC VARCHAR2(60 CHAR),rn NATIONCODE NUMBER(4),rn NATIONDESC VARCHAR2(60 CHAR),rn COLLECTNUM VARCHAR2(32 CHAR),rn WEIGHT VARCHAR2(60 CHAR),rn WEIGHTUNIT VARCHAR2(10 CHAR),rn CUBATURE VARCHAR2(60 CHAR),rn CUBATUREUNIT VARCHAR2(10 CHAR),rn COUNTS NUMBER(8),rn COUNTRY VARCHAR2(60 CHAR),rn AGEADDITIONAL VARCHAR2(120 CHAR),rn PRODUCTAREA VARCHAR2(300 CHAR),rn YEARDATA VARCHAR2(120 CHAR),rn INNERSIZE VARCHAR2(300 CHAR),rn INNERSIZEUNIT VARCHAR2(10 CHAR),rn OUTERSIZE VARCHAR2(300 CHAR),rn OUTERSIZEUNIT VARCHAR2(10 CHAR),rn INSCRIPTION VARCHAR2(240 CHAR),rn SHAPE VARCHAR2(120 CHAR),rn COLOR VARCHAR2(120 CHAR),rn THUMBNAIL VARCHAR2(64 CHAR),rn GENRE VARCHAR2(120 CHAR),rn DEPTID CHAR(2 CHAR),rn KEEPSTATUS VARCHAR2(3000 CHAR),rn CREDITNUM VARCHAR2(32 CHAR),rn SAVECREDIT VARCHAR2(32 CHAR),rn SAVEDATE DATE,rn SAVEPLACE VARCHAR2(120 CHAR),rn SAVEUSER NUMBER(12),rn SAVETIME TIMESTAMP(6),rn INPUTUSER NUMBER(12),rn INPUTUSERNAME VARCHAR2(30 CHAR),rn INPUTTIME TIMESTAMP(6),rn VERIFYUSER NUMBER(12),rn VERIFYTIME TIMESTAMP(6),rn PUBUSER NUMBER(12),rn PUBTIME TIMESTAMP(6),rn DELUSER NUMBER(12),rn DELTIME TIMESTAMP(6),rn STATUS CHAR(2 CHAR),rn OLDID NUMBER(12),rn REGCODE2 VARCHAR2(32 CHAR),rn CPCATALOGYN VARCHAR2(10 CHAR) default 0

谁能帮我优化一下这条oracle 的sql语句？

<em>sql</em>语句如下：rn[code=SQL]select zonecode as zonecode,rn nvl(t.chargesrc, '-1') as chargesrc ,rn nvl(t.infectapp, '-1') as infectapp,rn sum(t.counts) as counts,rn sum(casern when t.flwcount = 1 thenrn 1rn elsern 0rn end) as sumflw1, rn sum(casern when t.flwcount = 2 thenrn 1rn elsern 0rn end) as sumflw2, --完成两次rn sum(casern when t.flwcount = 2 thenrn 1rn elsern 0rn end) as sumflw3, --完成三次rn sum(casern when t.flwcount = 2 thenrn 1rn elsern 0rn end) as sumflw4, --完成四次rn sum(casern when t.cd4count = 1 thenrn 1rn elsern 0rn end) as cd41, --完成一次cd4rn sum(casern when t.cd4count = 2 thenrn 1rn elsern 0rn end) as cd42, --完成两次cd4rn sum(casern when t.virunlncount = 1 thenrn 1rn elsern 0rn end) as viruln1, --完成一次rn sum(t.virunlnno) as noviruln ,--被抑制 rn sum(t.initvirunlncount) as avirulnsumrn from (select a.card_id as card_id,rn a.zonecode as zonecode,rn a.chargesrc as chargesrc,rn a.infectapp as infectapp,rn sum(casern when a.dt_treatbegin <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) thenrn 1rn elsern 0rn end) as counts,rn sum(casern when c.card_id is not null andrn b.dt_treatbegin <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) thenrn 1rn elsern 0rn end) as flwcount, --当年完成次数rn sum(casern when c.cd4 is not null andrn b.dt_treatbegin <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) thenrn 1rn elsern 0rn end) as cd4count, --完成检测数rn sum(casern when c.viruln is not null andrn b.dt_treatbegin <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) thenrn 1rn elsern 0rn end) as virunlncount,rn sum(casern when b.viruln is not null and b.viruln <> '0' andrn c.tm_create = to_date('2009-01-01', 'yyyy-mm-dd') andrn c.viruln = '0' andrn (c.dt_flworinterp - b.dt_treatbegin >= 180) andrn (c.dt_flworinterp - b.dt_treatbegin <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) thenrn 1rn elsern 0rn end) as virunlnno, --被抑制rn sum(casern when b.viruln is not null andrn b.dt_treatbegin >= to_date('2009-01-01', 'yyyy-mm-dd') andrn c.tm_create <>'1' and d.end_cause <>'2' and d.end_cause<>'4' or d.end_cause is null ) thenrn 1rn elsern 0rn end) as initvirunlncountrn from aidszh_sgra_child_info b, aidszh_sgra_child_flw c,aidszh_sgra_child_newstatus drn where b.card_id = c.card_id and d.card_id=b.card_idrn rn group by b.card_id, b.zonecode, b.chargesrc, b.infectapp) trn group by zonecode,rn nvl(t.chargesrc, '-1'),rn nvl(t.infectapp, '-1')[/code]rnrn在where语句中我都建立的索引，但是不走索引。rn执行计划如下所示：rn[img=http://p.blog.csdn.net/images/p_blog_csdn_net/xieyunchao/EntryImages/20091204/<em>sql</em>.jpg][/img]rnrn怎么样才能让让效率提高啊。

谁帮我看看这条sql有没有什么方法优化啊

select object_id,profile_type,profile_name,rolernfrom rn(rnselect apa.object_id,apa.profile_type,apa.profile_name,apa.role rnfrom processace apa left join process p on apa.object_id = p.object_id and p.state=2 rnwhere apa.profile_type=5rn)brnwhere b.object_id not inrn(rn select distinct bb.object_id from processace bb left join process p on p.object_id=bb.object_id and p.state= 2 where bb.profile_type=3rn)

请高手帮我优化下下面这条语句

SELECT a.PubBeginDate,a.PubEndDate,b.BookingDays rn FROM SD_kind_main a,SD_Other_Parameters b rn WHERE a.KindCode(+)=b.KindCode AND rownum<2 and b.KindCode is nullrnrna表是主表，b表是从表，b表的kindcode如果不为空的话，以kindcode为准，如果没有kindcode对应的值，则以空的值为准。

各位帮我看看这条语句怎么优化

SELECT a.sex,b.age,c.name,d.state,e.type,f.headrn rnrnFROM a AS a LEFT OUTER JOINrnrn rnrn b AS b ON a.PInstID = b.ID LEFT OUTER JOINrnrn rnrn c AS c ON c.ID = b.PrID LEFT OUTER JOINrnrn rnrn d AS d ON d.ID = c.PSetID LEFT OUTER JOINrnrn rnrn e AS e ON a.PInstID = e.PInstID AND rnrn rnrn a.HeaderID = e.ID LEFT OUTER JOINrnrn rnrn f AS f ON e.efctID = f.ID rnrn rnrnWHERE (a.Status IN (0, 1))rn====================================================rn各位高手这句SQL语句怎么<em>优化</em>啊，索引怎么加才合理啊rn

求大神们帮我优化一下这条sql语句

[code=<em>sql</em>]select rg.rghj,cl.clhj,fb.fbhj,jx.jxhj,zc.zchj from rn(select SUM(rg.hjje)as rghj from T_P_ChanZhi_FYWC rg,T_P_ProjectInfojg xm where xm.ProjectID=rg.ProjectID and xm.ProjectID='')rg,rn(select SUM(cl.hjje)as clhj from T_P_ChanZhi_WCCL cl,T_P_ProjectInfojg xm where xm.ProjectID=cl.ProjectID and xm.ProjectID='')cl,rn(select SUM(fb.hjje)as fbhj from T_P_ChanZhi_WCFB fb,T_P_ProjectInfojg xm where xm.ProjectID=fb.ProjectID and xm.ProjectID='')fb,rn(select SUM(jx.hjje)as jxhj from T_P_ChanZhi_WCJXSB jx,T_P_ProjectInfojg xm where xm.ProjectID=jx.ProjectID and xm.ProjectID='')jx,rn(select SUM(zc.hjje)as zchj from T_P_ChanZhi_WCZZCL zc,T_P_ProjectInfojg xm where xm.ProjectID=zc.ProjectID and xm.ProjectID='')zc[/code]

麻烦大牛们帮我优化下这条Sql语句

[code=SQL]rnselect distinct A.a0100 from lackarchives B, rn(rnselect a0100,'A0141' as colname,A0141 as colvalue from sba01 where A0141!=''rnunion rnselect a0100,'AdministrativeLevels' as colname,AdministrativeLevels as colvalue from sba01 where AdministrativeLevels!=''rnunionrnselect a0100,'TechnicalLevel' as colname,TechnicalLevel as colvalue from sba01 where TechnicalLevel!=''rnunionrnselect a0100,'PostPosition' as colname,PostPosition as colvalue from sba01 where PostPosition!=''rnunionrnselect a0100,'CulturalDegree' as colname,CulturalDegree as colvalue from sba01 where CulturalDegree!=''rnunionrnselect a0100,'AdministrativeLevels-R' as colname,AdministrativeLevels as colvalue from resume where AdministrativeLevels!=''rnunionrnselect a0100,'PostPosition-R' as colname,Position as colvalue from resume where Position!=''rnunionrnselect a0100,'PXDDDN' as colname,PXDDDM as colvalue from pxtable where PXDDDM!=''rnunionrnselect a0100,'JCDM' as colname,JCDM as colvalue from jctable where JCDM!=''rnunionrnselect a0100,'A1204' as colname, cast(A1204 as varchar) as colvalue from a12 where A1204!=''rnunionrnselect a0100,'PXXZDM' as colname,PXXZDM as colvalue from a08 where PXXZDM!=''rn) As Arnwhere A.colvalue!=''rnand A.colvalue=B.dircode and B.hlpcode=A.colnamernand not exists rn(rn select 1 from lackarchives C where C.hlpcode=A.colname and C.dircode=A.colvaluern and C.archid in (select clcodetype from archives where archives.a0100=A.a0100 )rn)) as c rnand kind='某一类材料名称的id'rn[/code] rn rn麻烦各位大牛给<em>优化</em>下这<em>条</em>Sql语句，再次先谢过了！

帮我优化下sql语句

select * from (select *,row_number() over (order by Sort desc ,EffectDate desc )as serialnumber from (select CASE parentid WHEN '00000000-0000-0000-0000-000000000000' THEN NULL ELSE Title END AS Title,parentid,CASE parentid WHEN '00000000-0000-0000-0000-000000000000' THEN Title ELSE (SELECT TOP 1 Title FROM CMS_Category AS A WHERE A.CategoryID = CMS_Category.parentid) END AS ParentTitle,(SELECT COUNT(*) FROM CMS_ContentAttachment WHERE CMS_ContentAttachment.ContentID = CMS_Content.ContentID) AS AttachmentCount,( SELECT COUNT(*) FROM CMS_ContentOfProducts WHERE CMS_ContentOfProducts.ContentID = CMS_Content.ContentID) AS ProductCount,CMS_Content.ID as intContentID,CMS_Content.ContentID,CMS_Content.ContentTitle,CMS_Content.ContentAlias,CMS_Content.HashFolderName,CMS_Content.TitleImageName,CMS_Content.Summary,CMS_Content.Description,CMS_Content.CategoryID,CMS_Content.IsAudit,CMS_Content.AuditResult,CMS_Content.IsActive,CMS_Content.IsDelete,CMS_Content.IsPublish,CMS_Content.IsArchiving,CMS_Content.IsDisplayInHome,CMS_Content.ImportanceLevel,CMS_Content.EffectDate,CMS_Content.ExpireDate,CMS_Content.Sort,CMS_Content.Hits,CMS_Content.MetaDesc,CMS_Content.Attribs,CMS_Content.Version,CMS_Content.CreateUserGuid,CMS_Content.CreateTime,CMS_Content.ModifyUserGuid,CMS_Content.ModifyTime,CMS_Content.Customlinks, CMS_TitleStyle.IsBold,CMS_TitleStyle.IsNeedColor,CMS_TitleStyle.Color,CMS_TitleStyle.IsItalic FROM CMS_Content INNER JOIN CMS_Category ON CMS_Content.CategoryID = CMS_Category.CategoryID AND CMS_Category.IsDelete = 0 left JOIN CMS_TitleStyle ON CMS_Content.ContentID = CMS_TitleStyle.TableID AND CMS_TitleStyle.TitleKind = 2 where ( cms_content.EffectDate < GETDATE() ) and (cms_content.ExpireDate is null or GETDATE()< DATEADD(day,1,CMS_Content.ExpireDate)) and CMS_Category.ParentID ='f42428ed-5667-4131-8504-4ccb1c34f0ac' and CMS_Content.IsDelete = 'false' and CMS_Content.IsArchiving = 'false' and CMS_Content.IsPublish = 'true') h) as T where T.SerialNumber > ( 0) and T.SerialNumber <= (8 )rn

这条SQL语句如何优化呢？

select rna.id , rna.username, rna.avatar,rn(select COUNT(b.user_id) from poll_topics b where (a.id=b.user_id) and (UNIX_TIMESTAMP(b.created_at) > UNIX_TIMESTAMP() - 86400) ) as topics,rn(select COUNT(c.user_id) from user_votes c where (a.id=c.user_id) and (UNIX_TIMESTAMP(c.created_at) > UNIX_TIMESTAMP() - 86400) ) as votes,rn(select COUNT(d.user_id) from poll_comments d where (a.id=d.user_id) and (UNIX_TIMESTAMP(d.created_at) > UNIX_TIMESTAMP() - 86400) ) as comments,rn(select COUNT(b.user_id) from poll_topics b where (a.id=b.user_id) and (UNIX_TIMESTAMP(b.created_at) > UNIX_TIMESTAMP() - 86400) )*3+rn(select COUNT(c.user_id) from user_votes c where (a.id=c.user_id) and (UNIX_TIMESTAMP(c.created_at) > UNIX_TIMESTAMP() - 86400) )*2+rn(select COUNT(d.user_id) from poll_comments d where (a.id=d.user_id) and (UNIX_TIMESTAMP(d.created_at) > UNIX_TIMESTAMP() - 86400) )*2 as activernfrom users a rngroup by a.id rnorder by active descrnlimit 6;rnrnactive字段是topics*3 + votes*2 + comments*2计算出来的，前面已经进行过一次查询，那么在计算active字段时是否还需要进行查询呢？有没有更好的写法呢？

谁来帮忙优化一下这条SQL语句

其实就是从视图中搜索第16<em>条</em>记录之后的15<em>条</em>记录。rn使用的数据库为SQL 2000rn求更<em>优化</em>的语句！！rnrnSELECT * FROM V_view WHERE sortID =89 AND id IN (SELECT TOP 15 id FROM V_view WHERE sortID =89 AND id NOT IN(SELECT TOP 15 id FROM V_view WHERE sortID =89 ORDER BY id desc ) ORDER BY id desc ) ORDER BY id desc rnrn谢谢大侠们！rnrnrn

这条sql语句怎么优化？求教高手

"select detail.serialid,srzh,zzcbh,yzcbh,to_char(createdate,'yyyy-mm-dd hh24:mi') as begin_time,last_record.workorder from rn t_ct51d,t_task_finish_detail detail,t_task_finish_detail last_record where detail.task_id=t_ct51d.task_id rn and detail.workorder='000001' and detail.end_time is null and detail.ifcancel <> '1' and t_ct51d.status <> '30'rn and detail.begin_task_sort = '11' and detail.last_serialid = last_record.serialid order by zzcbh";rn求教上边的<em>sql</em>语句怎么<em>优化</em>啊？task_id，workorder，serialid 是索引的。现在查询都是全表扫描，很慢。oracle8的。

请教这条SQL还有优化的余地吗？

select top 1 a.id,a.name,a.time,b.name from new a inner join t b on a.t_id=b.id where b.id=123 order by a.id descrnrnA表 100万<em>条</em>左右。B表10万<em>条</em>左右。B表其他字段多，字段内容多。rnrn谢谢大家。

求高手优化这条sql语句

语句一和语句二都能很快显示结果,但当一和二合并时，却半天没有结果rn语句一：rn rn select we.wip_entity_name,rn msi.inventory_item_id,rn msi.segment1,rn we.organization_id,rn msi.description,rn wdj.START_QUANTITY,rn wdj.QUANTITY_completed,rn wdj.QUANTITY_SCRAPPED,rn wdj.creation_date,rn max(WMT.TRANSACTION_DATE) last_date,rn wdj.COMPLETION_SUBINVENTORY,rn wdj.DATE_COMPLETED,rn TO_OPERATION_SEQ_NUM,rn wdj.wip_entity_id, rn decode(substr(wdj.class_code, 1, 2),rn 'FX',rn msi.attribute1,rn msi.WIP_SUPPLY_SUBINVENTORY) WIP_SUPPLY_SUBINVENTORYrn from wip.wip_move_transactions wmt,rn wip.WIP_DISCRETE_JOBS wdj,rn wip.wip_entities we,rn inv.mtl_system_items msirn where rn wmt.organization_id+0=4rn and wdj.wip_entity_id = wmt.wip_entity_id+0rn and wdj.COMPLETION_SUBINVENTORY = 'J030半成品'rn and wdj.organization_id = wmt.organization_idrn and we.wip_entity_id= wmt.wip_entity_id+0rn and WMT.TRANSACTION_DATE>= to_date('&p_fm_date','YYMMDD')rn and WMT.TRANSACTION_DATE < to_date('&p_to_date','YYMMDD') + 1rn and TO_OPERATION_SEQ_NUM =rn (select max(OPERATION_SEQ_NUM)rn from APPS.WIP_OPERATIONS worn where wo.wip_entity_id = wmt.wip_entity_idrn and wo.organization_id = wmt.organization_id)rn and WMT.to_INTRAOPERATION_STEP_TYPE+0= 3rn rn and msi.organization_id= we.organization_id rn and msi.inventory_item_id= we.primary_item_idrn AND WE.WIP_ENTITY_NAME || '' LIKE '&P_1'rn and ((wdj.class_code not like 'FX%' andrn msi.WIP_SUPPLY_SUBINVENTORY || '' like '&P_SUB_CODE' || '%') orrn (wdj.class_code like 'FX%' andrn msi.attribute1 || '' like '&P_SUB_CODE' || '%')) rn rn group by wip_entity_name,rn msi.inventory_item_id,rn msi.segment1,rn we.organization_id,rn msi.description,rn wdj.START_QUANTITY,rn wdj.QUANTITY_completed,rn wdj.QUANTITY_SCRAPPED,rn wdj.COMPLETION_SUBINVENTORY,rn wdj.DATE_COMPLETED,rn TO_OPERATION_SEQ_NUM,rn wdj.wip_entity_id,rn wdj.creation_date, rn decode(substr(wdj.class_code, 1, 2),rn 'FX',rn msi.attribute1,rn msi.WIP_SUPPLY_SUBINVENTORY)rn语句二：rn select distinct mmt.transaction_referencern from inv.mtl_material_transactions mmtrn where rn mmt.organization_id=4rn and substr(mmt.transaction_reference,1,2) in('JM','JW')rn and mmt.transaction_date >= to_date('&p_fm_date', 'YYMMDD')rn and mmt.transaction_date = to_date('&p_fm_date','YYMMDD')rn and WMT.TRANSACTION_DATE < to_date('&p_to_date','YYMMDD') + 1rn and TO_OPERATION_SEQ_NUM =rn (select max(OPERATION_SEQ_NUM)rn from APPS.WIP_OPERATIONS worn where wo.wip_entity_id = wmt.wip_entity_idrn and wo.organization_id = wmt.organization_id)rn and WMT.to_INTRAOPERATION_STEP_TYPE+0= 3rn rn and msi.organization_id= we.organization_id rn and msi.inventory_item_id= we.primary_item_idrn AND WE.WIP_ENTITY_NAME || '' LIKE '&P_1'rn and ((wdj.class_code not like 'FX%' andrn msi.WIP_SUPPLY_SUBINVENTORY || '' like '&P_SUB_CODE' || '%') orrn (wdj.class_code like 'FX%' andrn msi.attribute1 || '' like '&P_SUB_CODE' || '%')) rn rnrn and we.wip_entity_name not inrn (--语句二rn select distinct mmt.transaction_referencern from inv.mtl_material_transactions mmtrn where rn mmt.organization_id=4rn and substr(mmt.transaction_reference,1,2) in('JM','JW')rn and mmt.transaction_date >= to_date('&p_fm_date', 'YYMMDD')rn and mmt.transaction_date

这条sql 语句如何优化，提交效率 ？

这<em>条</em>查询运行时间需要30秒，太长了，不知道怎样<em>优化</em>呢 ？rnrnSELECT do.Order_ID as id, do.BriefNo, do.ReviseNo as Revise,do.Bid,price = do.grade + '/' + do.currency,et.Exhibition_en, et.Exhibition_cn, ct.Client_name as client, et.Start_Date, et.End_Date,ut.USERNAME as Sales, Designer = dbo.f_getorderdesigner(Do.Order_ID), do.Act_Start_Date, do.Act_End_Date,do.Summit_Time as Create_date FROM DesignOrder as do INNER JOIN Join_ID as ji ON do.Join_ID = ji.Join_id INNER JOIN USER_TABLE as ut ON do.UserID = ut.USERID INNER JOIN UserGroup as ug ON ut.USERGROUP_ID = ug.UserGroup_ID INNER JOIN Exhibition_Table as et ON ji.Exhibition_ID = et.Exhibition_ID INNER JOIN Client_Table as ct ON ji.Client_ID = ct.Client_ID INNER JOIN Company as cp ON ut.Company_id = cp.Company_id INNER JOIN Nature as na ON do.Nature_Id = na.Nature_id WHERE ( do.UserID = ut.USERID and dbo.f_designer_exist(Do.Order_ID,'jacky.jiang') like 'jacky.jiang' and ut.State = 'active' and et.Start_Date>='4/1/2011' and et.Start_Date )

请问这条sql 语句怎样优化呢?

如题:rnSELECT username, pid, style, datetime, SUM(price) AS sumprice,rn (SELECT picrn FROM dbo.canpinrn WHERE pid = t_price.pid) AS pic,rn (SELECT cityrn FROM dbo.user_tablern WHERE username = t_price.username) AS cityrnFROM dbo.t_pricernGROUP BY username, pid, style, datetime

能优化这条sql语句吗？

<em>sql</em>="select * from da where not t in (select t from pa) and t>='"&trim(request.form("cid1"))&"' and trnrn这<em>条</em>语句执行的特别慢，请高手指点怎么改进这<em>条</em>语句？

这条sql语句怎么优化呢？

select STORE_takeMatM.cpbh,STORE_takeMatM.cpth,STORE_takeMatM.cpxh,STORE_takeMatN.remark,STORE_takeMatN.dr,STORE_takeMatN.plan_price,STORE_takeMatN.takedate,sendmat.rq,MAT_storage.item_name,MAT_storage.item_spec,MAT_storage.item_xh,MAT_storage.item_unit,MAT_storage.item_num,(MAT_storage.item_num*MAT_storage.item_price) as item_hj,MAT_storage.item_price,(STORE_takeMatN.dr*STORE_takeMatN.plan_price) as item_hj1,(select department_name from system_department where system_department.department_id=STORE_takeMatM.dept_id) as department_name from MAT_storage, STORE_takeMatN,sendmat,STORE_takeMatM where Sendmat.item_no=MAT_storage.item_no and Sendmat.item_no=STORE_takeMatN.item_no and sendmat.storageNo=STORE_takeMatN.storageNo and STORE_takeMatN.storageNo=STORE_takeMatM.storageNorn执行起来特别慢rn请大家<em>帮</em>忙

请帮忙优化一下这条SQL

Select Rid,rn Bar_Id,rn Movie_Id,rn Part_Id,rn Begin_Time,rn End_Time,rn Proportion,rn Publish_Datern From (Select Rownum As Rid,rn Bar_Id,rn Movie_Id,rn Part_Id,rn Begin_Time,rn End_Time,rn Proportion,rn Publish_Datern From (Select m.Bar_Id,rn m.Movie_Id,rn m.Part_Id,rn m.Begin_Time,rn m.End_Time,rn m.Proportion,rn m.Publish_Datern From b_Bar_Info i, m_Bar_Movie mrn Where i.Bar_Id = m.Bar_Id And m.Proportion = 0rn Order By m.Publish_Date Desc))rnWhere Rid > 0 And Rid <= 20rnrn最里面的SELECT不加Group By,执行时间0.109秒,rn加Group By时间4.672秒rn加上中间的SELECT的时间是8.203秒rn全部(即加上最外边的SELECT)时间为9.032秒rn因为我不是专业的DBA,是一个java 程序员,这<em>条</em>SQL已经经过<em>优化</em>,但是最好的都要9秒多,还是很不理想,请各位<em>帮</em>忙看一下

（急）如何优化我的这条SQL语句?

SELECT v.sub_id,v.city_id AS city_id ,v.vip_level AS vip_level FROM vip_user_info v rnWHERE v.rec_status=1 AND v.cust_type = 1 rn AND EXISTS(SELECT 1 FROM vip_manager vm WHERE v.sub_id=vm.sub_id AND vm.rec_status=1 rn AND EXISTS ( SELECT 1 FROM manager_info m WHERE vm.manager_id=m.manager_id AND m.rec_status=1 AND m.manager_type=5 ))rnrn-------------------------------------------rn其中:rnvip_user_info(v) VIP用户信息表rnvip_manager(vm) VIP用户-客户经理关系表rnmanager_info(m) 客户经理信息表rnrn因为数据量太大，造成查询时间太长，请问如何进行<em>优化</em>比较好呢？

如何优化这条sql语句呢?

select * from declarebill where ModyfyTime like "%2008-02-22%" and EnterpriseType='外商投资企业' and Single_Audit='1' and billmode='02' or EnterpriseType='中外合资企业' and ModyfyTime like "%2008-02-22%" and Single_Audit='1' and billmode='02'rnrn我想EnterpriseType字段中的'外商投资企业'和'中外合资企业' 都拥有这个<em>条</em>件ModyfyTime like "%2008-02-22%" and Single_Audit='1' and billmode='02'rn应该如何<em>优化</em>呢?我现在写的太长啦!

这条sql 语句可以优化吗?

下面这<em>条</em><em>sql</em> 语句可以<em>优化</em>吗?rnrnSELECT *rnFROM dbo.T1rnWHERE (id NOT INrn (SELECT idrn FROM dbo.T2))

这条 SQL 如何优化 ??? (在线等)

我有<em>条</em>SQL如下rnselect b.cons_no,rn b.cust_query_no,rn b.cons_name,rn a.rcvbl_ym,rn a.release_date,rn a.amt_type,rn a.RCVBL_AMT,rn a.RCVED_AMT,rn a.RCVBL_PENALTY,rn a.RCVED_PENALTY,rn a.T_PQ,rn (select sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY)rn from EPM_JX.A_RCVBL_FLOWrn where cons_no = a.cons_no) OWE_AMT,rn (select nvl(sum(PREPAY_BAL), 0)rn from EPM_JX.A_ACCT_BALrn where cons_no = a.cons_no) PREPAY_BALrn from EPM_JX.A_RCVBL_FLOW a, EPM_JX.c_cons brn where a.cons_no = b.cons_norn and a.rcvbl_amt > 0rn and a.RELEASE_DATE = '20081001'rn and a.org_no like '36408'rn and b.org_no like '36408'rnrn它的结果集有 40000 <em>条</em>记录，但是很慢，而且执行到1000行左右就执行不下去了，rn主要是如下两个费时间rn(select sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY)rn from EPM_JX.A_RCVBL_FLOWrn where cons_no = a.cons_no) OWE_AMT,rn (select nvl(sum(PREPAY_BAL), 0)rn from EPM_JX.A_ACCT_BALrn where cons_no = a.cons_no) PREPAY_BALrnrnrn这我该如何<em>优化</em>SQL啊，高手<em>帮</em>我！！！！

这条SQL还有优化余地吗？

环境是oralce10，下面这个<em>sql</em>在我这环境里执行大约需要50秒左右，其中TABLEA的数据为400多万，TABLEB的数据为1200多万rn请问下高手，该<em>sql</em>是否还有<em>优化</em>余地rn[code=SQL]rnselect /*+index(mbf IDX_MT_BIZ_FIN_FIN_DATE_01;mprf IDX_MT_PAY_RECORD_FIN_1) */ decode(pers_type,'1','学生','5','老师','教授') pers_type,rn nvl(count(distinct(mbf.indi_id)), 0.00) as jzrs,rn nvl(sum(decode(mprf.fund_id,rn '003',rn mprf.real_pay,rn '901',rn mprf.real_pay,rn '001',rn mprf.real_pay,rn '301',rn mprf.real_pay,rn '201',rn mprf.real_pay,rn '999',rn mprf.real_pay,rn 0)),rn 0) as all_money,rn nvl(sum(decode(mprf.fund_id, '003', mprf.real_pay)), 0.00) as grzh_money,rn nvl(sum(decode(mprf.fund_id, '001', mprf.real_pay)), 0.00) as tc_money,rn nvl(sum(decode(mprf.fund_id, '301', mprf.real_pay)), 0.00) as offical_money,rn nvl(sum(decode(mprf.fund_id, '201', mprf.real_pay)), 0.00) as db_money,rn nvl(sum(decode(mprf.fund_id, '901', mprf.real_pay)), 0.00) as grzf_yw,rn nvl(sum(decode(mprf.fund_id, '999', mprf.real_pay)), 0.00) as grzf_moneyrnrn from TABLEA mprf, TABLEB mbfrn where mbf.serial_no = mprf.serial_norn and mprf.valid_flag = '1'rn and mbf.valid_flag = '1'rn and mbf.center_id = '430201'rn and mbf.biz_type between 10 and 14rn -- and mbf.biz_type in (10, 11, 12, 13, 14)rn and to_char(mbf.fin_date, 'yyyymmdd') >= '20000101'rn and to_char(mbf.fin_date, 'yyyymmdd') )rnrn[/code]

这条SQL语句能优化吗？

select * from (select VehicleLic 车牌号,Longitude 经度,Latitude 纬度,Position 地址,min(GpsDateTime) 开始时间,max(GpsDateTime)结束时间,count(*) 记录数,convert(char(8),(max(GpsDateTime)-min(GpsDateTime)),108) 时间 from gps00320110206 a,VehicleInfo b where a.VehicleID=b.VehicleID group by VehicleLic,Longitude,Latitude,Position having count(*)>1) as c rnwhere 时间'2011-02-06 12:25:38'and 结束时间

这条sql语句该怎么优化呢？

[code=SQL]rnupdate user set user.coin=user.coin+ footballrec.getcoin,user.jifen=user.jifenrn+rncase when not EXISTS(select id from A where Auserid=user.id) thenrn case when footballrec.getcoin - footballrec.betcoin >=1000 and footballrec.getcoin - footballrec.betcoin =10000 and footballrec.getcoin - footballrec.betcoin =50000 thenrn 3rn elsern 0rn endrnelsern0rnendrn,user.jifenall=user.jifenall+rncase when not EXISTS(select id from A where Auserid=user.id) thenrn case when footballrec.getcoin - footballrec.betcoin >=1000 and footballrec.getcoin - footballrec.betcoin =10000 and footballrec.getcoin - footballrec.betcoin =50000 thenrn 3rn elsern 0rn endrnelsern0rnendrn,user.jyan=user.jyan+(rncase when footballrec.betcoin >=50000 and footballrec.betcoin =100000 and footballrec.betcoin =200000 and footballrec.betcoin =500000 and footballrec.betcoin =1000000 thenrn15rnelsern0rnendrn) from user inner join footballrec on user.id=footballrec.userid where footballrec.betnum= + @bnumrnrnrn[/code]rnrn作用是根据竞猜表(footballrec)中的投注记录更新用户表(user)里用户的“金币”，“积分”，“总积分”，“经验值”。rnuser表中的几个字段：rnid=用户的IDrncoin=用户的金币rnjifen=用户的积分rnjifenall=用户的总积分rnjyan=用户的经验rnrnfootballrec表中的几个字段：rnuserid=用户IDrnbetconi=用户在这一期的投注金币rngetcoin=用户在这一期的获得金币rnbetnum=这一期游戏的期号rnrn现在应该怎么<em>优化</em>这<em>条</em>语句呢，先谢过大家了！！

这条sql有没优化余地

explain select sum(if(is_hot = 1,1,0) ) as is_hot,sum(if(is_new = 1,1,0) ) as is_new,sum(if(is_best = 1,1,0) ) as is_best ,sum(if(goods_number <= warn_number ,1,0)) as warn ,sum(if(is_alone_sale=1,1,0)) as total FROM `icmall`.`ecs_goods` WHERE is_delete = 0 AND is_real = 1 rnrnrn1 SIMPLE ecs_goods ref delete_real_goods_id,all_info all_info 2 const,const 2302644 rnrnrn以下是这个表的索引情况rnecs_goods 0 PRIMARY 1 goods_id A 2302656 BTREE rnecs_goods 1 cat_id 1 cat_id A 850 BTREE rnecs_goods 1 brand_id 1 brand_id A 348 BTREE rnecs_goods 1 goods_type 1 goods_type A 1 BTREE rnecs_goods 1 goods_name 1 goods_name A 2302656 BTREE rnecs_goods 1 brand_goods_id 1 brand_goods_id A 2302656 BTREE rnecs_goods 1 delete_real_goods_id 1 is_delete A 1 BTREE rnecs_goods 1 delete_real_goods_id 2 is_real A 1 BTREE rnecs_goods 1 delete_real_goods_id 3 goods_id A 2302656 BTREE rnrnrn现在的查询时间。大概是 10秒，大概有2302715<em>条</em>数据。

求大神看看这条SQL怎么优化

SELECT ( po.InsuredUnit + '--' + po.PolicyNo ) AS ProjectName ,rn ( SELECT COUNT(CustomerID)rn FROM Biz_ImportCustomerHis WITH ( NOLOCK )rn WHERE ISNULL(CustomerID, '') != 0rn AND ( ( ModifyType = 1rn AND Status = 1rn AND ImportType = 2rn )rn OR ( ImportType = 1rn AND Status = 1rn )rn )rn AND Memo LIKE '%新增%'rn AND PolicyNo = po.PolicyNorn AND CreateTime >= '2015-06-25 00:00:00.200'rn AND CreateTime < '2016-06-25 00:00:00.200'rn ) AS InsuredCount ,rn ( SELECT COUNT(b1.CustomerID)rn FROM Biz_HealthMedical b1 WITH ( NOLOCK )rn WHERE b1.CustomerID IN ( SELECT CustomerIDrn FROM Biz_CustomerAccount b2rn WHERE b2.PolicyNo = po.PolicyNo )rn AND b1.ActivationTime >= '2015-06-25 00:00:00.200'rn AND b1.ActivationTime < '2016-06-25 00:00:00.200'rn ) AS ActiveCount ,rn ( SELECT COUNT(c3.CustomerID)rn FROM Biz_Customer c3 WITH ( NOLOCK )rn WHERE c3.HCN IN (rn SELECT c1.UserNamern FROM Sys_User c1 WITH ( NOLOCK )rn WHERE ISNULL(c1.WeiXinID, '') != ''rn AND c1.UserName IN (rn SELECT c2.HCNrn FROM Biz_CustomerAccount c2rn WHERE c2.PolicyNo = po.PolicyNo )rn AND c1.WeiXinBindTime >= '2015-06-25 00:00:00.200'rn AND c1.WeiXinBindTime < '2016-06-25 00:00:00.200' )rn ) AS WeixinBindCount ,rn ( SELECT COUNT(d1.CustomerID)rn FROM Biz_Customer d1 WITH ( NOLOCK )rn WHERE d1.HCN IN (rn SELECT d3.HMCardNumberrn FROM Biz_Consuming d3rn WHERE d3.TransactionType IN ( 0, 3, 8, 9 )rn AND d3.AccountID IN (rn SELECT d2.AccountIDrn FROM Biz_CustomerAccount d2rn WHERE d2.PolicyNo = po.PolicyNo )rn AND d3.CreateTime >= '2015-06-25 00:00:00.200'rn AND d3.CreateTime < '2016-06-25 00:00:00.200' )rn ) AS UserCount ,rn ( SELECT ISNULL(SUM(e1.Amount), 0)rn FROM Biz_Consuming e1 WITH ( NOLOCK )rn WHERE e1.TransactionType IN ( 1, 4, 5 )rn AND e1.AccountID IN ( SELECT e2.AccountIDrn FROM Biz_CustomerAccount e2rn WHERE e2.PolicyNo = po.PolicyNo )rn AND e1.CreateTime >= '2015-06-25 00:00:00.200'rn AND e1.CreateTime < '2016-06-25 00:00:00.200'rn ) AS RechargeAmountSum ,rn ( SELECT -ISNULL(SUM(f1.Amount), 0)rn FROM Biz_Consuming f1 WITH ( NOLOCK )rn WHERE f1.TransactionType IN ( 0, 3, 8, 9 )rn AND f1.AccountID IN ( SELECT f2.AccountIDrn FROM Biz_CustomerAccount f2rn WHERE f2.PolicyNo = po.PolicyNo )rn AND f1.CreateTime >= '2015-06-25 00:00:00.200'rn AND f1.CreateTime < '2016-06-25 00:00:00.200'rn ) AS ConsumAmountSum ,rn ( SELECT -ISNULL(SUM(g1.Amount), 0)rn FROM Biz_Consuming g1 WITH ( NOLOCK )rn WHERE g1.TransactionType = 2rn AND g1.AccountID IN ( SELECT g2.AccountIDrn FROM Biz_CustomerAccount g2rn WHERE g2.PolicyNo = po.PolicyNo )rn AND g1.CreateTime >= '2015-06-25 00:00:00.200'rn AND g1.CreateTime < '2016-06-25 00:00:00.200'rn ) AS ReimburseAmountSum ,rn ( ( SELECT ISNULL(SUM(e1.Amount), 0)rn FROM Biz_Consuming e1 WITH ( NOLOCK )rn WHERE e1.TransactionType IN ( 1, 4, 5 )rn AND e1.AccountID IN ( SELECT e2.AccountIDrn FROM Biz_CustomerAccount e2rn WHERE e2.PolicyNo = po.PolicyNo )rn AND e1.CreateTime >= '2015-06-25 00:00:00'rn AND e1.CreateTime < '2016-06-25 00:00:00.200'rn )rn - ( SELECT -ISNULL(SUM(f1.Amount), 0)rn FROM Biz_Consuming f1rn WHERE f1.TransactionType IN ( 0, 3, 8, 9 )rn AND f1.AccountID IN (rn SELECT f2.AccountIDrn FROM Biz_CustomerAccount f2rn WHERE f2.PolicyNo = po.PolicyNo )rn AND f1.CreateTime >= '2015-06-25 00:00:00'rn AND f1.CreateTime < '2016-06-25 00:00:00.200'rn ) ) AS AmountSum ,rn ( SELECT COUNT(i1.Amount)rn FROM Biz_Consuming i1 WITH ( NOLOCK )rn WHERE i1.TransactionType IN ( 0, 3, 8, 9 )rn AND i1.AccountID IN ( SELECT i2.AccountIDrn FROM Biz_CustomerAccount i2rn WHERE i2.PolicyNo = po.PolicyNo )rn AND i1.CreateTime >= '2015-06-25 00:00:00.200'rn AND i1.CreateTime < '2016-06-25 00:00:00.200'rn ) AS UseCountrnFROM Biz_PolicyInfo po;

怎样优化这条oracle sql语句

语句如下:rn select /*+user_hash(a b c)*/ rn b.id, rn b.name, rn count(c.number)rn from v_useinfo a, v_info c, t_region brn where NOT EXISTS (select numberrn from v_pdreportrn where number = a.numberrn and ddate >= sysdate-1rn and ddate < sysdate)rn and a.status in (3,4)rn and a.number = c.numberrn and a.stype = 1rn and c.id = b.idrn group by b.id, b.name;rn说明 表v_useinfo ，v_info ，v_pdreport数据量非常大,该怎样<em>优化</em>这<em>条</em>语句?

求教　这条sql语句怎么优化？？

select a.Stnm,b.Year,c.Xsl,b.Wqg,b.Mespestrnfrom (select a.stcd,max(a.year) year,b.Stnm from wq_msar_a_year a inner join Wq_Wqsinf_b b on a.Stcd=b.Stcdrn inner join Wq_Swsinf_b c on c.Stcd=a.Stcd rn where (c.stfn='00011000' or c.stfn='01011000' or (c.stfn='01010000' and b.Xiangmuqu='2')) rn and (b.Xiangmuqu='1' or b.Xiangmuqu='2') group by a.stcd,b.stnmrn ) a rn rninner join (select a.stcd,a.Year,a.Wqg,a.Mespest from Wq_Msar_a_Year a inner join Wq_Wqsinf_b b on a.Stcd=b.Stcdrn inner join Wq_Swsinf_b c on c.Stcd=a.Stcdrn where a.Tp=1 and (c.stfn='00011000' or c.stfn='01011000' or (c.stfn='01010000' and b.Xiangmuqu='2')) rn and(b.Xiangmuqu='1' or b.Xiangmuqu='2') rn ) b on a.Stcd=b.Stcd and a.year=b.yearrn rninner join (select stcd,round(avg(Xsl),2) xsl,to_char(spt,'yyyy') year from Wq_Other_d a inner join rn Wq_Wqsinf_b b on a.Stcd=b.Stcdrn inner join Wq_Swsinf_b c on c.Stcd=a.Stcd rn where (c.stfn='00011000' or c.stfn='01011000' or (c.stfn='01010000' and b.Xiangmuqu='2')) rn and (b.Xiangmuqu='1' or b.Xiangmuqu='2') group by stcd,to_char(spt,'yyyy')rn ) c on c.stcd=a.Stcd and c.year=a.year

请看看这条sql怎么优化？谢谢

SELECT ssfzgy_zdspzs.spzs , rn ssfzsp_spmspj.sp_sz ,rn ssfzvw_spjchz1.lbid, rn ssfzgy_zdSpflBh.ssrqid, rn ssfzgy_zdspmc.spmc, rn ssfzgy_zdspys.spys , rn ssfzgy_zdspcd.spcd , rn ssfzgy_zdSpflBh.spsh, rn ssfzgy_zdSpflBh.spbh, rn ssfzgy_zdSpflzm.spzsid, rn ssfzgy_zdSpflzm.spmcid, rn ssfzgy_zdSpflBh.spysid, rn ssfzgy_zdSpflBh.spcdid, rn ssfzsp_spmspj.sp_qd, rn ssfzsp_spmspj.sp_cf, rn ssfzsp_spmspj.sp_nxs, rn ssfzsp_spmspj.sp_ssl ,rn ssfzvw_spjchz1.spbhid,rn ssfzvw_spjchz1.ckid,rn ssfzvw_spjchz1.jc_spjs,rn ssfzvw_spjchz1.jc_spsl, rn ssfzvw_spjchz1.pp_spsl, rn ssfzvw_spjchz1.pl_spsl,rn ssfzvw_spjchz1.pi_spsl,rn ssfzgy_zdspflbh.spbz,rn ssfzgy_zdspflbh.sppz,rn ssfzps_preps.ypsl as ypsl,rn ssfzgy_zdspflbh.sppsbz,rn sum(case when ssfzvw_spjchz1.ckid=ssfzps_spck.rcckid then ssfzps_spck.rcsl end) as psrcsl,rn sum(case when ssfzvw_spjchz1.ckid=ssfzps_spck.psckid then ssfzps_spck.ypsl end) as pssl,rn sum(case when ssfzvw_spjchz1.ckid=ssfzps_spck.psckid then ssfzps_spck.ypwcsl when ssfzvw_spjchz1.ckid=ssfzps_spck.rcckid then ssfzps_spck.rcwcsl end) as ypwcslrn FROM ((((((((ssfzvw_spjchz1 inner join ssfzgy_zdSpflBh with(nolock) on (ssfzvw_spjchz1.spbhid = ssfzgy_zdSpflBh.spbhid )) inner joinrn ssfzgy_zdSpflzm with(nolock) on (ssfzgy_zdSpflzm.spflzmid = ssfzgy_zdSpflBh.spflzmid)) rn inner join ssfzgy_zdspmc with(nolock) on(ssfzgy_zdSpflzm.spmcid = ssfzgy_zdspmc.spmcid))rn left outer join ssfzgy_zdspys with(nolock) on(ssfzgy_zdSpflBh.spysid = ssfzgy_zdspys.spysid))rn inner join ssfzgy_zdspzs with(nolock) on( ssfzgy_zdSpflzm.spzsid = ssfzgy_zdspzs.spzsid))rn left outer join ssfzgy_zdspcd with(nolock) on( ssfzgy_zdSpflBh.spcdid = ssfzgy_zdspcd.spcdid))rn inner join ssfzsp_spmspj with(nolock) on( ssfzgy_zdSpflBh.spbhid = ssfzsp_spmspj.spbhid))rn left outer join ssfzps_preps on (ssfzvw_spjchz1.spbhid=ssfzps_preps.wlid and rn ssfzvw_spjchz1.ckid = ssfzps_preps.spckid and ssfzps_preps.psbz='0')rn left outer join ssfzps_spck on(ssfzvw_spjchz1.spbhid=ssfzps_spck.wlid))rnGROUP BY ssfzgy_zdspzs.spzs, rn ssfzsp_spmspj.sp_sz,rn ssfzvw_spjchz1.lbid, rn ssfzgy_zdSpflBh.ssrqid, rn ssfzgy_zdspmc.spmc, rn ssfzgy_zdspys.spys, rn ssfzgy_zdspcd.spcd, rn ssfzgy_zdSpflBh.spsh, rn ssfzgy_zdSpflBh.spbh, rn ssfzgy_zdSpflzm.spzsid, rn ssfzgy_zdSpflzm.spmcid, rn ssfzgy_zdSpflBh.spysid, rn ssfzgy_zdSpflBh.spcdid, rn ssfzsp_spmspj.sp_qd, rn ssfzsp_spmspj.sp_cf, rn ssfzsp_spmspj.sp_nxs, rn ssfzsp_spmspj.sp_ssl ,rn ssfzvw_spjchz1.spbhid,rn ssfzvw_spjchz1.ckid,rn ssfzvw_spjchz1.jc_spjs,rn ssfzvw_spjchz1.jc_spsl, rn ssfzvw_spjchz1.pp_spsl, rn ssfzvw_spjchz1.pl_spsl,rn ssfzgy_zdspflbh.spbz,rn ssfzgy_zdspflbh.sppz,rn ssfzps_preps.ypsl,rn ssfzgy_zdspflbh.sppsbz,rn ssfzvw_spjchz1.pi_spsl,rn ssfzvw_spjchz1.tl_spsl

请高手帮助优化这条SQL语句

SELECT A.GJLDATE GJLDATE,rn A.GJLGDID GJLGDID,rn GOODSBASE.GBCNAME,rn GOODSBASE.GBBARCODE,rn GOODSPRICE.GPHSJJ,rn GOODSPRICE.GPSJ,rn SUM(DECODE(A.GJLTRAN,'1',A.GJLSL,'2',A.GJLSL,'3',A.GJLSL,'4',A.GJLSL,'5',A.GJLSL,'6',A.GJLSL,0)) JHSL, rn -1*SUM(DECODE(A.GJLTRAN,'E',A.GJLSL,'F',A.GJLSL,'G',A.GJLSL,'H',A.GJLSL,0)) XSSL,rn SUM(B.GJLQMSL) KCSLrn FROM GOODSJXCLIST A,GOODSPRICE,GOODSBASE,rn (SELECT GJLGDID,GJLDATE,GJLQMSL FROM GOODSJXCLIST WHERE GJLSEQ IN (SELECT MAX(GJLSEQ)FROM GOODSJXCLIST GROUP BY GJLDATE,GJLGDID) ) Brn WHERE A.GJLGDID=GOODSPRICE.GPGDID ANDrn A.GJLGDID=B.GJLGDID AND rn A.GJLDATE=B.GJLDATE ANDrn A.GJLGDID=GOODSBASE.GBID ANDrn A.GJLMFID = '2020099' AND rn ( A.GJLDATE >= TO_DATE('2002-10-01','YYYY-MM-DD') AND A.GJLDATE )) rn GROUP BY A.GJLDATE,rn A.GJLGDID,rn GOODSBASE.GBCNAME,rn GOODSBASE.GBBARCODE,rn GOODSPRICE.GPHSJJ,rn GOODSPRICE.GPSJrn ORDER BY A.GJLDATErnrn这是一<em>条</em>放在数据窗口的语句，所以不能用临时表，这<em>条</em>语句涉及的三个表的数据量都很大，执行起来花一个多小时，请问该怎么<em>优化</em>这<em>条</em>语句；

向高手请教这条SQL可否优化？

向高手请教这<em>条</em>SQL可否<em>优化</em>？rn请看下面:..........rnrn[code=SQL]WITH CarPartsCTE AS (SELECT 物料编号, 用量rn FROM B_物料清单rn WHERE (产品编号 = N'1001001-1000')rn UNION ALLrn SELECT B_物料清单_1.物料编号, CarPartsCTE_2.用量 * B_物料清单_1.用量 AS Expr1rn FROM CarPartsCTE AS CarPartsCTE_2 INNER JOINrn B_物料清单 AS B_物料清单_1 ON CarPartsCTE_2.物料编号 = B_物料清单_1.产品编号)rn SELECT 产品编号, 部门编号, 分钟rn FROM (SELECT TOP (100) PERCENT N'1#1001001-1000' AS 产品编号, B_加工中心.部门编号, SUM(B_加工流程.合计分钟) rn * 480 AS 分钟rn FROM B_加工中心 INNER JOINrn B_加工流程 ON B_加工中心.加工编号 = B_加工流程.加工编号 INNER JOINrn (SELECT TOP (1) 产品编号, 1 AS 用量rn FROM B_物料清单 AS B_物料清单_2rn WHERE (产品编号 = N'1001001-1000')rn UNION ALLrn SELECT 物料编号, SUM(用量) AS 单耗rn FROM CarPartsCTE AS CarPartsCTE_1rn WHERE (NOT EXISTSrn (SELECT 1 AS Expr1rn FROM A_物料 AS A_物料_1rn WHERE (物料编号 = CarPartsCTE_1.物料编号)))rn GROUP BY 物料编号) AS tb ON B_加工流程.产品编号 = tb.产品编号rn GROUP BY B_加工中心.部门编号rn ORDER BY B_加工中心.部门编号) AS tb2rn WHERE (NOT (产品编号 INrn (SELECT CONVERT(nvarchar, 销售订单编号) + N'#' + 产品编号 AS Expr1rn FROM PO_部门排产)))[/code]

这条sql语句可以优化吗？

select * from tablename where mykey=123 || mykey=456rnrn可以<em>优化</em>吗？rn求解释，求<em>优化</em>原理。

求帮忙优化我这条sql语句

首先说一下下面<em>sql</em>语句要用到的表的基本情况：n一、有三张表，一张叫equipment，一张叫sensor，一张叫sensorInputDatan二、三表关系是equipment下有多个sensor，sensor下有多个sensorInputdata，所以sensor下有一个equipment的主键quipmentId做外键，sensorInputData下也有一个sensor的主键sensorId做外键，其他都是各自表的属性，应该能比较清晰的看出来n下面是我的查询代码：n```n select nequipment.equipmentName as 设备,nequipment.longitude as 经度,nequipment.latitude as 纬度,nequipment.equipmentType as 设备类型,ndata.数据类型 as 数据类型,nConvert(decimal(18,2),data.数据) as 数据,ndata.单位 as 单位,nconvert(varchar(20), data.日期,120) as 日期 nfrom EquipmentMessage as equipmentnleft joinn(n select n sensor.sensorUnit as 单位,n sensorData.value as 数据,n sensorData.date as 日期,n sensorData.valueType as 数据类型,n sensor.equipmentId as equipmentId n from Sensor as sensor n join SensorInputData as sensorData on sensor.sensorId=sensorData.sensorId n and not exists n (n select 1 from SensorInputData as t where t.sensorId=sensorData.sensorId and t.date>sensorData.daten )n) as data on equipment.equipmentId=data.equipmentId nn```n左连接是查出传感器（sensor表）与传感器的第一<em>条</em>数据（sensorInputData表）nnn

这条SQL有没优化余地

explain select a.* from dede_taglist1 a where not exists (select 1 from dede_taglist b where a.tid=b.tid and a.aid=b.aid)rnrn1 PRIMARY a ALL 388199 Using wherern2 DEPENDENT SUBQUERY b ref a a 8 caiji1.a.tid,caiji1.a.aid 1 Using indexrnrn2个表的结构是一样的，都有40万<em>条</em>记录。查询出dede_taglist1中存在，但是在dede_taglist中不存在的某几<em>条</em>记录。

这条SQL语句怎么优化，马上给分

select room.id as id ,room.roomid as roomid ,device.wychargemeterdeviceid as bh ,record.valuediff as actualvalue,record.approtion as approtion,record.value as value ,record.meterdevicerecordid as meterdevicerecordid ,device.chargemeterdeviceid as chargemeterdeviceid ,rndevice.beishu as bs , param.lowdegree,param.firstrange,param.secondrange,param.thirdrange,rnparam.neededcalculatefee ,param.normalprice,param.firstrangeprice,param.secondrangeprice,param.thirdrangeprice , record.addvalue as addvalue from wy_room as room inner join jf_chargemeterdevice as device using(id) rninner join jf_chargemeterparam as param using (chargemeterparamid) rnleft join jf_chargemeterdevicerecord as record on(device.chargemeterdeviceid=record.chargemeterdeviceid rnand param.chargemeterparamid = record.chargemeterparamid ) rnleft join jf_calculaterecord as cal using (calserial) rnwhere room.buildingareaid='0613021207_1000000000000002462' and room.buildingid='0724044445_1000000000000031287' rnand device.type='电表' and cal.calserial '0918094046_1000000000000053836' order by room.roomidrnrn求教高手，这<em>条</em>语句怎么<em>优化</em>？rn数据量少的时候，没有问题，数据多时CPU跑到100%，这是怎么拉rnrn高手<em>帮</em><em>帮</em>我好吗？解决马上给分rn先谢谢啦rnrn

看这条sql优化应该怎么做

SET @ins =1;rnSET @begcre = '2015-02-01';rnSET @endcre = '2015-02-31';rnrnSELECT patients.`pat_first_name` AS '姓氏' ,rn patients.pat_last_name AS '名' ,rn patients.pat_gender AS '性别' ,rn patients.pat_dob AS '出生日期' ,rn studies.stu_id AS '研究编号',rn institutions.ins_id AS '研究机构' ,rn studies.cre_date AS '扫描时间' ,rn NULL AS '是否收费',rn NULL AS '健康状况' ,rn NULL AS '完成情况'rnFROM rnstudies,institutions,patientsrnWHERErnstudies.cre_date BETWEEN @begcre AND @endcrernANDrninstitutions.ins_id = @ins; rn

求个大佬给这条sql优化下

select distinct d.IndentDetailId, i.IndentCode,isnull(i.PayTime,i.CreateTime) as FundToTime,rnd.FundMoney,i.IndentType,i.BidderName as BidderName,p.TendProjectNo rn,case when isnull(p.IsSection,0)=1 then s.SectionNo when ISNULL(p.IsSection,0)=2 then s.SectionNo else '' end as BidSectionNo rn,l.DeptName ,i.BidderId as BidCompanyId,rnbr.ContactUser as BidContractUser, rnbr.MobileTelephone as BidContractTel,rnbr.EMail as BidEmail ,p.TendAgencyId,p.TendProjectName,l.TendFileFeeWay rnfrom rnT_BidRegistRecord br right join T_Indent i on (i.IndentCode = br.OrderNo or i.IndentCode=br.PretrialOrderNo)rnleft join T_IndentDetail d on d.IndentId=i.IndentIdrnleft join T_BidSection s on d.BidSectionId=s.BidSectionIdrnleft join T_TendProject p on s.TendProjectId=p.TendProjectIdrnleft join T_TendProjectPlan l on s.TendProjectId=l.TendProjectIdrnwhere 1=1 and i.IsFund is not null and i.AgencyId = '2E858978-A7C0-4D99-A602-967A31FB4828' rnrn

大家看看这条 SQL 语句能不能优化！！！

有两个表 ：rn一个医生表，里面包含医院名称。 rn一个护士表，里面包含医院名称。rn我想查找每一个医院有多少医生，有多少护士。但是有的医院可能只登记了医生，有的医院只登记了护士。于是我作了两个外部连接并且将结果联合。rnrn请问有没有更好的方法，或者语句更加<em>优化</em>。rnrnselect * from(rnselect b.unitname , a.intNurNum , b.intDocNum from rn(select unitname , count(*) as intNurNum from tbnur group by unitname ) arn,(select unitname , count(*) as intDocNum from tbdoc group by unitname ) brn where a.unitname(+) = b.unitname rn union rnselect a.unitname , a.intNurNum , b.intDocNum from rn(select unitname , count(*) as intNurNum from tbnur group by unitname ) arn,(select unitname , count(*) as intDocNum from tbdoc group by unitname ) brn where a.unitname = b.unitname (+) rn ) ttb where ttb.unitname like '%医院%' order by unitnamern

看看这条sql语句怎样优化？

select acct_bran_code, acct_name,cust_no, sum(y_begin_bal),rn sum(m_begin_bal), sum(rmb_acct_bal) rnfrom ( select acct_bran_code, acct_name, cust_no,y_begin_bal, rn m_begin_bal, rmb_acct_bal rn from plb_curr_dep_h rn where (( sub_code like '201%' rn and acct_name not like '%财政%' rn and acct_name not like '%核算中心%' rn and acct_name not like '%社保%') rn or sub_code not like '201%') rn and acct_flag = '1'rn and curr_date='2008-01-06' rn union all rn select acct_bran_code, acct_name, cust_no, y_begin_bal, m_begin_bal, rmb_acct_bal rn from plb_fixed_dep_h rn where sub_code not like '251%' rn and (( sub_code like '205%'rn and acct_name not like '%财政%' rn and acct_name not like '%核算中心%' rn and acct_name not like '%社保%' ) rn or sub_code not like '205%') rn and acct_flag = '1'rn and curr_date='2008-01-06' )rngroup by acct_bran_code,cust_no,acct_name rnhaving sum(rmb_acct_bal)>=5000000 rnorder by sum(rmb_acct_bal) desc rnrn字段说明：rnacct_bran_code char(10) //机构码rnacct_name char(60) //客户名rncust_no char(32) //客户号rny_begin_bal number(16,2) //年初余额rnm_begin_bal number(16,2) //月初余额rnrmb_acct_bal number(16,2) //日余额rnsub_code char(8) //科目号rncurr_date data //当前日期rnrnplb_curr_dep_h是活期表，plb_curr_dep_h是定期表rn该语句的意图是按客户号统计余额大与500万的客户，（同一客户号可以开多个账户，将定期和活期的加起来），并按语句所示排除一些客户。rn高手门看看，看如何能<em>优化</em>，是建索引还是改变语句结构？如果要建索引，怎样建，因为语句中要用到not like ，建索引是不是也无效？rn

这条SQL语句怎么优化，提高执行效率

这<em>条</em>语句的执行效率有点底，当数据量有不到三千行的时候需要1.2秒，希望能<em>优化</em>一点rn语句可能有点长，希望大家能解答，最好能写出来源语句rnrnrnSELECT COUNT(DISTINCT T5.`ID`)rn FROMrn `REL_LHFUNC_TABLE` AS T5rn INNER JOIN `FUNCTION_TABLE` AS T6 ON T5.`FUNC_ID` = T6.`ID`rn INNER JOIN `LH_TABLE` AS T7 ON T5.`LH_ID` = T7.`ID`rn LEFT JOIN `CLOSED_TABLE` AS T8 ON T5.`LH_ID` = T8.`PROJ_ID`rn ANDrn T8.`PROJ_TYPE` = 'LH'rn WHERErn T5.`ID` INrn (rn SELECTrn DISTINCT T3.`ID`rn FROMrn `REL_LHFUNC_TABLE` AS T3,rn (rn (SELECTrn DISTINCT T2.`FUNC_ID` AS FUNC_ID,rn T1.`UP_ID` AS LH_IDrn FROMrn `REL_TABLE` AS T1, `PH_TABLE` AS T2rn WHERErn T1.`UP_TYPE` = 'LH'rn ANDrn T1.`DOWN_TYPE` = 'PH'rn ANDrn T2.`ID` = T1.`DOWN_ID`rn ORDER BY T2.`FUNC_ID`, T1.`UP_ID`rn )rn UNIONrn (SELECTrn DISTINCT T2.`FUNC_ID` AS FUNC_ID,rn T1.`UP_ID` AS LH_IDrn FROMrn `REL_TABLE` AS T1, `UI_TABLE` AS T2rn WHERErn T1.`UP_TYPE` = 'LH'rn ANDrn T1.`DOWN_TYPE` = 'UI'rn ANDrn T2.`ID` = T1.`DOWN_ID`rn ORDER BY T2.`FUNC_ID`, T1.`UP_ID`rn )rn ) AS T4rn WHERErn T3.`FUNC_ID` = T4.FUNC_IDrn ANDrn T3.`LH_ID` = T4.LH_IDrn )rn ANDrn T8.`ID` IS NULL

高手帮忙优化这条SQL语句，难!

K2.dbo._worklist数据量是20万<em>条</em>，DocumentReceive 10万<em>条</em>，其他表均在200以下 rnSQL语句如下：rnSELECT a.procID,b.ProcName,b.ProcSetID,FROM (SELECT DISTINCT procID FROM K2.dbo._ProcInst WHERE ID IN(SELECT DISTINCT DocumentReceive.ProcInstID rn FROM K2.dbo._worklist,DocumentReceive rn WHERE K2.dbo._worklist.[User]='chenxh14' AND DocumentReceive.Exigence='平件' AND K2.dbo._worklist.Status IN(0,1) AND K2.dbo._worklist.ProcInstID rn NOT IN(SELECT ProcInstID FROM DeletedPendingWorkListItem WHERE Status=0 UNION SELECT ProcInstID FROM DeletedPendingWorkListItem WHERE Status=2)AND K2.dbo._worklist.ProcInstID=DocumentReceive.ProcInstID))a,Process b WHERE a.procID=b.procID ORDER BY b.ProcSetID ASCrn在查询分析器里面运行了3分钟都没有出结果，但是我把rnSELECT DISTINCT procID FROM K2.dbo._ProcInst WHERE ID IN(SELECT DISTINCT DocumentReceive.ProcInstID rn FROM K2.dbo._worklist,DocumentReceive rn WHERE K2.dbo._worklist.[User]='chenxh14' AND DocumentReceive.Exigence='平件' AND K2.dbo._worklist.Status IN(0,1) AND K2.dbo._worklist.ProcInstID rn NOT IN(SELECT ProcInstID FROM DeletedPendingWorkListItem WHERE Status=0 UNION SELECT ProcInstID FROM DeletedPendingWorkListItem WHERE Status=2)AND K2.dbo._worklist.ProcInstID=DocumentReceive.ProcInstID)rn单独拿出来只需要三秒钟(结果只有4行)，为什么 SELECT a.procID,b.ProcName,b.ProcSetID,FROM a,Process b WHERE a.procID=b.procID ORDER BY b.ProcSetID ASC这句要执行那么长时间呢？rn高手出来<em>帮</em>下忙啊!!!!!!rn

这条存储过程怎么样优化

忽然发现程序中有一个存储过程 速度很慢rn还能<em>优化</em>不 谢谢rn[code=SQL]rnrnCREATE PROCEDURE [dbo].[UpdateModifyOrder]rn(rn @OrderDetailID int,rn @UpdateAmount int,rn @Operator varchar(20)rn)rnASrnBegin Tran rnDECLARE @RowCount intrnSELECT @RowCount=COUNT(*) FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailIDrnIF @RowCount>0rn BEGINrn SELECT @RowCount=COUNT(*) FROM TModifyOrder WHERE FOrderDetailID=@OrderDetailIDrn IF @RowCount>0rn BEGINrn UPDATE TModifyOrder SET FOperator=@Operator WHERE FOrderDetailID=@OrderDetailID rn ENDrn ELSErn BEGINrn INSERT INTO TModifyOrder SELECT FOrderMasterID,FOrderDetailID,FBookID,FAmount,'',GETDATE()rn FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailIDrn UPDATE TModifyOrder SET FOperator=@Operator WHERE FModifyID=@@IDENTITYrn ENDrn UPDATE TOrderDetail SET FAmount=@UpdateAmountrn END rnIf @@Error <> 0rn Beginrn RollBack Tranrn Return -1rn EndrnElsern Beginrn Commit Tran rn End rn[/code]

帮忙优化这条语句

select a.m_id,a.stock_gd2 from mact a rnwhere a.sys_id=(select min(b.sys_id) from mact b rnwhere a.m_id=b.m_id and convert(char(10),b.a_dt,126)>='2004-07-01')rnrnunion rnrnselect a.m_id,a.stock_gd3 from mact a rnwhere a.sys_id=(select max(b.sys_id) from mact b rnwhere a.m_id=b.m_id and convert(char(10),b.a_dt,126)='2004-07-01')rnrn这是一个物料流水表：mact。 stock_gd2 ：是期初数量 stock_gd3：是期末数量rn我要查出七月份的期初数量 。注意：当七月份无流动时。把最接近7月1日的纪录的期末数作rn为7月的期初数。rn我这<em>条</em>语句数据每问题，速度奇慢。高手们<em>帮</em>忙<em>优化</em>

如何优化这条语句

[code=SQL]这<em>条</em>语句只适合有主键的表，我有一个表有两个字段 WTPROD 和 WTTOOL合在一起才是唯一的。下面这<em>条</em>语句怎样改才适合？rn这<em>条</em>语句是我分页的核心语句，决定了整个页面的速度和效率。rnStringBuilder <em>sql</em>2 = new StringBuilder( "Select Top " + PageSize + " * from I_ToolPlan where WTPROD not in(select top " + PageSize * CurrentPage + " WTPROD from I_ToolPlan order by WTPROD desc) "); rnrn[/code]

这条语句怎么优化

SELECT sale_info.t_bank,dep.dep_name, dep1.dep_name AS X, product.product_name, SUM （sale_info.product_num) AS product_num_cntrn FROM sale_info rnINNER JOIN dep as dep1 ON sale_info.t_bank = dep1.dep_id rnINNER JOIN dep ON sale_info.dep_id = dep.dep_id rnINNER JOIN product ON LEFT(sale_info.t_date, 7) = product.ny and sale_info.product_id = product.product_id rnWHERE (sale_info.check_sym = '有效') rn group by product.product_name,sale_info.t_bank ,dep.dep_name ,dep1.dep_namernrn union rnrn select sale_info.t_bank,null,dep.dep_name, product.product_name,0 rnfrom sale_info rninner join dep on sale_info.t_bank=dep.dep_id rninner join product on product.ny>= '2009-09-01' AND product.ny))rnrn数据量比较大 ，页面出现“超时时间已到。在操作完成之前超时时间已过或服务器未响应”

如何优化这条查询语句！

SELECT TOP 20 * FROM (rn SELECT TOP 20 * FROM (SELECT TOP rn 20 * FROM (SELECT TOP 23700 * FROM Sign_StudentInfo ORDER BY ID ) DERIVEDTBL ORDER BY ID DESC ) DERIVEDTBL ORDER BY ID ) DERIVEDTBL rnrnrn需要时间！30妙左右。rn详情 ：rnhttp://community.csdn.net/Expert/topic/3201/3201919.xml?temp=.2758142rnrn如何<em>优化</em>?时间能<em>优化</em>到2秒左右就好了

这条语句能不能优化

这<em>条</em>语句能不能<em>优化</em>rnDAILY_VW_temp 每天从别的服务器传输过来rn大约1千5百万<em>条</em>rnVW_temp 大约1亿5千万<em>条</em>rn现在这<em>条</em>语句用时43589秒rnVW_temp里有个num索引rnrnDELETE VW_temprnWHERE num INrn(SELECT DISTINCT numrn rnFROM DAILY_VW_temp)

请高手帮我完善这条SQL语句

小弟的“资料”表中有一个名为“邮箱”的字段，里面是用来放邮箱地址的，如：aaa@bbb.comrnrnrn我想用SQL语句找到不符合格式的邮箱地址，其中包括“空值、没有字符@、字符长度小于6”rnrn我已写出一<em>条</em>SQL语句：select * from 资料 where 资料.邮箱 is nullrnrn这是用来找空值的，那其他两个<em>条</em>件（没有字符@、字符长度小于6）如何也添加进那<em>条</em>语句中呢？rnrn

请帮我解释一下这条SQL语句：

int nStartIndex = dgrdTopicList.VirtualItemCount-nPageIndex * dgrdTopicList.PageSize;rnint nEndIndex = nStartIndex - dgrdTopicList.PageSize ;rn rnstring sSQL = "select * from (select top " + dgrdTopicList.PageSize + " * from (select top " + (nEndIndex+1) + " * from topic order by id) t order by id desc) t2 order by id desc";rn rn是思归给我的一个分页的例子(http://expert.csdn.net/Expert/topic/1311/1311406.xml?temp=.6506616)里面的；rn其中的 t,t2是什么用法？如果再<em>帮</em>我讲讲nStartIndex，nEndIndex 的意义就更好了，谢谢！

大家帮我看看这条sql 语句，谢谢！

with adoquery1 dorn beginrn close;rn <em>sql</em>.Clear;rn <em>sql</em>.Add('insert into axc (a) values(9999)');rn open;rn end;rn为什么这<em>条</em>语句会报错，而值实际上已经添加到数据库中了！

帮我看看这条SQL语句，多谢！！！

两张表：rn日汇总表1：单号、总计数量、总计金额rn日报表1：编号、单号、名称、车型、车号、发动机、数量、单价、金额rn计算金额公式：rn金额=数量*单价rn按单号分组计算出总计数量、总计金额：rn总计数量=SUM（数量）rn总计金额=SUM（金额）rnrn提示我：“FROM子句语法错误”rn下面是代码：rnAdodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\JieJieCar.MDB;Persist Security Info=False"rnrnAdodc1.RecordSource = "select 编号,日报表1.单号,名称,车型,车号,发动机,数量,单价,日汇总表1.总计数量=sum(数量),日汇总表1.总计金额=sum(金额),金额=数量*单价 from 日报表1,日汇总表1 where 日汇总表1.单号=日报表1.单号 group by 日汇总表1.单号"rnrnSet DataGrid1.DataSource = Adodc1

？？？会者不难，帮我看看这条SQL语句？？？

主从表，rn欲用一<em>条</em>语句，取出主表记录内容及相对于该主表记录时，从表的记录数量，在MS SQL SERVER中，可以这样：rnselect m.*,d.cc from tab_reportmain m,(select f_reportno,count(*) as cc from tab_reportdata group by f_reportno) d where m.f_no=d.f_reportnorn但该语句在SYBASE中不能执行，rn>>(select f_reportno,count(*) as cc from tab_reportdata group by f_reportno) drn上面这<em>条</em>语句好像就有问题了。rnrn刚刚接触SYBASE，rn很不熟悉，rn请熟悉的朋友<em>帮</em>我看看这<em>条</em>语句，rn不胜感激!

网络工程图书管理grails java web下载

网络工程图书管理 网络工程图书管理 网络工程图书管理 相关下载链接：[url=//download.csdn.net/download/feiyingyinling/2534666?utm_source=bbsseo]//download.csdn.net/download/feiyingyinling/2534666?utm_source=bbsseo[/url]

WinMerge 代码对比软件下载

当您有两个以上的内容相似的文字文件时，WinMerge就可以立刻派上用场，帮您检查两个文件里头不同之处。如您有写过程序就会了解，当您在改版时，在新的程序码与旧的程序码之间到底有哪些不同之处，的确会让您伤一些脑筋。或者您曾撰写两份以上的文字文件，内容稍有差异，但要从头查起，可能会相当累人，这时候就可以利用WinMerge来帮您自动比对。WinMerge会将两个文件内容做比对，并在相异之处以高亮度的方式显示，让使用者可以很快的查知，并且您可以直接让左方的文件内容直接覆盖至右方，或者反过来也可，这对需要常常修改文件内容的朋友来说，会是相当便利的一个功能。 相关下载链接：[url=//download.csdn.net/download/jason69181/2629716?utm_source=bbsseo]//download.csdn.net/download/jason69181/2629716?utm_source=bbsseo[/url]

2013支付宝订单系统下载

2013支付宝订单系统 支持支付宝 外加新东方PHP订单系统 多个模版 对模版不满意的建议看下教程 自己创建模版 相关下载链接：[url=//download.csdn.net/download/b376924098/5957255?utm_source=bbsseo]//download.csdn.net/download/b376924098/5957255?utm_source=bbsseo[/url]

相关热词 c#部署端口监听项目、 c#接口中的属性使用方法 c# 昨天 c#func链接匿名方法 c#怎么创建文件夹 c#从键盘接收空格 c#da/ad c#部门请假管理系统 c#服务器socket c# 默认的访问修饰符