php - 从两个不同的表中选择行，但使用 php 和 MySQL 按日期排序

Question

我有两个表；

我正在尝试制作一份对账单(如银行对账单)来显示所有费用和所有付款。费用和付款需要根据 statement_date 字段按日期顺序显示。

我很确定我需要使用“联合”，但我似乎找不到解决方案。

到目前为止我的努力是；

$statement_sql = "(SELECT * FROM accounts_tenant_charge)
UNION
(SELECT * FROM accounts_tenant_payment)
ORDER BY statement_date";

提前致谢。

Answer 1

感谢您的帮助。以下查询对我有用；

SELECT tenant_charge_date as statement_date, tenant_charge_id as reference, tenant_charge_total_amount as debit, NULL as credit, 'Charge' as type FROM accounts_tenant_charge
UNION ALL
SELECT tenant_payment_date as statement_date, tenant_payment_id as reference, NULL as debit, tenant_payment_amount as credit, 'Payment' as type FROM accounts_tenant_payment
ORDER BY statement_date