喂猪吃鸡腿 2022-03-01 15:07 采纳率: 47.8%
浏览 39
已结题

有没有搞过sql优化的看看 pgsql 查询优化

问题遇到的现象和发生背景

看看这个sql语句还能不能优化

问题相关代码,请勿粘贴截图
SELECT
    com.name                AS company, 
    area.name               AS area_name, 
    br.name                 AS branch,
    hps.id                  AS swinery_id,
    svc.svc_org_id          AS svg_id,
    so.date                 AS date,
    fm.name                 AS farmer, 
    cus.name                AS customer,
    CASE 
        WHEN so.sale_type = 'inner_allocate' THEN '内部调拨'
        WHEN so.sale_type = 'inner_sale' THEN '内部销售'
        WHEN so.sale_type = 'outer' THEN '外部销售'
    END                                   AS saletype,

    CASE 
        WHEN sol.level = 'lev_one' THEN '一级品'
        WHEN sol.level = 'lev_two' THEN '二级品'
        WHEN sol.level = 'lev_three' THEN '三级品'
    END                                   AS level,
    
    sol.qty_delivery      AS qty_sold,
    sol.weight_delivery   AS weight_sold,
    CASE 
        WHEN sol.qty_delivery > 0 THEN round(sol.weight_delivery :: NUMERIC / sol.qty_delivery :: NUMERIC, 2)
        ELSE 0
    END                      AS weight_avg,
    
    sol.price                AS price_avg,
    sol.line_amount          AS price,
    ff.name  as  ff_name,
    hme.name as sale_man,
    hps.number as swinery,
    emp.name as technician
    FROM hd_mrk_sale_order so
    INNER JOIN hd_mrk_sale_order_line sol ON sol.order_id = so.id
    LEFT JOIN hd_m_customer cus ON cus.id = so.customer_id
    left JOIN res_company com ON com.id = so.company_id
    INNER JOIN hd_m_svc_org svc on svc.org_unit_id = so.company_id             
    INNER JOIN hd_m_area area on area.id = svc.area_id          
    INNER JOIN hd_mrk_recycle_order rec on rec.id  = so.recycle_order_id
    INNER JOIN hd_m_farmer fm on fm.id  = rec.farmer_id
    INNER JOIN hd_m_branch br on br.id = svc.branch_id
    left join hd_pro_swinery as hps on hps.id = rec.swinery_id
    left join hd_m_employee emp on emp.id = hps.technician_id
    left join hd_far_farm ff on ff.id = hps.farm_id
    left join hd_m_employee hme on rec.salesman_id = hme.id
    WHERE so.state IN ('audit', 'closed')
  • 写回答

1条回答 默认 最新

  • yang_z_1 2022-03-02 12:57
    关注

    不知道你这个语句查询需要多长时间,表的大小,只能给你提几个意见:

    1. 如果表的数据量大。可以考虑用子查询只查需要的字段,然后在关联。
    2. 查询尽量走索引
    3. 你关联的表有点多,可以试试嵌套。
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 系统已结题 3月10日
  • 已采纳回答 3月2日
  • 创建了问题 3月1日

悬赏问题

  • ¥15 有没有人会打学生成绩管理系统呀
  • ¥15 在使用Fiddler和夜神模拟器抓包的时候一直出现443该怎么办啊QAQ搜了好几个笔记都没有解决
  • ¥15 3x7的二维数组A、B、C,A中的任意1个数组元素与B的任意1个数组元素、同时又与C的任意1个数组元素比较,把不同位置出现相同数的比较称为无意义,反之称为有意义,把有意义的比较打印输出。
  • ¥20 预测模型怎么处理原始数据(随机森林)
  • ¥20 请问discuz3.5如何实现插入ckplayer全能播放器功能呢?
  • ¥15 thingsboard代码编译出错误
  • ¥15 博途v18仿真报错怎么解决
  • ¥15 欧姆龙plc枕式包装机 ST编程
  • ¥15 为啥快手广告联盟的广告这么难出来
  • ¥15 k8s集群重启后,kubelet一直报systemctl restart kubelet.service "Failed to delete cgroup paths"