sql 语句优化,求帮忙

Select t9.Product_ID,a.Product_Name,a.Product_BuyNum, b.Org_Price, b.Sale_Price, c.Img_Small, c.Img_Middle,
isnull((select SubShop_Price from subshop_product where subshop_id=1057 and product_id=a.product_id),b.Sug_Price) as SubShop_Price, b.Sug_Price,a.Product_SeeNum,a.AddTime,
a.orderid From
(
Select Top 30 product_id from
(
Select row_number() over(order by a.product_id desc) as rownumber,a.product_id

From Product_Info a
Inner Join Product_Price b On a.Product_ID = b.Product_ID

Inner Join SubShop_ProductRight d ON a.Class_ID = d.Class_ID AND d.SubShop_ID=1057

Where ((a.SubShop_ID is null or a.SubShop_ID=-1) or ((a.SubShop_ID=1057 or a.Is_AllShow=0 ) and Is_Audit=0))
and a.IsHidden = 0 And a.IsDelete = 0
And a.Product_Name Like '%%' and a.Product_ID
not in (select product_id from SubShop_Product where IsHidden=1 and SubShop_ID=1057)

) t
where rownumber>4170 Order By rownumber
) as t9
inner join Product_Info a on a.Product_ID=t9.Product_ID
Inner Join Product_Price b On a.Product_ID = b.Product_ID left Join Product_Detail c On a.Product_ID = c.Product_ID

rownumber>4170 这个数值会根据不同页数变化,求解怎么优化,这个占用CPU很高,导致系统速度慢

4个回答

你那个算法太多了,能不能精简一下

你不要直接贴语句 把相关的表结构和功能说出来 需要达到什么样的效果
要不你这样别人只能在你的思路上进行修改 没啥作用

要看懂起码要半个小时,再写一下又要半个小时,只能呵呵了!!!!

