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

Question

谁能帮我优化这个查询

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

我还想删除正在获取的空记录。注意:您还应该注意，在子查询中我使用了一些不同的条件，这些条件是根据我的要求产生的。

编辑

我已经解决了删除空记录的问题，但优化仍然存在。

新编辑

这是我尝试过的半连接

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 更改组和订购方式，它会为借方带来完美的记录，如果我使用 CreditCode 更改它，如果为贷方带来完美的记录。有什么方法可以克服这个问题或任何替代方法。

Answer 1

我已经研究了您的架构和 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;

在内部部分，我通过合并其他 2 个连接(LEFT 和 RIGHT)来模拟 FULL OUTER 连接。外部执行所有分组。 Take a look at the result .

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

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