需求:将销售订单和采购订单相同字段合并成一张表然后进行分页查询。
列如采购数据:
SELECT contract_id,group_id,total_quantity,total_amount,created FROM scm_purchase_order ORDER BY created DESC LIMIT 0,15
结果:
销售数据:
SELECT contract_id,group_id,total_quantity,total_amount,created FROM scm_sale_order ORDER BY created DESC LIMIT 0,15
结果:
然后将两张表数据合并为一张表分页查询:
SELECT
contract_id AS 合同号,
group_id AS 部门id,
total_quantity AS 数量,
total_amount AS 金额,
created AS 时间
FROM
(
SELECT
contract_id,
group_id,
total_quantity,
total_amount,
created
FROM
scm_purchase_order
UNION
SELECT
contract_id,
group_id,
total_quantity,
total_amount,
created
FROM
scm_sale_order
) AS tb ORDER BY created DESC LIMIT 0,15
得出结果: