zjg448281779 2015-06-01 02:28 采纳率: 0%
浏览 1701

关于这个sql应该如何去优化,谁可以指点一下方向和给出一些参考资料


SELECT *,
<!-- 若过滤条件含有 供应商反馈交期,后面的语句采用INNER JOIN链接过滤 -->
CAST(T.deliveryfeedback as char) as deliveryfeedback,
CAST(T.pocreatedate as char) as pocreatedate


CAST(prorder.deliveryfeedback as char) as deliveryfeedback,
CAST(prorder.pocreatedate as char) as pocreatedate

FROM (

SELECT request.fnumber,
request.purtaskid,
rd.request_detail_id,
rd.request_id,
item.skuid,
item.sku,
item.purdesc,
item.purspec,
CONCAT(request.purtaskid,'',rd.skuid) as combineid,
rd.stopkg,
rd.purpkg,
rd.reqpurqty,
rd.requestqty,
rd.poqty,
rd.piqty,
rd.sponroadqty,
rd.revshipqty,
rd.podate,
rd.pidate,
rd.shipdate,
rd.revshipdate,
rd.skulabel,
rd.reqdeliverydate,
rd.purpkgid,
request.fstatus,
request.urgency,
date_format(request.checktime, '%Y-%m-%d %T') as checktime,
sys_user.firstname,
CONCAT(sys_user.firstname,'',sys_user.lastname) as username,
Sysuser.email,
date_format(request.create_date, '%Y-%m-%d') createdate
,
prorder.deliveryfeedback,
prorder.deliveryfeedbackmax,
prorder.pocreatedate,
prorder.curno,
prorder.orderno,
prorder.amount

FROM rs_request_detail rd
inner join rs_request request on request.request_id = rd.request_id

left join bs_item item on rd.skuid = item.skuid
LEFT JOIN sys_user Sysuser ON Sysuser.userid = request.user_id
left join sys_user sys_user on item.pmid = sys_user.userid

        <if test="filtersqlPo.length() != 0 or pod_deliveryfeedback.length() != 0"> <!--  若过滤条件含有 供应商反馈交期,采用INNER JOIN链接过滤  -->
        INNER JOIN (
            SELECT 
                GROUP_CONCAT(distinct pod.delivery_feedback order by pod.delivery_feedback ) deliveryfeedback,
                MAX(pod.delivery_feedback) deliveryfeedbackmax ,
                po.createdate AS pocreatedate,
                po.purtaskid,
                pod.skuid,
                pod.delivery_feedback_remark,
                'CNY' as curno,
                GROUP_CONCAT(distinct po.orderno order by po.orderno ) orderno,
                FORMAT(SUM((SELECT rate FROM exchange_rate WHERE money_type = po.curno )*pod.price/(SELECT rate FROM exchange_rate WHERE money_type = 'CNY')*pod.purqty)/SUM(pod.purqty),2) as amount
            FROM pr_order po
            INNER JOIN pr_order_detail pod ON po.prorderid = pod.prorderid and po.purtaskid > 0
            where po.purtaskid > 0
            <if test="filtersqlPo.length() != 0">
              ${filtersqlPo}
            </if>
            GROUP BY po.purtaskid,pod.skuid ORDER BY NULL
        ) prorder on prorder.purtaskid=request.purtaskid and prorder.skuid=item.skuid 
             <if test="pod_deliveryfeedback.length() != 0">
                  ${pod_deliveryfeedback}
             </if>
         </if>
        WHERE (1 = 1) 
        <if test="filtersql.length() != 0">
              ${filtersql}
        </if>
         and item.isvirtual=0 
         and request.purtaskid>0
         and request.fstatus in(22,23,30)
         ORDER BY rd.request_id  DESC
            LIMIT ${offset},${limit}
        ) T
        <if test="filtersqlPo.length() == 0 and pod_deliveryfeedback.length() == 0">   <!--  若过滤条件不包含有 供应商反馈交期,采用LEFT JOIN链接过滤  -->
        LEFT JOIN (
            SELECT 
                GROUP_CONCAT(distinct pod.delivery_feedback) deliveryfeedback,
                MAX(pod.delivery_feedback) deliveryfeedbackmax ,
                po.createdate AS pocreatedate,
                po.purtaskid,
                pod.skuid,
                pod.delivery_feedback_remark,
                'CNY' as curno,
                GROUP_CONCAT(distinct po.orderno) orderno,
                FORMAT(SUM((SELECT rate FROM exchange_rate WHERE money_type = po.curno )*pod.price/
                (SELECT rate FROM exchange_rate WHERE money_type = 'CNY')*pod.purqty)/
                SUM(pod.purqty),2)
                as amount
            FROM pr_order po
            INNER JOIN pr_order_detail pod ON po.prorderid = pod.prorderid and po.purtaskid > 0
            where po.purtaskid > 0
            GROUP BY po.purtaskid,pod.skuid ORDER BY NULL
        ) prorder on prorder.purtaskid=T.purtaskid and prorder.skuid=T.skuid 
        </if>
        LEFT join (
            select 
                A.purtaskid,
                A.skuid,
                SUM(A.quantity)  detectquantity,
                SUM(A.batchCheckNum)  batchCheckNum,
                date_format(A.transdate,'%Y-%m-%d %H:%i:%s') transdate,
                date_format(A.detectDate,'%Y-%m-%d %H:%i:%s') detectDate
             from (
                select 
                prorder.prorderid,
                detect.detect_id,
                prorder.purtaskid,
                detect.skuid,
                detect.quantity,
                SUM(detectdetail.batchCheckNum) as batchCheckNum,
                detect.transdate,
                IFNULL(detectdetail.detectDate,DATE('9999-01-01')) as detectDate
            from pr_order prorder
            INNER join scm_detect detect on prorder.prorderid = detect.prorderid and prorder.purtaskid>0
            LEFT join scm_detect_detail detectdetail on detectdetail.detect_id =detect.detect_id
            GROUP BY prorder.purtaskid,detect.skuid,detect.detect_id
            ORDER BY NULL
        ) A
          GROUP BY A.purtaskid,A.skuid ORDER BY NULL
        ) detectd on detectd.purtaskid=T.purtaskid and detectd.skuid=T.skuid
          ORDER BY T.request_id DESC
 </select>
  • 写回答

3条回答 默认 最新

  • quanwuhui 2015-06-01 02:38
    关注

    通过查看执行计划查看你的q所耗费的资源在哪里就知道优化了,另外优化的一大原则就是 尽量使用索引
    如何查看执行计划,你自己百度吧.

    评论

报告相同问题?

悬赏问题

  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题