mysql - 多个 MySQL 表的净总和

Question

我有两个 mysql 表，一个是 tblvouchers，第二个是 tblinvoices，我正在开发一个小型会计系统。两个表都有不同的字段帐户代码、借方和贷方金额字段等。现在我想计算总净余额从这两个表的代码来看，我通过多个小临时表和查询来实现这一目标，但我想通过单个查询来实现这一目标，请任何人指导我如何通过单个 mysql 查询来实现这一目标。

我的示例表如下。

TB礼券

VOU_ACC_CODE  DR_AMOUNT  CR_AMOUNT  
------------  ---------  -----------
3-01-0001             0         4600
3-01-0002         10000            0
3-01-0005         15000            0
6-02-0001             0        27500
6-02-0002             0       315432
6-03-0001         27500            0

TBLINVOICES

ACCOUNT_CODE  DR_AMOUNT  CR_AMOUNT  
------------  ---------  -----------
3-01-0001             0        11400
3-01-0005             0      10454.2
3-01-0001             0         4600
3-01-0001             0         7500
6-01-0002          1250            0
6-02-0007             0          750
7-01-0001             0         1250


ACCOUNT_CODE  DR_AMOUNT  CR_AMOUNT    NET_BALANCE
------------  ---------  -----------  -----------
3-01-0001         0      28100        -28100
3-01-0002     10000          0     10000    
3-01-0005    4545.8          0        4545.8
6-02-0001         0      27500        -27500
6-02-0002         0     315432       -315432
6-02-0007         0        750          -750
6-03-0001     27500          0         27500
7-01-0001         0       1250         -1250

我的 PHP 代码:

$empty = mysql_query("TRUNCATE `tblaccountingledger`");
  $VOUCHERS = mysql_query("
  INSERT INTO `tblaccountingledger`
  SELECT 
    `tblvouchers`.`VOU_DATE`,
    `tblvouchers`.`VOU_NO`,
    `tblvouchers`.`BILL_NO`,
    `tblvouchers`.`VOU_CROSS_ACC_CODE`,
    `tblcharts`.`ACC_NAME`,
    `tblvouchers`.`VOU_NARRATION`,
    `tblvouchers`.`DR_AMOUNT`,
    `tblvouchers`.`CR_AMOUNT` 
  FROM
    `tblvouchers` 
    INNER JOIN `tblcharts` 
      ON (
        `tblvouchers`.`VOU_CROSS_ACC_CODE` = `tblcharts`.`ACC_CODE`
      ) 
  WHERE `tblvouchers`.`VOU_ACC_CODE` = '".$ACC_CODE."' 
    AND `tblvouchers`.`VOU_DATE` BETWEEN '".$START_DATE."' 
    AND '".$END_DATE."';
  ");


  $INVOICES = mysql_query("
  INSERT INTO `tblaccountingledger`
  SELECT 
    `tblinvoices`.`INVOICE_DATE`,
    `tblinvoices`.`INVOICE_NO`,
    `tblinvoices`.`BILL_NO`,
    `tblinvoices`.`CROSS_ACCOUNT_CODE`,
    `tblcharts`.`ACC_NAME`,
    `tblinvoices`.`NARRATION`,
    `tblinvoices`.`DR_AMOUNT`,
    `tblinvoices`.`CR_AMOUNT` 
  FROM
    `shop_system`.`tblinvoices` 
    INNER JOIN `shop_system`.`tblcharts` 
      ON (
        `tblinvoices`.`CROSS_ACCOUNT_CODE` = `tblcharts`.`ACC_CODE`
      ) 
  WHERE `tblinvoices`.`ACCOUNT_CODE` = '".$ACC_CODE."' 
    AND `tblinvoices`.`INVOICE_DATE` BETWEEN '".$START_DATE."' 
    AND '".$END_DATE."';
  ");

Answer 1

首先，您需要使用 UNION ALL 从这两个表中创建一个表。然后，您只需使用 GROUP BY 聚合数据即可:

SELECT VOU_ACC_CODE, SUM(DR_AMOUNT), SUM(CR_AMOUNT), (SUM(DR_AMOUNT) - SUM(CR_AMOUNT)) AS NET_BALANCE  FROM
  (SELECT VOU_ACC_CODE, DR_AMOUNT, CR_AMOUNT FROM TBLVOUCHERS
    UNION ALL
  SELECT ACCOUNT_CODE AS VOU_ACC_CODE, DR_AMOUNT, CR_AMOUNT FROM TBLINVOICES) AS tbl1
GROUP BY VOU_ACC_CODE

请参阅 SQLFiddle:http://sqlfiddle.com/#!2/07a12/10