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

来源：未知责任编辑：责任编辑发表时间:2013-12-22 14:57　点击:次

一张订单表

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