当前位置:操作系统 > Unix/Linux >>

三表(订单表、销售表、采购表)查询、统计语句综合


一张订单表
 
estimate_id title
 
一张销售表
 
sell_id estimate_id no sprice snum
 
一张采购表
 
buy_id estimate_id no bprice bnum
 
一个订单对应多个销售和采购(estimate_id),一个销售对应多个采购(no)
 
查询的结果:
 
title   scount    bcount 
 
XX     10.00        5.00
 
BB                               (注:这种情况可能是只有订单,还没有销售和采购)
 
AA    100.00     45.00
 
 ..         ....             .....
 
SQL语句:
 
[sql]
SELECT e. * , s.scount, b.bcount   
FROM tb_estimate AS e  
LEFT JOIN (  
    SELECT SUM( sprice * snum ) AS scount, estimate_id  
    FROM tb_sell  
    WHERE deleted =0  
    GROUP BY estimate_id  
) AS s ON e.estimate_id = s.estimate_id  
LEFT JOIN (  
    SELECT SUM( bprice * bnum ) AS bcount, estimate_id  
    FROM tb_buy  
    WHERE deleted =0  
    GROUP BY estimate_id  
) AS b ON b.estimate_id = e.estimate_id  
WHERE e.deleted =0  
ORDER BY updated DESC  
 
 
 
作者 chuangrain
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,