我有以下两个 mysql 查询,我正在尝试将它们合并为一个。
查询 1:
$getData = $this->db->query("SELECT *,accounts.name AS DebitAccountName ,debit_side.amount AS DebitAmount
FROM credit_side
LEFT JOIN debit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
LEFT JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
LEFT JOIN accounts ON accounts.code = credit_side.account_code
WHERE debit_side.account_code='1001'");
查询 2:
$getData = $this->db->query(SELECT *,accounts.name AS CreditAccountName,credit_side.amount AS CreditAmount
FROM debit_side
LEFT JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
LEFT JOIN transaction_info ON transaction_info.transaction_id = debit_side.transaction_id_dr
LEFT JOIN accounts ON accounts.code = debit_side.account_code
WHERE credit_side.account_code='1001'");
我已经尝试过 UNION ALL,但这实际上在这种情况下不起作用,问题是当我回显$DebitAccountName
它也会显示$CreditAccountName
的结果时,这是我不想要的。
正在使用 Codeigniter,在我的视图文件中,我的目标是像这样回显结果。
<?php if(count($records) > 0) { ?>
<?php foreach ($records as $row){ ?>
<?php echo $row['DebitAccountName']; ?>
<?php echo $row['DebitAmount']; ?>
<?php echo $row['CreditAccountName']; ?>
<?php echo $row['CreditAmount']; ?>
你能帮我把两个问题合二为一吗?
提前致谢:)
我结合了这些查询
$data = array(
'debit_side.account_code Code',
'group_concat(distinct accounts.name) as DebitAccount',
'group_concat(debit_side.amount) as DebitAmount',
'group_concat(transaction_info.voucher_date) as DebitVoucherDate',
'(SELECT group_concat(distinct accounts.name) as CreditAccount FROM (accounts)
left JOIN debit_side ON accounts.code = debit_side.account_code
left JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
left JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
group by credit_side.account_code
having credit_side.account_code = `Code`) as CreditAccount',
'(SELECT group_concat(credit_side.amount) as CreditAmount FROM (accounts)
left JOIN debit_side ON accounts.code = debit_side.account_code
left JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
left JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
group by credit_side.account_code
having credit_side.account_code = `Code`) as CreditAmount',
'(SELECT group_concat(transaction_info.voucher_date) as CreditVoucherDate FROM (accounts)
left JOIN debit_side ON accounts.code = debit_side.account_code
left JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
left JOIN transaction_info ON transaction_info.transaction_id = credit_side.transaction_id_cr
group by credit_side.account_code
having credit_side.account_code = `Code`) as CreditVoucherDate'
);
$this->db->select($data);
$this->db->from('accounts');
$this->db->join('credit_side','accounts.code = credit_side.account_code','left');
$this->db->join('debit_side','debit_side.transaction_id_dr = credit_side.transaction_id_cr','left');
$this->db->join('transaction_info','transaction_info.transaction_id = credit_side.transaction_id_cr','left');
$this->db->group_by('debit_side.account_code');
$this->db->order_by('debit_side.account_code','ASC');
$query = $this->db->get();
return $query->result_array();
也在此处查看编辑
如何使用 PHP Mysql 创建总账/T 帐户
好吧,由于@vyegorov,这是此查询的修改和优化版本
查询中分组依据的奇怪行为需要优化
为什么不使用两个表(debit_side和credit_side)中的 SELECT?当然,对于每个表所需的所有 JOIN。
为了简单起见,我省略了transaction_info JOIN:
SELECT debacc.name AS DebitAccountName, debit_side.amount AS DebitAmount,
credacc.name AS CreditAccountName, credit_side.amount AS CreditAmount
FROM debit_side
LEFT JOIN credit_side ON debit_side.transaction_id_dr = credit_side.transaction_id_cr
LEFT JOIN accounts credacc ON credacc.code = credit_side.account_code
LEFT JOIN accounts debacc ON debacc.code = debit_side.account_code
WHERE debit_side.account_code='1001'
此查询引用账户表两次,一次用于借方,一次用于贷方。这允许您显示交易双方的信息。