data structure, tables relationship, foreign key, primary key.
查詢目的。這些都沒有,

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
其他相关推荐
求帮忙优化sql语句
大哥、大姐们:rn 请帮我看看,谢谢了,我就50分了,全给了。rn 要求是查出第一段时间内(我的sql里4月1号到9月1号),间隔几周(我sql里是3周),某个星期几(代码里是1和4)的日期,我这个将查询时间段拉长,间隔时间改小,就算不出来了,请帮忙优化下,多谢。[code=sql]Select tdate, weekrn From (Select to_char(c1, 'yyyymmdd') tdate,rn decode(to_char(c1, 'd'),rn '1',rn 'seven',rn '2',rn 'one',rn '3',rn 'two',rn '4',rn 'three',rn '5',rn 'four',rn '6',rn 'five',rn '7',rn 'six') weekrn From (rn --得出每个周日和往前推6天的日期rn Select Distinct c1 - Level + 1 c1rn From (rn --根据最小的日期一次往前推3周rn Select c1 + (3 * 7) *rn (Level - 1) c1rn From (rn --计算为周日的最小日期rn Select Min(c1) c1rn From (rn --构造4月1号到9月1号的日期表rn Select to_date('2013-04-01','yyyy-mm-dd') + Level - 1 c1rn From dualrn Connect By Level <=rn to_date('2013-09-01','yyyy-mm-dd') - to_date('2013-04-01','yyyy-mm-dd') + 1)rn Where to_char(c1, 'd') = 1)rn Connect By Level <=rn (to_date('2013-09-01','yyyy-mm-dd') - to_date('2013-04-01','yyyy-mm-dd')) /rn (3 * 7) + 1)rn Connect By Level < 8rn Order By c1)rn Where c1 >= to_date('2013-04-01','yyyy-mm-dd'))qrn Where Exists (Select weekrn From ( /**将某字段,由逗行分隔的值转成列**/rn Select REGEXP_SUBSTR('one,four', '[^,]+', 1, rownum) As weekrn From DUALrn Connect By ROWNUM <= LENGTH('one,four') -rn LENGTH(Replace('one,four', ',')) + 1) wrn Where w.week = q.week)[/code]
求大神帮忙 求优化下SQL语句
[code=sql]select t.htId,t1.id,t2.id,t3.id ,htNo 合同编号,visitType 回访类型,success 回访状态,buy 客户姓名,buyPhone 客户电话,seller 房东姓名,sellerPhone 房东电话,storeDegree 门店评价,transferDegree 客户评价,warrantUserId 权证评价,processDate 过户日期,visitDate 回访日期,t2.`name` 录入人,t3.`name` 录入部门,flowuser 流程人,flowdept 流程部门,signuser 签约人,t1.signdept 门店,t1.dname 门店区域,houseAddress 位置,turnRefer 转介绍,t.createDate 录入日期,t.remark 备注,t4.dPhone 店长电话rnrnfrom rn ht_visit t LEFT JOIN (select t.id,t.htNo,t.buy,t.buyPhone,t.seller,t.sellerPhone,t.processDate,t.flowuser,t.flowdept,t.signuser,t.houseAddress,t.signdept,t1.`name` as dname FROM v_ht_info t LEFT JOIN rn (select t.id,t.name,t2.p_id,t2.signdept FROM b_organs t,(select t1.id,t1.p_id,t.signdept,t1.name from v_ht_info t,b_organs t1 where t.signdept=t1.name) t2 where t.id=t2.p_id) t1 ON t.signdept=t1.`name`) t1rnrnrnON t.htId=t1.idrn LEFT JOIN b_emp t2 ON t.userId =t2.idrn LEFT JOIN b_organs t3 ON t.deptId =t3.idrn LEFT JOIN (select t.name,t.phone as dPhone,t.name1,t.sysOrgan_id,t1.name as name2,t1.id fromrn (select t.name,t.phone,t1.name as name1,t.sysOrgan_id from b_emp t JOIN b_position t1 ON t.position_id = t1.id where t1.id=116 and t.status=243) t JOIN b_organs t1 ON t.sysOrgan_id = t1.id) t4 on t1.signdept =t4.name2[/code]rnrnrn本人知识有限 写的太垃圾 求大神帮忙 优化下。还有一个问题我截图 rn[img=https://img-bbs.csdn.net/upload/201411/24/1416820635_583944.jpg][/img]rn[img=https://img-bbs.csdn.net/upload/201411/24/1416820642_242016.jpg][/img]rn一个店 出现两个店长 就会导致数据增加了同一样的记录 就只是 店长电话部一样 我现在只需要随便一个就可以了
求帮忙优化优化语句。
[code=sql]SELECT KK.WORDrn FROM KEYWORD KKrn WHERE EXISTS (SELECT KC.KIDrn FROM CLASS CC, KEYWORD_CLASS KCrn WHERE CC.CID = KC.CIDrn AND KC.KID = KK.KIDrn AND CC.NAME IN ('门户网站'))rn AND EXISTS (SELECT KC.KIDrn FROM CLASS CC, KEYWORD_CLASS KCrn WHERE CC.CID = KC.CIDrn AND KC.KID = KK.KIDrn AND CC.NAME IN ('搜索引擎'))rn AND NOT EXISTS (SELECT KC.KIDrn FROM CLASS CC, KEYWORD_CLASS KCrn WHERE CC.CID = KC.CIDrn AND KC.KID = KK.KIDrn AND CC.CID NOT IN (SELECT CID FROM CLASS WHERE CC.NAME IN ('门户网站', '搜索引擎'));[/code]
帮忙优化SQL语句
sql="select ((select sum(c.pcb_num) from storage_pcb c where c.pcb_id=a.pcb_id and c.pcb_zt='0')-(select sum(c.pcb_num) from storage_pcb c where c.pcb_id=a.pcb_id and c.pcb_zt='1')) as pcb_num,b.pcb_name,c.p_name from (select * from storage_pcb where id in (select max(id) from storage_pcb group by pcb_id)) a inner join shop_pcb b on a.pcb_id=b.id inner join shop_p_class c on c.p_id=b.pcb_class"rnrn表storage_pcb (产品出入库)rnIDrnPCB_ID (对应产品库ID)rnPCB_NUM (出入库数量)rnPCB_TIME (出入库时间)rnPCB_ZT (0入库 1出库)rnrn表shop_pcb (产品库)rnIDrnPCB_NAME (产品名称)rnPCB_CLASS (对应分类库P_ID)rnrn表shop_p_class (分类库)rnP_IDrnP_NAME (分类名称)rnrn目的就是统计出现所有产品的库存,,有没有办法优化这条SQL语句的效率呢?
帮忙优化下sql语句
signstationid,reachdatetime,signmachineid,cp是联合主键, 这是个分页的语句,才几千条数据,在数据库中查询居然要10秒,请大家帮忙优化下.rnSELECT TOP 10 *rnFROM (SELECT t1.signstationid,rn t1.reachdatetime,rn t1.signmachineid,rn t1.cp,rn t1.direction,rn t1.imageindex,rn t1.isupload,rn t1.vehiclecount,rn t1.vehiclespeed,rn t1.lanenum,rn t1.iscorrect,rn t1.vehiclelenth,rn t1.vehicletype,rn t2.signstationname,rn t3.signmachinename,rn t3.signmachineiprn FROM cpvehiclepass t1rn LEFT JOIN (SELECT signstationid,rn signstationnamern FROM signstation) t2rn ON t1.signstationid = t2.signstationidrn LEFT JOIN (SELECT signmachineid,rn signmachinename,rn signstationid,rn signmachineiprn FROM signmachine) t3rn ON t1.signmachineid = t3.signmachineidrn AND t2.signstationid = t1.signstationid) t4rn WHERE (CONVERT(VARCHAR(100),t4.reachdatetime,120)rn + t4.signstationidrn + t4.signmachineidrn + t4.cp) NOT IN (SELECT TOP 3660 (CONVERT(VARCHAR(100),t5.reachdatetime,120)rn + t5.signstationidrn + t5.signmachineidrn + t5.cp)rn FROM (SELECT t1.signstationid,rn t1.reachdatetime,rn t1.signmachineid,rn t1.cp,rn t1.direction,rn t1.imageindex,rn t1.isupload,rn t1.vehiclecount,rn t1.vehiclespeed,rn t1.lanenum,rn t1.iscorrect,rn t1.vehiclelenth,rn t1.vehicletype,rn t2.signstationname,rn t3.signmachinename,rn t3.signmachineiprn FROM cpvehiclepass t1rn LEFT JOIN (SELECT signstationid,rn signstationnamern FROM signstation) t2rn ON t1.signstationid = t2.signstationidrn LEFT JOIN (SELECT signmachineid,rn signmachinename,rn signstationid,rn signmachineiprn FROM signmachine) t3rn ON t1.signmachineid = t3.signmachineidrn AND t2.signstationid = t1.signstationid) t5rn ORDER BY t5.reachdatetime ASC)rn ORDER BY reachdatetime ASC
SQL语句帮忙优化
就是关联两张表查询,为了分别查处 一张的不同字段 且为了不出现重复记录用了以下语句是union,但查询速度巨慢,上来求助,散分求助rnrn[code=SQL]rnselect count(*) as num from (rnrnselect char(F1.createTime) as datatime, rnFP.TCODE, rnF1.enddate date,rn '01', rn 'CNY', rn F1.datarange, rn F1.startdate, rn F1.enddate, rn round(F1.growth2/100, 4), --不同rn '' as endflag rn from lippervalue_his_1M F1 rn LEFT JOIN rn ( rn SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL rn UNIONrn SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'' rn ) AS FP rn ON F1.trade_code=FP.TRADE_CODE rn where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL rn AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') rn and F1.createtime>'2011-01-02-00.16.14.609000' rn rn union rn rn select char(F1.createTime) as datatime, rn FP.TCODE, F1.enddate date, rn '02',rn 'CNY', rn F1.datarange,rn F1.startdate, rn F1.enddate, rn F1.STDDEV2, --不同rn '' as endflagrn from lippervalue_his_1M F1 rn LEFT JOIN rn ( rn SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL rn union rn SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'' rn ) AS FP rn ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULLrn AND FP.TCODE IS NOT NULL rn AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY')rn and F1.createtime>'2011-01-02-00.16.14.609000' rn rn union rn select rn char(F1.createTime) as datatime, rn FP.TCODE, rn F1.enddate date, rn '03', 'CNY', F1.datarange, F1.startdate, F1.enddate, rn F1.YIELD2, --不同rn rn '' as endflag from lippervalue_his_1M F1 rn LEFT JOIN rn (rn SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY'rn ) and F1.createtime>'2011-01-02-00.16.14.609000' rn rn union rn rn select char(F1.createTime) as datatime, FP.TCODE, F1.enddate date, '05', 'CNY', F1.datarange, F1.startdate, F1.enddate, F1.riskgrowth2, '' as endflag from lippervalue_his_1M F1 LEFT JOIN (SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') and F1.createtime>'2011-01-02-00.16.14.609000' rn rn union rn rn rn select char(F1.createTime) as datatime, FP.TCODE, F1.enddate date, '06', 'CNY', F1.datarange, F1.startdate, F1.enddate, rn rn F1.sharpe2, --不同rn rn '' as endflag from lippervalue_his_1M F1 LEFT JOIN (SELECT TRADE_CODE, TRADE_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL union SELECT TRADE_CODE, FUND_SECOND_CODE AS TCODE FROM FUND_PRODUCT WHERE DELETETIME IS NULL AND FUND_SECOND_CODE IS NOT NULL AND FUND_SECOND_CODE<>'') AS FP ON F1.trade_code=FP.TRADE_CODE where F1.DELETETIME IS NULL AND FP.TCODE IS NOT NULL AND F1.datarange in('1M','3M','6M','1Y','3Y','5Y','BY') and F1.createtime>'2011-01-02-00.16.14.609000' rn )a rnrn[/code]
SQL语句帮忙优化!!!!!!!!!
![图片说明](https://img-ask.csdn.net/upload/201703/14/1489457067_467098.png)SQL语句帮忙优化rnSQL语句帮忙优化rnSQL语句帮忙优化rnSQL语句帮忙优化!!rn主要看图
帮忙优化 SQL 语句
有一个表,其中有 Data 列和 QNO 列,现在想要通过连续的几千个 QNO 列值索引更新 Data 列。rn用如下 SQL 语句可以达到循环更新的目的。但现在的问题是 SQL 语句的执行效率太低,rnCPU 用到快 100%,双核 3G 的也用到 40%,而且要大概 7,8 秒才能执行完,有什么办法优化吗?rnrn说明一下,下面语句只是个例子,真正更新的时候每个不同的 QNO 都对应不同的 Data 值,rn但它们两者之间没有逻辑关系,只是需要通过 QNO 确定位置,把处理后的值更新到 DatarnrnDECLARE @i intrnSET @i = 0 WHILE @i < 4000 BEGINrn SET @i = @i + 1rn UPDATE AIDATArn SET Data = 100rn WHERE QNO = @irn UPDATE DIDATArn SET Data = 1rn WHERE QNO = @irnEND
求帮忙优化我这条sql语句
首先说一下下面sql语句要用到的表的基本情况: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表)与传感器的第一条数据(sensorInputData表)nnn
求sql优化语句
表里有两个子段,id,parentidrn表名为tablern外面有两个for循环,在此表里检索,请问怎么写成一条语句阿rnfor i循环 rn select id from table where parent='$arrS[$i]rn得出记录集arrB,for j循环rnselect id from table where parent='$arrB[$j]rnrn能用一条语句写出来吧,是不是能提高效率?rnrn
求优化SQL语句!!!!!!!!--------------
能优化下这个让人写吐了的SQL语句么??rnrn[code=SQL]rnUPDATE t_syukkarnSET pic_kijyun_flg ='1',--rnsyusei_flg='1',rnmae_cose = CASE WHEN UPD.mae_cose is null THEN UPD.todoke_cose ELSE UPD.mae_cose END,rnmae_jyunban =CASE WHEN UPD.mae_jyunban is null THEN UPD.todoke_jyunban ELSE UPD.mae_jyunban END,rnhoryu_kbn =CASE WHEN UPD.horyu_zone='1'rn OR UPD.horyu_sk_kbn = '1'rn OR UPD.horyu_bmp = '1'rn OR UPD.horyu_sjk_kbn = '1'rn OR UPD.horyu_sn = '1'rn OR UPD.horyu_tome = '1'rn OR UPD.horyu_irisu = '1'rn OR UPD.horyu_syaban = '1'rn OR UPD.horyu_koujyou = '1'rn OR UPD.horyu_tome = '1'rn --OR UPD.horyu_han = '1'rnTHEN '1'rnELSE '0' END,rnhoryu_cose ='0',rn-----------rnhoryu_sk_kbn ='0',rnhoryu_bmp='0',rnhoryu_sjk_kbn ='0',rnhoryu_syudou='0',rnhoryu_sn ='0',rn---------------rntodoke_cose='M001',--rntodoke_jyunban='1',--rncose_grp=UPD.cose_grp,rnpic_siji_jyun=UPD.pic_siji_jyun,rn--if SJKパターン = 'S' Thenrnsjk_ptrn=UPD.sjk_ptrn_j,rn/*rn--if SJKパターン = 'S' Thenrnsjk_ptrn=UPD.sjk_ptrn_k,rn--if SJKパターン = 'S' Thenrnsjk_ptrn=UPD.sjk_ptrn_h,rn--if SJKパターン = 'S' Thenrnsjk_ptrn=UPD.sjk_ptrn_s,rn--if SJKパターン = 'S' Thenrnsjk_ptrn='',rn*/rnhan_cd=UPD.han_cd,rnhoryu_han=CASE WHEN UPD.han_cd is null THEN '1' ELSE horyu_han END,rnarea_cd=UPD.area_cd,rnpic_syubetu=UPD.pic_syubetu,rn---jiba_bukken_kbn=,rnpic_j =CASE WHEN UPD.sk_syubetu ='A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '0' THEN '30' rnWHEN UPD.sk_syubetu = 'A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '1' AND UPD.sn_pic_kbn = '1' THEN '30' rnELSE t_syukka.pic_j END,rnrnjisseki_su=CASE WHEN UPD.sk_syubetu ='A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '0' THEN UPD.yotei_surnWHEN UPD.sk_syubetu = 'A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '1' AND UPD.sn_pic_kbn = '1' THEN UPD.yotei_surnELSE t_syukka.jisseki_su END,rnrntantou_jigyou_cd=CASE WHEN UPD.sk_syubetu ='A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '0' THEN UPD.sk_basyornWHEN UPD.sk_syubetu = 'A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '1' AND UPD.sn_pic_kbn = '1' THEN UPD.sk_basyornELSE t_syukka.tantou_jigyou_cd END,rnrntantou_cd=CASE WHEN UPD.sk_syubetu ='A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '0' THEN 'XXXX'rnWHEN UPD.sk_syubetu = 'A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '1' AND UPD.sn_pic_kbn = '1' THEN 'XXXX'rnELSE t_syukka.tantou_cd END,rnrnpic_no =CASE WHEN UPD.sk_syubetu ='A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '0' THEN 1rnWHEN UPD.sk_syubetu = 'A' AND UPD.horyukbn = '0' AND UPD.sn_kenpin_kbn = '1' AND UPD.sn_pic_kbn = '1' THEN 1rnELSE t_syukka.pic_no END,rnrnup_date = getdate()rnFROM (rnSELECT --A.sjk_kbn ,rn A.sk_basyo,A.denpyou_no,A.denpyou_gyouno,A.sk_syubetu,A.syaban,rn A.zone_kbn,A.yotei_su,rn ISNULL(B.cose_grp,'') AS cose_grp,rn ISNULL(B.pic_siji_jyun,0) AS pic_siji_jyun,rn ISNULL(B.sjk_ptrn_s,'') AS sjk_ptrn_s,rn ISNULL(B.sjk_ptrn_j,'') AS sjk_ptrn_j,rn ISNULL(B.sjk_ptrn_k,'') AS sjk_ptrn_k,rn ISNULL(B.sjk_ptrn_h,'') AS sjk_ptrn_h,rnrn ISNULL(B.sn_pic_kbn,'') AS sn_pic_kbn,rn ISNULL(m_han_pic.han_cd,'') AS han_cd,rnrn ISNULL(m_area.area_cd,'') AS area_cd,rnrn ISNULL(m_bukken_pic.pic_syubetu,'01') AS pic_syubetu,rn --ISNULL(m_bukken_pic.jiba_bukken_kbn,'0') AS jiba_bukken_kbn,rn ISNULL(m_koujyou_grp.sn_kenpin_kbn,'') AS sn_kenpin_kbn,rn A.horyu_zone,A.horyu_sk_kbn,A.horyu_bmp,A.horyu_sjk_kbn,A.horyu_syudou,rn A.horyu_tome,A.horyu_irisu,A.horyu_syaban,A.horyu_sn,A.horyu_koujyou,rn A.jisseki_su,A.pic_j,A.tantou_jigyou_cd,A.tantou_cd,A.pic_no,A.mae_cose,A.mae_jyunban,A.todoke_cose,A.todoke_jyunban,rnCASE WHEN A.horyu_zone='1'rnrnOR A.horyu_sk_kbn = '1'rnOR A.horyu_bmp = '1'rnOR A.horyu_sjk_kbn = '1'rnOR A.horyu_sn = '1'rnOR A.horyu_tome = '1'rnOR A.horyu_irisu = '1'rnOR A.horyu_syaban = '1'rnOR A.horyu_koujyou = '1'rnOR A.horyu_tome = '1'rnOR A.horyu_han = '1'rnTHEN '1'rnELSE '0' END horyukbnrn FROM t_syukka A rnrn LEFT JOIN m_cose B ON(rn A.sk_basyo = B.jigyou_cd ANDrn B.cose = '1001')rn LEFT JOIN m_han_pic ON(rn A.sk_basyo = m_han_pic.jigyou_cd ANDrn A.zone_grp = m_han_pic.zone_grp ANDrn B.cose_grp = m_han_pic.cose_grp)rn LEFT JOIN m_area ON(rn A.sk_basyo = m_area.jigyou_cd ANDrn A.zone_grp = m_area.zone_grp ANDrn B.cose_grp = m_area.cose_grp)rn LEFT JOIN m_bukken_pic ON(rn A.sk_basyo = m_bukken_pic.jigyou_cd ANDrn m_bukken_pic.cose = '1001' ANDrn m_bukken_pic.jyunban = '1')rn LEFT JOIN m_koujyou_grp ON(rn A.sk_basyo = m_koujyou_grp.jigyou_cd ANDrn A.koujyou_grp = m_koujyou_grp.koujyou_grp)rnWHERE A.sk_basyo ='GY01'rnAND A.jyusin_d ='20080808'rnAND A.todoke_cose ='T001'rnAND A.todoke_jyunban ='9'rnAND A.sjk_kbn ='1'rnAND A.pic_j ='00'rnAND A.horyu_syudou ='1'rnAND A.horyu_sjk_kbn ='1'rnAND A.horyu_sk_kbn ='1'rnAND A.horyu_bmp ='1'rnAND A.horyu_sn ='1'rnunionrnSELECT --A.todoke_cose ,rn A.sk_basyo,A.denpyou_no,A.denpyou_gyouno,A.sk_syubetu,A.syaban,rn A.zone_kbn,A.yotei_su,rn ISNULL(B.cose_grp,'') AS cose_grp,rn ISNULL(B.pic_siji_jyun,0) AS pic_siji_jyun,rn ISNULL(B.sjk_ptrn_s,'') AS sjk_ptrn_s,rn ISNULL(B.sjk_ptrn_j,'') AS sjk_ptrn_j,rn ISNULL(B.sjk_ptrn_k,'') AS sjk_ptrn_k,rn ISNULL(B.sjk_ptrn_h,'') AS sjk_ptrn_h,rnrn ISNULL(B.sn_pic_kbn,'') AS sn_pic_kbn,rn ISNULL(m_han_pic.han_cd,'') AS han_cd,rnrn ISNULL(m_area.area_cd,'') AS area_cd,rnrn ISNULL(m_bukken_pic.pic_syubetu,'01') AS pic_syubetu,rn -- ISNULL(m_bukken_pic.jiba_bukken_kbn,'0') AS jiba_bukken_kbn,rn ISNULL(m_koujyou_grp.sn_kenpin_kbn,'') AS sn_kenpin_kbn,rn A.horyu_zone,A.horyu_sk_kbn,A.horyu_bmp,A.horyu_sjk_kbn,A.horyu_syudou,rn A.horyu_tome,A.horyu_irisu,A.horyu_syaban,A.horyu_sn,A.horyu_koujyou,rn A.jisseki_su,A.pic_j,A.tantou_jigyou_cd,A.tantou_cd,A.pic_no,A.mae_cose,A.mae_jyunban,A.todoke_cose,A.todoke_jyunban,rnCASE WHEN A.horyu_zone='1'rnOR A.horyu_sk_kbn = '1'rnOR A.horyu_bmp = '1'rnOR A.horyu_sjk_kbn = '1'rnOR A.horyu_sn = '1'rnOR A.horyu_tome = '1'rnOR A.horyu_irisu = '1'rnOR A.horyu_syaban = '1'rnOR A.horyu_koujyou = '1'rnOR A.horyu_tome = '1'rnOR A.horyu_han = '1'rnTHEN '1'rnELSE '0' END horyukbnrn FROM t_syukka A rnrn LEFT JOIN m_cose B ON(rn A.sk_basyo = B.jigyou_cd ANDrn B.cose = '1001')rn LEFT JOIN m_han_pic ON(rn A.sk_basyo = m_han_pic.jigyou_cd ANDrn A.zone_grp = m_han_pic.zone_grp ANDrn B.cose_grp = m_han_pic.cose_grp)rn LEFT JOIN m_area ON(rn A.sk_basyo = m_area.jigyou_cd ANDrn A.zone_grp = m_area.zone_grp ANDrn B.cose_grp = m_area.cose_grp)rn LEFT JOIN m_bukken_pic ON(rn A.sk_basyo = m_bukken_pic.jigyou_cd ANDrn m_bukken_pic.cose = '1001' ANDrn m_bukken_pic.jyunban = '1')rn LEFT JOIN m_koujyou_grp ON(rn A.sk_basyo = m_koujyou_grp.jigyou_cd ANDrn A.koujyou_grp = m_koujyou_grp.koujyou_grp)rnWHERE A.sk_basyo ='GY01'rnAND A.jyusin_d ='20080808'rnAND A.todoke_cose ='T001'rnAND A.todoke_jyunban ='9'rn/********rnAND A.sjk_kbn ='1'rnAND A.pic_j ='00'rnrnAND A.horyu_syudou ='1'rnAND A.horyu_sjk_kbn ='1'rnAND A.horyu_sk_kbn ='1'rnAND A.horyu_bmp ='1'rnAND A.horyu_sn ='1'rn*/rn) AS UPDrnWHERE t_syukka.sk_basyo = UPD.sk_basyornAND t_syukka.denpyou_no =UPD.denpyou_nornAND t_syukka.denpyou_gyouno =UPD.denpyou_gyounornAND t_syukka.sk_syubetu =UPD.sk_syubeturnAND t_syukka.syaban =UPD.syabanrn[/code]
求SQL语句优化
select * rnfrom t_weblog a where bk_abbPaytime between '2008-01-13' and '2008-01-19' rnand bk_payCode not in(select bk_paycode from t_banklog a rnwhere bk_abbPaytime between '2008-01-13' and '2008-01-19')rnrn每一个查询单独查的话速度都在0秒,就是Not In的时候要几分钟。rn求一下语句上面的优化。
求优化的sql语句
现在有两张表:rn1.新闻表News:rnNewsID Title ...rn2.新闻评论表NewsCommentsrnCommentID NewsID PosterID ...rn新闻评论表中的NewID是外键,对应新闻表中的NewID。rnrn一个用户可以对一条新闻评论多次。rn现在我想找到PosterID为2的用户评论过的新闻,那肯定要用到distinct,怎么写效率最高。rnrn如果不做distinct的话,sql如下,但是选择出来的数据是有重复的。rn[code=SQL]select News.* from NewsComments left join News on NewsComments.NewsID = NewsComments.NewsIDrnwhere NewsComments.PosterID = 2;[/code]rnrn还有另外一个问题,NewsComments的数据量是News的n倍(n>50),用NewsComments left join News和News left join NewsComments在性能上哪个更加好。rnrn谢谢大家!时刻关注!rn
求sql语句 优化
正常是这么查找rnselect * from aaa where id in (1,2,3,4);rnrn用什么方法可以替代 in 提高查找效率
求优化 sql语句
rnrn三张表 T_OrderInfo T_OrderDtl_Info T_Counter_Infornrn主外键rnrnT_OrderInfo.OrderId=T_OrderDtl_Info.OrderIdrnrnT_OrderDtl_Info.CounterId=T_Counter_Info.CounterIdrnrnrnrnrnrnsql语句:rn[code=SQL]rnselect rnT_OrderInfo.OrderId,T_OrderInfo.OrderPrice,T_OrderInfo.InsertTimernfrom rndbo.T_OrderInfo,dbo.T_OrderDtl_Info,T_Counter_Infornwhere T_OrderInfo.OrderId=T_OrderDtl_Info.OrderIdrnand T_OrderDtl_Info.CounterId=T_Counter_Info.CounterIdrnand T_Counter_Info.CounterId=1rnand T_OrderInfo.InsertTime < getdate()rnand T_OrderInfo.InsertTime > (getdate()-1)rnrn[/code]rnrn这个 怎么优化????
求SQL语句的优化。
以下代码,总的就是在指定范围内,那些字段加起来在t_Xm_SalePrice 表没有记录的数据。请大神帮忙优化下,查不出数据 ,显示执行计划又是正常。谢谢rn[code=sql]rn SELECT DISTINCT c.FNumber 物料代码,c.FName 物料名称,c.FModel 物料规格,b.FMapNumber 客户对应代码,d.FNumber 客户代码,'' 价格类型,1 [销货量(从)],10000000 [销货量(到)], b1.FNumber 计量单位代码,a1.FName 币别,0 [报价],e1.FName 业务员,CONVERT(DATETIME,'1900-01-01') 生效日期,CONVERT(DATETIME,'2100-01-01') 失效日期,'' 备注,d.fname 客户名称,a.FDate 订单日期 ,a.FInterID,a.FBillNo 订单编号,ss1.fname 销售模式 rn FROM dbo.SEOrder a INNER JOIN dbo.SEOrderEntry b ON a.FInterID = b.FInterID rn INNER JOIN dbo.t_MeasureUnit b1 ON b.FUnitID=b1.FMeasureUnitID rn INNER JOIN dbo.t_ICItem c ON b.FItemID = c.FItemID INNER JOIN dbo.t_Organization d ON a.FCustID=d.FItemID rn INNER JOIN dbo.t_Currency a1 ON a.FCurrencyID=a1.FCurrencyID LEFT JOIN t_Item t7933 ON b.FEntrySelfS0150 = t7933.FItemID rn LEFT JOIN dbo.t_Emp e1 ON a.FEmpID=e1.FItemID rn LEFT JOIN dbo.t_SubMessage ss1 ON ss1.FInterID=a.FHeadSelfS0138 WHERE ISNULL(d.F_120,0)=1 and t7933.fname not like '%取消%' rn AND a.FDate >='2014-09-23' and a.FDate<='2014-10-23' AND rn CONVERT(VARCHAR(15),a.FCustID)+CONVERT(VARCHAR(15),b.FItemID)+CONVERT(VARCHAR(15),a.FCurrencyID)+b.FMapNumber rn NOT IN (rn SELECT CONVERT(VARCHAR(15),FCustID)+CONVERT(VARCHAR(15),FItemID)+CONVERT(VARCHAR(15),FCurrency)+FMapNumber rn FROM dbo.t_Xm_SalePrice WHERE CONVERT(VARCHAR(10),GETDATE(),120)>=FQuoteTime rn AND CONVERT(VARCHAR(10),GETDATE(),120)<=FDisTime ) AND a.FInterID=(SELECT min(a1.FInterID) rn FROM seorder a1 INNER JOIN dbo.SEOrderEntry b1 ON a1.FInterID = b1.FInterID rn WHERE a1.FCustID=a.FCustID AND b1.FItemID=b.FItemID AND b1.FMapNumber=b.FMapNumber and a1.fdate>='2014-09-23'rn ) rn[/code]
求sql语句优化
rn[code=SQL]rnselect invent.cDepotId,invent.cprodid rn,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity rn,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCentiare rn,case when in_iSuAmt is null then 0 else in_iSuAmt end in_iSuAmt rn,case when ou_iQuanTity is null then 0 else ou_iQuanTity end ou_iQuanTity rn,case when ou_iSuCentiare is null then 0 else ou_iSuCentiare end ou_iSuCentiare rnfrom (rnselect depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit rnfrom invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId rn)invent left join (rnselect cDepotId,paperlog.cProdId,sum(iQuanTity)in_iQuanTity,sum(iSuCentiare)in_iSuCentiare,sum(iSuAmt)in_iSuAmt rnfrom paperlog,papClass rnwhere paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='入库' rngroup by cDepotId,cProdId) inPaperlog on invent.cProdId=inPaperlog.cProdid and invent.cDepotId=inPaperlog.cDepotId rnleft join (rnselect cDepotId,paperlog.cProdId,sum(iQuanTity)ou_iQuanTity,sum(iSuCentiare)ou_iSuCentiare rnfrom paperlog,papClass rnwhere paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='出库' rngroup by cDepotId,cProdIdrn)ouPaperlog on invent.cProdId=ouPaperlog.cProdId and invent.cDepotId=ouPaperlog.cDepotId rnwhere in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0 rnorder by invent.cprodid,invent.cDepotIdrn[/code]rn这个语句,执行不了,Sql服务器好像死循环了rn如果把rnwhere in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0 rn这句给删除了,查询出来的记录也才80000多条rn为什么会这样呢?rnrn求师兄师姐给指点一下,谢谢!
sql 语句求优化
SELECT zdrq 日期,djbh 单号,c.ckbh 仓库编号,CKMC 仓库,H.CFE1 提货方式,H.CFE2 付款方式,rn(select top 1 djbh from k_ckw cw left join k_ck ct on cw.link=ct.link where cw.fhid=w.id) 出库单号,rnk.khbh 客户编号,K.KHMC AS 客户名称,y.ywbh 业务编号,Y.YWXM AS 业务员 ,W.SPBH AS 商品编号,P.SPMC AS 商品名称,W.GGXH AS 规格型号,rnP.JLDW 单位,P.SPCD ,P.SPCZ ,p.spbz ,p.splb,p.gsz 根实重,rnw.hwbh,W.SCPH 入库批号,rnw.cfe1,w.cfe2,w.cfe3,w.cfe4,w.cfe5,w.cfe6,w.cfe7,w.cfe8,rn case when H.FHFS=0 THEN '合同销售'rn when H.FHFS=1 THEN '正常销售'rn when H.FHFS=2 THEN '内调销售'rn when H.FHFS=3 THEN '直销销售'rn when H.FHFS=4 THEN '其它销售'rn when H.FHFS=5 THEN '退货销售'rn else '' end 销售类别,rnrn ISNULL(W.SPSL,0) AS SPSLrn ,ISNULL(W.DFE1,0) AS DFE1rn ,ISNULL(W.DFE2,0) AS DFE2rn ,ISNULL(W.DFE3,0) AS DFE3rn,DBO.ISZERO(W.SPJE+W.SSSE,W.SPSL) AS SPSL价rn,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE1) AS DFE1价rn,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE2) AS DFE2价rn,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE3) AS DFE3价rn--,isnull(dbo.getFHFYDJ(w.link,2),0) 费用单价rn--,isnull(dbo.getFHFYDJ(w.link,2)*w.spsl,0) 费用金额rn,ISNULL(W.SPJE,0) AS SPJErn,ISNULL(W.SSSE,0) AS 税额rn,ISNULL(W.SSSE+W.SPJE,0) AS JESErn--,ISNULL(W.SSSE+W.SPJE,0)+isnull(dbo.getFHFYDJ(w.link,2)*w.spsl,0) AS 金额总计rn,isnull(w.SPCB,0) as SPSL成本价rn,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE1,0)) as DFE1成本价rn,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE2,0)) as DFE2成本价rn,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE3,0)) as DFE3成本价rn,isnull(w.SPcb*w.spsl,0) as 成本SPJErn,isnull((select sum(spsl) from k_ckw where fhid=w.id),0) 出库数量rn,isnull((select sum(dfe1) from k_ckw where fhid=w.id),0) 出库支数rn,isnull((select sum(dfe2) from k_ckw where fhid=w.id),0) 出库过磅rn,isnull((select sum(dfe3) from k_ckw where fhid=w.id),0) 出库件数rn,isnull((select sum(shsl) from k_ckw where fhid=w.id),0) 收货数量rn,isnull((select sum(shhl) from k_ckw where fhid=w.id),0) 收货支数rn,isnull((select sum(shtl) from k_ckw where fhid=w.id),0) 收货过磅rn,isnull((select sum(shfl) from k_ckw where fhid=w.id),0) 收货件数rn,isnull(w.spje-w.spcb*w.spsl,0) as 利润1rn,isnull(w.spje-(select sum(spje) from k_ckw where fhid=w.id),0) as 利润2rnrn,(select top 1 dt.cfe3 from c_dd dt left join c_ddw dw on dt.link=dw.link where dw.id in(select ddid from k_rkw where spbh=w.spbh and scph=w.scph)) 采购合同编号rn,(select top 1 dt.cfe3 from x_dd dt left join x_ddw dw on dt.link=dw.link where dw.id =ddid) 销销合同编号,rn'X_FH' SRC,rnW.LINK SRCID,rn W.ID WID,W.LINE 生成时间rnFROMrn X_FHW W LEFT JOIN X_FH H ON W.LINK=H.LINKrn LEFT JOIN D_SP P ON W.SPBH=P.SPBHrn LEFT JOIN D_KH K ON H.KHBH=K.KHBHrn LEFT JOIN D_YWY Y ON Y.YWBH=H.YWBHrn left join d_ck c on c.ckbh=w.ckbhrnWHERE ZDRQ >= '[开始日期,DATE]' AND ZDRQ <= '[结束日期,DATE]' rn and H.DWID like '[公司,D_DW]%'rn AND W.SPBH LIKE '[商品名称,D_SP]%' ANDrn H.KHBH LIKE '[客户名称,D_KH]%' AND BSTT >= 1rn AND MMMM<>0 rn AND (H.YWBH LIKE '[业务员,D_YWY]%' OR H.YWBH IS NULL)rn AND W.CKBH LIKE '[仓库,D_CK]%'rnrnrn查询慢,感觉是列求和的问题
求优化下sql语句
array(2) rn [0] => string(6) "4.1184"rn [1] => string(316) "select a.custid from g_customer_tb a join g_customer_tbdata b on a.custid = b.custid where (b.rq > '2016-01-29 00:00:00 ' and b.rq < '2018-01-27 23:59:59') and a.shopid in (5,7,18,29,47,77,78,86,87,88,196,197,198,199) and( a.size1 = 1 or a.size2 = 1 or a.size3 = 1 ) group by b.custid having count(b.id) = 1"rnrn前面是运行时间,后面是sql语句,数据量大概是四十万,rn
SQL语句求优化
[code=SQL]rnselect changeVal.val ,val.results,changeVal.changeTime,val.changeNum from (select sum(pur.purchase_change_cost) val,count(pur.sp_code) changeTimern from ba_netmessage_purchase purrn left join ba_sp_info sp on pur.sp_code = sp.sp_codern left join ba_area area on pur.area_code = area.AREA_CODE rn where pur.sp_code inrn (select t12.sp_codern from (select ba.purchase_lowest_cost, ba.sp_codern from ba_netmessage_purchase ba,rn (select max(oprate_date) oprate_date, sp_codern from ba_netmessage_purchasern group by sp_code) t1rn where ba.sp_code = t1.sp_codern and ba.oprate_date = t1.oprate_date) t12,rn (select ba.purchase_lowest_cost, ba.sp_codern from ba_netmessage_purchase ba,rn (select min(oprate_date) oprate_date, sp_codern from ba_netmessage_purchasern group by sp_code) t1rn where ba.sp_code = t1.sp_codern and ba.oprate_date = t1.oprate_date) t2rn where t12.sp_code = t2.sp_codern and t12.purchase_lowest_cost - t2.purchase_lowest_cost > 0) rn and pur.oprate_date betweenrn to_date('2012-1-19 00:00:00', 'yyyy-MM-dd HH24:MI:SS') ANDrn to_date('2012-12-19 23:59:59', 'yyyy-MM-dd HH24:MI:SS')rn AND pur.area_code = '100' ) changeVal,--and sp.sp_name like '%%'rn ( select sum(t12.purchase_lowest_cost)results,count(t12.sp_code) changeNumrn from (select ba.purchase_lowest_cost, ba.sp_code,ba.purchase_change_costrn from ba_netmessage_purchase ba,rn (select max(ba.oprate_date) oprate_date, ba.sp_codern from ba_netmessage_purchase ba left join ba_sp_info sp on ba.sp_code=sp.sp_code rn where ba.oprate_date betweenrn to_date('2012-1-1 00:00:00', 'yyyy-MM-dd HH24:MI:SS') ANDrn to_date('2012-12-18 23:59:59', 'yyyy-MM-dd HH24:MI:SS')rn AND ba.area_code = '100' -- and sp.sp_name like '%%'rn group by ba.sp_code) t1rn where ba.sp_code = t1.sp_codern and ba.oprate_date = t1.oprate_date rn ) t12,rn (select ba.purchase_lowest_cost, ba.sp_codern from ba_netmessage_purchase ba,rn (select min(oprate_date) oprate_date, sp_codern from ba_netmessage_purchasern group by sp_code) t1rn where ba.sp_code = t1.sp_codern and ba.oprate_date = t1.oprate_date) t2rn where t12.sp_code = t2.sp_codern and t12.purchase_lowest_cost - t2.purchase_lowest_cost > 0 ) val;rn[/code]rn请各位帮忙从语法上优化下,对SQL不精通!rn
sql语句求优化
update warehouse.dbo.everyday_server set leijiamount=(select sum(c.order_amount) as amount from wanhui2.dbo.info_recharge c,warehouse.dbo.everyday_type drnwhere c.order_status=1 and c.order_admin_user=0 and (c.order_type=0 or c.order_type=2)rnand d.plat='wanhui2' and c.user_channel=d.user_channel and convert(varchar(10),dateadd(s,c.user_add_date+28800,'1970-01-01'),120)>=a.kaifuriqirnand c.game_id=a.game_id and c.server_id=a.server_id and d.zongleixing=a.zongleixingrnand datediff(dd,dateadd(s,c.order_submit_time+28800,'1970-01-01'),getdate())>0rngroup by c.game_id,c.server_id,d.zongleixingrn),leijirenshu=(select count(distinct c.user_id) as usercount from wanhui2.dbo.info_recharge c,warehouse.dbo.everyday_type drnwhere c.order_status=1 and c.order_admin_user=0 and (c.order_type=0 or c.order_type=2)rnand d.plat='wanhui2' and c.user_channel=d.user_channel and convert(varchar(10),dateadd(s,c.user_add_date+28800,'1970-01-01'),120)>=a.kaifuriqirnand c.game_id=a.game_id and c.server_id=a.server_id and d.zongleixing=a.zongleixingrnand datediff(dd,dateadd(s,c.order_submit_time+28800,'1970-01-01'),getdate())>0rngroup by c.game_id,c.server_id,d.zongleixing)rnfrom warehouse.dbo.everyday_server a where a.plat='wanhui2' and a.user_channel='推广' and a.user_type='新用户' rn
求sql 语句优化
各位大神,帮忙优化一下这条sql语句,速度太慢了?rnselect c.EName,p.Id,p.Title,p.AddTime,p.CategoryId,rn(case when p.id in (select t.ProductId from Product p left join ProductCertificateItem t on p.Id=t.ProductId where t.CertificateItemId=131) then 1 else 0 end) as flagrnfrom Product p left join Category c on p.CategoryId=c.Idrnwhere c.status=1 and p.status=1 and p.UserId=6001
SQL 优化 求帮忙
100w的数据量 查一下要几乎1分钟 不可想象 怎么优化下啊?楼主菜鸟[code=sql]rnselect casern when a.type = '1' thenrn '存款'rn when a.type = '2' thenrn '存款'rn when a.type = '3' thenrn '贷款'rn when a.type = '4' thenrn '贷款'rn when a.type = '5' thenrn '贷款'rn when a.type = '6' thenrn '贷款'rn elsern '无数据'rn end as type,rn a.begin_avg,rn a.org_id,rn b.org_name,rn a.manager_id,rn c.group_name as manager_name,rn d.cons_desc,rn a.account_no,rn a.currency_code,rn a.ratio,rn kmh,rn a.customer_id,rn f.customer_name,rn casern when e.is_sx = '2' thenrn '重点'rn elsern '非重点'rn end as is_sx,rn a.Acc_typ,rn a.Store,rn a.amass,rn a.bal_avg,rn a.balancern from asses_jymgr_mx a, bank_list b,rn outer std_group c,rn outer d_cons d,rn outer customer_list_gj e,rn outer customer_list frn where a.org_id = b.org_idrn and a.manager_id = c.group_idrn and a.manager_cons = d.cons_idrn and a.customer_id = e.customer_idrn and a.customer_id = f.customer_idrn and a.year = 2012rn and a.month = 11rn and a.acc_typ = '1'rn[/code]
求帮忙优化SQL
select distinct a.file_name as bqyname,a.unique_id as menuid from briocat2 a,bqy_roleright b,portal_userroles c where rnrnc.userid=@userId and b.role_id=c.roleid and a.unique_id=b.resource_id and a.parent_id is not null and a.unique_id not in (select aa.menuid from rnrnportal_indivdetail aa,briocat2 bb where aa.userid=@userId and aa.menuid=bb.unique_id and aa.tabid=2 and bb.parent_id is not null)rnrn这个SQL中,各个表中记录都很少,最多一个表不过3000条左右,因为数据少所有都没有建索引。但是在这个查询中速度却慢得奇怪,竟然需要30~40秒才能查出来。请问大家有何解决办法?谢谢。
求高手帮忙优化语句
求高手帮忙优化rn with hisdatarn as (select sum(1) over(partition by usersub.mobile,sub.substation_id,alarm.type,alarm.level,ai.pidrn order by usersub.mobile,sub.substation_id,alarm.type,alarm.level,ai.pid,cdatetime ) xhrn ,sub.pnamern ,sub.substation_idrn ,ai.pidrn ,ai.item_codern ,itm.item_name item_namern ,his.cdatetimern ,(case when sub.sub_type=5 then his.cvalue else his.cvtval end) valrn ,std.highrn ,usersub.mobilern ,alarm.typern ,(case when sub.sub_type=5 then (case when info.admin_code in (1,2) then alarm.alarm_value else 2*alarm.alarm_value end)rn when sub.sub_type=6 then (case when info.admin_code in (1,2)rn then (case alarm.alarm_value when 6 then 2 when 10 then 4 when 14 then 6 when 24 then 8 end)rn else (case alarm.alarm_value when 6 then 4 when 10 then 8 when 14 then 12 when 24 then 16 end) end)rn end) alarm_valuern ,max(cdatetime) over(partition by ai.pid) maxdatern from hisdata_1h hisrn inner join tai airn on his.pid=ai.pidrn and ai.item_code in (201,311,316)rn and his.cdatetime>=trunc(sysdate,'hh')-2.5 and his.cdatetime<=trunc(sysdate,'hh') - 1/24rn inner join item itmrn on ai.item_code=itm.item_codern inner join substation subrn on ai.substation_id=sub.substation_idrn and sub.sub_type in (5,6)rn inner join ai ai0rn on ai0.substation_id=sub.substation_idrn and ai0.item_code in (210,492)rn inner join hisdata_1h his0rn on ai0.pid=his0.pidrn and his0.cdatetime=his.cdatetimern and his0.cdatetime>=trunc(sysdate,'hh')-2.5 and his0.cdatetime<=trunc(sysdate,'hh') - 1/24rn and his0.qcode=0 and nvl(his0.cvalue,0)>10rn inner join pollution_info inforn on sub.pcode_c=info.enterprise_codern and sub.pcode=info.pwk_codern and sub.stcode=info.stcodern and sub.cyear=info.cyearrn inner join v_wp_standard stdrn on ai.item_code=std.item_codern and ai.scope_id=std.scope_idrn and ai.poll_standard = std.standardrn inner join v_user_substation usersubrn on sub.substation_id=usersub.substation_id and usersub.mobile is not nullrn inner join alarm_define alarmrn on type in (11,13)rn and usersub.level>=alarm.levelrn left join station_status strn on sub.substation_id=st.substation_idrn and not (st.endtime=his.cdatetime+1/24)rn and ((st.type_code in (25,27) and (st.code in (5,6) or st.code=ai.item_code)) or st.type_code=23)rn and st.check=1rn and his.cdatetime<=st.endtime and his.cdatetime>=st.starttimern where st.substation_id is nullrn )rn select mobilern ,pnamern ,typern ,item_namern ,alarm_valuern ,to_char(maxdate,'mm')||'月'||to_char(maxdate,'dd')||'日'||to_char(maxdate,'hh24')||'时' dtrn ,sum(case when cdatetime>=trunc(sysdate,'hh')-alarm_value/24 then 1 else 0 end) smsrn from hisdatarn where cdatetime>=trunc(sysdate,'hh')-(2+alarm_value)/24rn and type=11rn group by mobile,substation_id,pname,item_name,maxdate,type,larm_valuern having sum(case when cdatetime>=trunc(sysdate,'hh')-alarm_value/24 and val<=high then 1 else 0 end) = 0rn and sum(case when cdatetime=trunc(sysdate,'hh')-alarm_value/24 and val>high then 1 else 0 end) = 1rn and sum(case when cdatetime=trunc(sysdate,'hh')-(1+alarm_value)/24 and val>high then 1 else 0 end)=0rn and sum(case when cdatetime>=trunc(sysdate,'hh')-alarm_value/24 then 1 else 0 end)>alarm_value*0.8rn union allrn select h1.mobilern ,h1.pnamern ,h1.typern ,h1.item_namern ,h1.alarm_valuern ,to_char(h1.maxdate,'mm')||'月'||to_char(h1.maxdate,'dd')||'日'||to_char(h1.maxdate,'hh24')||'时' dtrn ,sum(case when h1.cdatetime>=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)+1 smsrn from hisdata h1rn inner join hisdata h2rn on h1.mobile=h2.mobilern and h1.pid=h2.pidrn and h1.xh = h2.xh+1rn and h1.alarm_value = h2.alarm_valuern where h1.cdatetime>=trunc(sysdate,'hh')-(h1.alarm_value)/24rn and h1.type=13rn and h2.type=13rn group by h1.mobile,h1.substation_id,h1.pname,h1.item_name,h1.maxdate,h1.type,h1.alarm_valuern having sum(case when h1.cdatetime>=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val<>h2.val then 1 else 0 end)=0rn and sum(case when h1.cdatetime=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)=1rn and sum(case when h1.cdatetime=trunc(sysdate,'hh')+(-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)=0rn and sum(case when h1.cdatetime>=trunc(sysdate,'hh')+(1-h1.alarm_value)/24 and h1.val=h2.val then 1 else 0 end)+1>h1.c0109_alarm_value*0.8
MYSQL语句优化,求帮忙
[code=sql]rnSELECT message_id,rn send_num ,rn target_num,rn IFNULL(date_format(start_sent_time,'%Y%m%d%h%i%s'),''),rn IFNULL(date_format(stop_sent_time,'%Y%m%d%h%i%s'),''),rn sent_content rn FROM sms_sent_pool rn WHERE IFNULL(stop_sent_time,DATE_ADD(now(),INTERVAL 31 DAY)) > now() rn AND apply_state = 0 limit 500 -- apply_state 上面只有0 -1 1 2四中类型的值。rn[/code]rnrn1.数据表sms_sent_pool大约800w数据,查500条有时8秒,有时15秒左右,能否做个优化,让查500条大约在1秒内完成。rn2.apply_state 建了索引没有作用。rn3.apply_state 状态会根据程序处理,会发生变化rn4. 注释掉 IFNULL(stop_sent_time,DATE_ADD(now(),INTERVAL 31 DAY)) > now() 这个条件,查询时间也不发生变化.rnrn求大哥们帮忙,没有多少分数,希望不要嫌少。[img=https://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/001/face/21.gif][/img]rn
求助!帮忙优化sql语句。
UPDATE table1 SET x =rn (SELECT min(y) from table2 where table1.z=table2.z)rnrn多谢!rnrn
帮忙优化一个SQL语句
[code=SQL]rnselect a.riqi,rn a.yinghuisum,rn a.yinghuiMoney,rn b.shishousum,rn b.shishouMoney,rn c.notReturnsum,rn c.notReturnMoneyrn from (select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,rn count(t.flowid) as yinghuisum /*应回款订单数*/,rn sum(o.ordersum) as yinghuiMoneyrn from ordersinfo o, postdealinfo trn where o.orderid = t.orderidrn and TO_DATE(substr(t.postdealtime, 0, 14),rn 'YYYY-MM-DD HH24:MI:SS') >=rn TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')rn and TO_DATE(substr(t.postdealtime, 0, 14),rn 'YYYY-MM-DD HH24:MI:SS') <=rn TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')rn and not existsrn (select *rn from orderstatuhisinfo starn where t.orderid = sta.orderidrn and (sta.lststatu = '11' or sta.lststatu = '19'))rn group by substr(t.postdealtime, 0, 8)) arn left join (select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,rn count(t.flowid) as shishousum /*实回款单数*/,rn sum(o.ordersum) as shishouMoneyrn from ordersinfo o, postdealinfo trn where o.orderid = t.orderidrn and TO_DATE(substr(t.postdealtime, 0, 14),rn 'YYYY-MM-DD HH24:MI:SS') >=rn TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')rn and TO_DATE(substr(t.postdealtime, 0, 14),rn 'YYYY-MM-DD HH24:MI:SS') <=rn TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')rn and exists (select *rn from orderstatuhisinfo starn where t.orderid = sta.orderidrn and sta.lststatu = '17')rn group by substr(t.postdealtime, 0, 8)) b on b.riqi = a.riqirn left join (select TO_DATE(substr(t.postdealtime, 0, 8), 'YYYY-MM-DD') as riqi,rn count(t.flowid) as notReturnsum /*未回款单数*/,rn sum(o.ordersum) as notReturnMoneyrn from ordersinfo o, postdealinfo trn where o.orderid = t.orderidrn and TO_DATE(substr(t.postdealtime, 0, 14),rn 'YYYY-MM-DD HH24:MI:SS') >=rn TO_DATE('2009-01-01 16:46:14', 'YYYY-MM-DD HH24:MI:SS')rn and TO_DATE(substr(t.postdealtime, 0, 14),rn 'YYYY-MM-DD HH24:MI:SS') <=rn TO_DATE('2009-08-29 16:46:14', 'YYYY-MM-DD HH24:MI:SS')rn and not existsrn (select *rn from orderstatuhisinfo starn where t.orderid = sta.orderidrn and (sta.lststatu = '11' or sta.lststatu = '19' orrn sta.lststatu = '17'))rn group by substr(t.postdealtime, 0, 8)) c on a.riqi = c.riqirn[/code]
高手帮忙优化下SQL语句
select top 20 Projects.id,Projects.Project_PayMin,Projects.Project_PayMax,rnProjects.Project_SelectBiddingPay,Projects.Project_Name,Projects.Project_EndTime,rnProjects.Project_State ,Projects.Project_Class rnfrom Projects inner join Project_Type on Projects.Project_Type=Project_Type.idrn where Project_State<>0 and Project_State<>5 rnand Project_State<>6 and (Project_Pay_isTrust=1 or WKSiteid<>11) rn and Project_TypeFatherId=1 and Projects.id rnnot in (select top 0 Projects.id from Projects where Project_State<>0 rn and Project_State<>5 and Project_State<>6 and (Project_Pay_isTrust=1 or WKSiteid<>11) rn and Project_TypeFatherId=1 order by Projects.Project_BeginTime desc) rnorder by Projects.Project_BeginTime descrnrnrnrn就这个SQL 语句 数据有点多 执行有点慢rn
帮忙看一下 sql语句优化
select rnb.prod_inst_id,rnw.character_value acc_nbr, rnd.cust_id,rnf.password,rna.local_net_id area_code,rnb.product_id,rnm.product_name,rnh.offer_id,rnh.prod_offer_inst_id,rnb.eff_timestamp,rnb.exp_timestamp,rnk.character_value user_kind_id,rnl.character_value billing_flag_id,rnd.name,rno.brand_id,rnd.cust_prpty_idrnfrom lcust.prod_inst brnleft join lcust.serv_addr a on b.serv_id=a.serv_id and a.sts='A'rnleft join lcust.serv_cust c on b.serv_id=c.serv_id and c.sts='A'rnleft join lcust.cust_q d on d.cust_id=c.cust_id and d.sts='A'rnleft join lcust.cust_auth f on f.cust_id =c.cust_id and f.sts='A' and f.auth_level='1'rnleft join lcust.prod_inst_prpty k on b.prod_inst_id=k.prod_inst_id and k.characteristic_id=124 and k.sts='A'rnleft join lcust.prod_inst_prpty l on b.prod_inst_id=l.prod_inst_id and l.characteristic_id=19 and l.sts='A'rnleft join lcust.prod_inst_prpty w on b.prod_inst_id=w.prod_inst_id and w.characteristic_id=13 and w.sts='A'rnleft join lcust.prod_offer_inst_item g on b.prod_inst_id=g.inst_id and g.sts='A'rnleft join lcust.prod_offer_inst h on h.prod_offer_inst_id=g.prod_offer_inst_id and h.sts='A'rnleft join ppm.product m on b.product_id=m.product_idrnleft join ppm.product_offer o on h.offer_id=o.offer_idrnwhere b.MAIN_FLAG='0' and b.sts='A'; rnrn优化的前提是,不能加索引,因为这个语句是从别的db2数据仓库取数据,所以不能创建索引的rnrn
帮忙优化一下SQL语句
[code=SQL]select GTNeiRong, GTDate,KHID from rnrnGouTong INNER JOIN Customer_Person ON Customer_Person.ID = GouTong.KH_Person_IDrnrnwhere GTID in (select max(GTID) from GouTong inner join Customer_Person rn on GouTong.KH_Person_ID = Customer_Person.IDrn group by KHID)[/code]
帮忙优化个sql语句
场景大概如下,有BookInfo以及QueryPlan两张表,分别是表示书籍、我的定制。rn我的定制的意思是,用户可以设置自己定制的方案,之后一进系统就显示该方案对应的书籍。rnrn表结构大概如下:rnrnBookInfo:BookID,BookName,BookTypeID(所属分类的ID),Author(作者),press(出版社)...rnQueryPlan:UserID,Flag(标志该方案是否为默认方案,用户可以有多个方案),BookTypeID(数据类型大概是",a,b,fas,fre,"表示用户该定制方案是这四种书籍的分类),Author(作者),press(出版社)...大概就这么些字段。rnrn我自己写的语句如下:rnrn[code=SQL]select top 5 BookID,BookName,b.Author,PressTime,Thumbnail from dbo.BookInfo b rnwhere rncharindex(b.Booktypeid,(select BookTypeID from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true')) >0 rnor rn(charindex(author,(select author from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true')) > 0) rnor rn(charindex(press,(select press from dbo.QueryPlan where UserID = 'sunyusheng' and Flag = 'true')) > 0)[/code]rnrn由于BookInfo表数据量是一百万以上的,执行以上语句差不多是30秒。用户接受不了。请各位高手们帮帮忙,怎么优化这个语句,也可以通过索引或者其他的办法,只要行得通就可以了。但是修改表结构的办法就不用了,项目是二期的,没得对表结构做修改。。
帮忙优化一条SQL语句
update table1 set col1 = 3 where col2 = 'a1' and col3 = 'b1' and col4 = 'c1'rnupdate table1 set col1 = 3 where col2 = 'a2' and col3 = 'b2' and col4 = 'c2'rnupdate table1 set col1 = 3 where col2 = 'a3' and col3 = 'b3' and col4 = 'c3'rnrn这种语句有上万条。而且还在不停的产生这种语句。rn每条的set col1 = 3 是一样的。 后面的条件也都是 col2 col3 col4 就是 a1 b1 c1 不一样。
帮忙优化一下sql语句
[code=SQL]SELECT N.IDrn ,N.Titlern ,N.CreateTime AS [Date]rn ,N.SecondCategoryID AS SCrn ,S.ClassName AS SNrn ,S.ParentID AS FCrn ,F.ClassName AS FNrnFROM News Nrn Left JOIN PE_Class S ON N.SecondCategoryID = S.ClassIdrn Left JOIN PE_Class F ON S.ParentID = F.ClassIDrnWHERE N.ID INrn (SELECT TOP 10 IDrn FROM Newsrn WHERE SecondCategoryId= N.SecondCategoryIDrn ORDER BY CreateTime DESC)rn AND ChannelID = 1rnORDER BY fn,sn DESC[/code]
Sql语句求帮忙
[img=https://img-bbs.csdn.net/upload/201309/16/1379309310_617659.png][/img]rn怎么把后面的6列变成下面的一列显示123456rn[img=https://img-bbs.csdn.net/upload/201309/16/1379309384_498028.png][/img]
求优化这条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可以拆分,越效率越好
相关热词 c# 去空格去转义符 c#用户登录窗体代码 c# 流 c# linux 可视化 c# mvc 返回图片 c# 像素空间 c# 日期 最后一天 c#字典序排序 c# 截屏取色 c#中的哪些属于托管机制