三表(订单表、销售表、采购表)查询、统计语句综合
一张订单表
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