以下MySQL查询结果是每个帐户代码(acctcode
)的贷方和借方的总和,并使用union all
生成总计。表ledg_post
有 570 万条记录,并已编制索引。查询仍然需要 1 分钟才能执行。请帮助我调整此查询。
select b.acnt_code as acctcode
, b.disp_name as acctname
, sum(amt_dr) as debit
, sum(amt_cr) as credit
, (sum(amt_dr)- sum(amt_cr)) as closingbalance
, a.txn_code as txn_code
from ledg_post a
, gl_acnts b
, mst_loan lmt
where a.acnt_code = b.acnt_code
group
by b.acnt_code
union all
select ' ' acctcode
, ' Grand Total ' acctname
, sum(amt_dr) debit
, sum(amt_cr) credit
, (sum(amt_dr)- sum(amt_cr)) closingbalance
, '' txn_code
from ledg_post a
, mst_loan lmt
where lmt.loan_id = a.ref_id
表定义
create table ledg_post
( txn_code int(11)
, ref_id int(11)
, acnt_code int(11)
, amt_dr decimal(20, 2)
, amt_cr decimal(20, 2)
);
create table gl_acnts
( glm_acnt_code int
, glm_acnt_disp_name varchar(50)
);
create table mst_loan
( lmt_loan_id int(11)
, lmt_clnt_id int(11)
);
不需要
使用UNION ALL
,你可以使用GROUP BY...使用汇总
试试这个:
SELECT b.acnt_code AS acctcode, IFNULL(b.disp_name, ' Grand Total ') AS acctname,
SUM(a.amt_dr) AS debit, SUM(a.amt_cr) AS credit,
(SUM(a.amt_dr)- SUM(a.amt_cr)) AS closingbalance,
IFNULL(a.txn_code, '') AS txn_code
FROM ledg_post a
INNER JOIN gl_acnts b ON a.acnt_code=b.acnt_code
GROUP BY acctcode WITH ROLLUP