mysql多表联结

SQL多表联结与性能优化

最新推荐文章于 2025-09-13 19:07:06 发布

weixin_34110749

最新推荐文章于 2025-09-13 19:07:06 发布

阅读量82

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/u/347414/blog/176257

2019独角兽企业重金招聘Python工程师标准>>>

SELECT  row_number() OVER ( ORDER BY b.applicationmainCode DESC ) AS rowid ,
        a.applicationdetailid ,
        a.ApplicationMainCode ,
        a.price ,
        a.goodsnum ,
        a.reciveNum ,
        a.sendNum ,
        a.PublicNum ,
        a.Currency ,
        a.equipment ,
        a.equipmentnum ,
        a.requiredate ,
        a.des ,
        c.suppliername ,
        d.goodsname ,
        d.standard ,
        d.unit ,
        d.productid ,
        d.productArea ,
        d.buyarea ,
        e.paymenter ,
        f.workunit ,
        g.classname ,
        b.Ausername ,
        b.AworkUnit ,
        b.applicationMaindate ,
        b.buycode ,
        UseProuduct ,
        ProductCode ,
        TSort ,
        TsortChild ,
        TSortNum ,
        CASE WHEN tnewflag = 1 THEN '新'
             ELSE '旧'
        END AS tnewStatus ,
        Tcontent ,
        Tdes ,
        d.price AS GPrice ,
        CASE WHEN b.comfigflag = 0 THEN '是'
             ELSE '否'
        END AS comfigstatus ,
        ( SELECT TOP 1
                    getgoodsdate
          FROM      instock
          WHERE     applicationdetailid = a.applicationdetailid
          ORDER BY  getgoodsdate
        ) AS getgoodsdate
FROM    Gr_dt_applicationDetail a
        LEFT JOIN Gr_db_supplier c ON a.supplierid = c.supplierid
        LEFT JOIN applicationMain b ON a.applicationmainCode = b.ApplicationMaincode
        LEFT JOIN goods d ON a.goodsid = d.goodsid --#
        LEFT JOIN Payment e ON b.paymentid = e.paymentid
        LEFT JOIN workunit f ON b.UworkUnitId = f.workUnit_Id
        LEFT JOIN goodstreeclass g ON b.classid = g.classid
WHERE   a.applicationmainCode = b.ApplicationMaincode
        AND a.goodsid = d.goodsid    --这个地方跟#处有点儿重复了,#处改成 inner join
        AND 1 = 1
        AND b.passflag >= 1
ORDER BY b.applicationmainCode DESC

多表联结

SELECT ...  FROM 
a  JOIN  b  ON  (a.id   =  b.aid )
   JOIN   c  ON  (a.id   =  c.aid )
   JOIN   d  ON  (c.id   =  d.cid )

这样的语句，在数据量大，索引没维护好，想不慢都难呀。

通过执行计划检查性能低的位置，再考虑建索引，关联表太多考虑使用临时表。

转载于:https://my.oschina.net/u/347414/blog/176257