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

问题描述：

I have two tables;

Table 1

Table 2

I'm trying to produce a statement (like a bank statement) to show all charges and all payments. The charges and payments need to be displayed in date order based on the statement_date field.

I'm pretty sure I need to use 'unions' but I just can't seem to find a solution.

My effort so far is;

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

Thanks in advance.

我有两个表; p>

我正在尝试生成一份声明（如银行对帐单）以显示所有费用和所有付款。收费和付款需要根据statement_date字段按日期顺序显示。 p>

我很确定我需要使用'工会'，但我似乎无法找到到目前为止，我的努力是; p>

  $ statement_sql =“（SELECT * FROM accounts_tenant_charge）
UNION 
（SELECT *  FROM accounts_tenant_payment）
ORDER BY statement_date“; 
  code>  pre> 
 
 提前致谢。 p> 
  div>

答

Thanks for your help. The following query worked for me;

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

答

You must be make nested query. Firstly, you make union query based on your tables and put in inside query. Then, you make SELECT query and put in outside. Don't forget to give alias table for outside query.

You can try this code :

SELECT * FROM
(
  (SELECT * FROM accounts_tenant_charge)
   UNION
  (SELECT * FROM accounts_tenant_payment)
) a 
ORDER BY a.statement_date ASC

答

Your tables have different number of columns, therefore you cant union them right away by doing a select * . You have to select an equal number of columns from both tables. Like:

select
  tenant_charge_id as `id`,
  tenant_charge_date as `date`,
  tenant_charge_total_amonunt as `amount`
union
select
  tenant_payment_id,
  tenant_payment_date,
  tenant_payment_amount
order by
  `date` asc

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

相关推荐