Group by by Query中的奇怪行为需要优化

任何人都可以帮助我优化此查询

SELECT 
  `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) 
  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 
FROM
  (`accounts`) 
  LEFT JOIN `credit_side` 
    ON `accounts`.`code` = `credit_side`.`account_code` 
  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` 
GROUP BY `debit_side`.`account_code` 
HAVING `Code` IS NOT NULL 
ORDER BY `debit_side`.`account_code` ASC 

实际上，在这个查询中，我试图为所有帐户获取借方和贷方的数据。 您一定注意到子查询重复但选择了不同的列。 这个查询获取完美的结果，但我希望它被优化。 这是我的模式的链接

http://www.sqlfiddle.com/#!2/82274/6

以前我有这两个我试图结合的查询

SELECT
  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
FROM (`accounts`)
  LEFT JOIN `credit_side`
    ON `accounts`.`code` = `credit_side`.`account_code`
  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`
GROUP BY `debit_side`.`account_code`
ORDER BY `debit_side`.`account_code` ASC

和

SELECT
  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
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`
ORDER BY `credit_side`.`account_code` ASC

另外我想删除正在提取的空记录。 注意：你也应该注意到，在子查询中，我使用了根据我的要求产生的一些不同的条件。

EDITS

我有覆盖问题来删除空记录，但优化仍然保持不变。

新编辑

这是我用半连接尝试的

SELECT
  `lds`.`account_code`    DebitCode,
  group_concat(distinct la.name) as DebitAccount,
  group_concat(lds.amount) as DebitAmount,
  group_concat(lti.voucher_date) as DebitVoucherDate,
  `rcs`.`account_code`    CreditCode,
  group_concat(distinct ra.name) as CreditAccount,
  group_concat(rcs.amount) as CreditAmount,
  group_concat(rti.voucher_date) as CreditVoucherDate
FROM accounts as la
  LEFT join accounts as ra
    ON ra.`code` = la.`code`
  LEFT JOIN `credit_side` as lcs
    ON `la`.`code` = `lcs`.`account_code`
  LEFT JOIN `debit_side` as lds
    ON `lds`.`transaction_id_dr` = `lcs`.`transaction_id_cr`
  LEFT JOIN `transaction_info` as lti
    ON `lti`.`transaction_id` = `lcs`.`transaction_id_cr`
  LEFT JOIN `debit_side` as rds
    ON `ra`.`code` = `rds`.`account_code`
  LEFT JOIN `credit_side` rcs
    ON `rds`.`transaction_id_dr` = `rcs`.`transaction_id_cr`
  LEFT JOIN `transaction_info` as rti
    ON `rti`.`transaction_id` = `rcs`.`transaction_id_cr`
GROUP BY `CreditCode`
HAVING `CreditCode` IS NOT NULL
ORDER BY `CreditCode` ASC

奇怪的是，如果我通过使用DebitCode进行更改，并为DebitCode命令，它会为借方带来完美的记录，并且如果我使用CreditCode更改此值，如果为信用方带来完美记录。 有什么办法可以克服这个问题或其他方法吗？

---

我一直在研究你的模式和SQL一段时间，我不太了解你的逻辑。 我看到他们的事情：

你有一组交易（准确地说9）;

对于每笔交易，您都有关于借方和贷方的详细信息;

使用每一方的account_code ，您可以获得有关帐户的信息。

所以，我会以这种方式为初学者创建一个VIEW ，这将为您提供交易中所有必要的信息。 我已经在这里使用INNER加入，因为我认为每笔交易都必须同时拥有借方和贷方，而每一方都应该有一个帐户：

CREATE VIEW all_transactions AS
SELECT ti.transaction_id tid, ti.voucher_no tvno, ti.voucher_date tvdt,
       ds.account_code dacc, ds.amount damt, da.name daname, da.type dat,
       cs.account_code cacc, cs.amount camt, ca.name caname, ca.type cat
  FROM transaction_info ti
  JOIN debit_side ds ON ds.transaction_id_dr = ti.transaction_id
  JOIN credit_side cs ON cs.transaction_id_cr = ti.transaction_id
  JOIN accounts da ON da.code = ds.account_code
  JOIN accounts ca ON ca.code = cs.account_code;

现在，看看您的查询，您似乎在尝试为每个帐户代码获取所有反面操作的列表。 我不确定这是什么目的，但我会做以下事情：

选择一个唯一的账户代码列表;

为每个账户代码创建了汇总的借方操作列表，其中这些代码在信用方面;

为信用方面的业务创建了相同的汇总清单，这些帐户在借方;

并将每个帐户代码放在中间。

所以像这样的东西可能会完成这项工作：

SELECT group_concat(dacc) "D-Accounts",
       group_concat(damt) "D-Amounts",
       group_concat(daname) "D-Names",
       group_concat(dvdt) "D-Dates",
       code, name,
       group_concat(cacc) "C-Accounts",
       group_concat(camt) "C-Amounts",
       group_concat(caname) "C-Names",
       group_concat(cvdt) "C-Dates"
  FROM (
    SELECT atl.dacc, atl.damt, atl.daname, atl.tvdt dvdt,
           a.code, a.name, NULL cacc, NULL camt, NULL caname, NULL cvdt
      FROM accounts a
      LEFT JOIN all_transactions atl ON atl.cacc = a.code
    UNION ALL
    SELECT NULL, NULL, NULL, NULL, a.code, a.name,
           atr.cacc, atr.camt, atr.caname, atr.tvdt cvdt
      FROM accounts a
      RIGHT JOIN all_transactions atr ON atr.dacc = a.code
  ) full_join
 GROUP BY code, name
 ORDER BY code;

在内部部分，我通过合并另外两个连接LEFT和RIGHT模拟FULL OUTER连接。 外部执行所有分组。 看看结果。

请注意，如果您想要从结果中添加/删除列，则应该修改内部和外部查询。

我希望这是你一直在寻找的。

链接地址: http://www.djcxy.com/p/10707.html

上一篇: Strange Behaviour of Group by in Query which needs to be optimized

下一篇: jQuery UI Autocomplete categories with count mechanism