订单表:
订单id 名称
销售表:
销售id 订单id no 名称 单价 数量
采购表:
采购id 订单id no 名称 单价 数量 供货商id 操作人员id
供货商:
供货商id 名称
操作人员:
操作人员id 姓名
一个销售表中的no可以对应这个采购表的no
查询结果为:
SQL语句写法:
SELECT a.estimate_id, a.created, a.title, b.buy_id, b.no AS bno, b.bprice, b.bnum, b.detail as bdetail, b.bprice * b.bnum AS bcount, c.sell_id, c.category, c.no AS cno, c.detail as cdetail, c.sprice, c.snum, c.sprice * c.snum AS ccount, d.member_name, e.supplier_name
FROM tb_estimate AS a
INNER JOIN tb_buy AS b ON a.estimate_id = b.estimate_id
INNER JOIN tb_sell AS c ON c.estimate_id = a.estimate_id
INNER JOIN tb_member AS d ON d.member_id = b.member_id
INNER JOIN tb_supplier AS e ON e.supplier_id = b.supplier_id
WHERE a.estimate_id={$estimate_id}
返回的是一个二维数组,有重复的数据,不方便操作
也可以改为多次查询,将结果通过数组整合,方便操作