我正在尝试创建一个复式记账财务会计系统。我已经完成了数据库的设计，请检查此链接以查看我的数据库的图表。http://i39.tinypic.com/juhbv6.png
我已经尝试了很多开发 mysql 查询来生成总账，但我没有任何进展。我所能做的就是仅为一个帐户创建分类帐，而我必须为无限帐户创建分类帐。
我尝试过的查询如下：
$this->db->select('*');
$this->db->from('credit_side');
$this->db->join('debit_side', ' debit_side.transaction_id_dr = credit_side.transaction_id_cr ');
$this->db->join('transaction_info', 'transaction_info.transaction_id = credit_side.transaction_id_cr ');
$this->db->join('accounts', 'accounts.code = credit_side.account_code ');
$this->db->where('debit_side.account_code', '1001');
$this->db->order_by('voucher_date','ASC');
在编写可以为所有帐户生成分类帐的 mysql 查询失败后，我写下了创建总帐/T 帐户的逻辑。
现在，你能帮我解决mysql查询吗？
在执行以下操作之前，请先检查数据库。 提前致谢 ：）
Get the accounts.name作为账户主管，accounts.code从账户中。
到餐桌去debit_side and get debit_side.account_code,
if debit_side.account_code=accounts.code然后得到credit_side.account_codeAS AccountName1 （但是当我在 php 中 echo 时，我想获取名称而不是代码本身）并且credit_side.amountAS 金额1,SUM(credit_side.amount)AS 总金额 1 来自credit_side where debit_side.transaction_id_dr=credit_side.transaction_id_cr
and transaction_info.voucher_date位于 date1 和 date2 之间
在哪里transaction_info.transaction_id=debit_side.transaction_id_dr
完成第二步后进入表格credit_side and get credit_side.account_code,
if credit_side.account_code=accounts.code然后得到debit_side.account_codeAS AccountName2 （但是当我在 php 中 echo 时，我想获取名称而不是代码本身）并且debit_side.amountAS 金额2,SUM(debit_side.amount)AS 总金额2 来自debit_side where credit_side.transaction_id_cr=debit_side.transaction_id_dr
and transaction_info.voucher_date位于 date1 和 date2 之间
在哪里transaction_info.transaction_id=credit_side.transaction_id_cr
现在在视图文件中我的目标是具有以下内容：
<table width="200" border="0">
<tr><td colspan="5">Account Head <?echo $AccountHead ; ?> </td> </tr>
<tr>
<td>Dr.</td>
<td>amount.</td>
<td> </td>
<td>Cr</td>
<td>Amount</td>
</tr>
<tr>
<td><?echo $AccountName1 ; ?></td>
<td><?echo $Ammount1 ; ?></td>
<td></td>
<td><?echo $AccountName2 ; ?></td>
<td><?echo $Ammount2 ; ?></td>
</tr>
<tr>
<td>Total</td>
<td><?echo $TotalAmount1 ; ?></td>
<td> </td>
<td>Total </td>
<td><?echo $TotalAmount2 ; ?></td>
</tr>
</table>
public function getDebits(){
$data = array(
'debit_side.account_code DebitCode',
'group_concat(distinct accounts.name) as DebitAccount',
'group_concat(debit_side.amount) as DebitAmount',
'group_concat(transaction_info.voucher_date) as DebitVoucherDate'
);
$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();
}
public function getCredits()
{
$data = array(
'credit_side.account_code CreditCode',
'group_concat(distinct accounts.name) as CreditAccount',
'group_concat(credit_side.amount) as CreditAmount',
'group_concat(transaction_info.voucher_date) as CreditVoucherDate'
);
$this->db->select($data);
$this->db->from('accounts');
$this->db->join('debit_side','accounts.code = debit_side.account_code','left');
$this->db->join('credit_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('credit_side.account_code');
$this->db->order_by('credit_side.account_code','ASC');
$query = $this->db->get();
return $query->result_array();
}
抱歉回复晚了，但这是你在做任何事情之前想要测试的完美东西。我想为此创建一个视图文件，但它似乎很复杂并且需要更多时间，目前我湖
EDITED
这个解决方案可能会让您感到困惑，因为我已经测试过它，但是我尝试将这两个查询结合起来并成功了。该查询可能看起来很复杂，但它正在获取完美的结果。 这里是
$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();
虽然这个查询工作正常，但这里是这个查询的修改和优化版本，我真的从这个查询中学到了一些东西，你也应该看看这个
查询中 Group by 的奇怪行为需要优化