查询中分组依据的奇怪行为，需要进行优化 [英] Strange Behaviour of Group by in Query which needs to be optimized

问题描述

谁能帮助我优化此查询

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 

实际上，在此查询中，我尝试获取所有帐户的借方和贷方数据.您必须已经注意到子查询被重复，但是选择了不同的列.该查询正在获取完美的结果，但我希望对其进行优化.这是指向我的架构的链接

Actually in this query i am trying to get data for debit side and credit side for all accounts. You must have noticed that sub queries are repeated but selecting different columns. This query is fetching perfect results but i want it to be optimized. Here is the link to my schema

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

以前，我曾尝试将这两个查询组合在一起

Previously i had these two queries which i tried to combine

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

我也想删除正在获取的空记录. 注意:您还应该注意，在子查询中，我使用的条件略有不同，这是根据我的要求得出的.

Also i want to remove null record which is being fetched. Note : You should also note that in the sub queries i am using a little bit different conditions which are resulting according to my requirements.

编辑

我遇到了删除空记录的问题，但优化工作仍然没有解决.

I have covercome the problem to remove the null record but optimization is left still.

新编辑

这是我尝试半联接的方式

Here is what i tried with semi join

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进行更改则为贷方带来了完美的记录.有什么办法可以克服这个问题或任何替代方法.

The strange thing is that if i change group by having and order by with DebitCode it bring perfect records for debit side and if i change this with CreditCode if brings perfect records for credit side. Is there any way to overcome this problem or any alternative.

推荐答案

一段时间以来，我一直在研究您的模式和SQL，但我不太了解您的逻辑.我看到的东西:

I've been looking into your schema and SQL for a while and I don't quite understand your logic. Things as I see them:

• 您有一组交易(准确地说是9笔交易)；
• 对于每笔交易，您都有借方和贷方的详细信息；
• 使用两侧的account_code，您可以获得有关帐户的信息.

• you have a set of transactions (9 to be precise);
• for each transaction you have details on the debit and credit sides;
• using the account_code on each side, you can obtain info about accounts.

因此，对于初学者，我将采用这种方式并创建了VIEW，它将为您提供有关交易的所有必要信息.我在这里使用了INNER联接，因为我相信每笔交易必须都具有借方和贷方，并且每一方都应有一个帐户:

So, I would go this way for starters and created a VIEW, that would provide you with all the necessary information bout your transactions. I have used INNER joins here, as I believe each transaction must have both, debit and credit sides, and each side, in turn, should have an account:

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;

现在，在查看查询时，您似乎正在尝试获取每个帐户代码的所有计数器操作的列表.我不确定这样做的目的是什么，但是我会执行以下操作:

Now, looking at your queries, it seems that you're trying to get a list of all counter-side operations for each account code. I'm not sure what's the purpose of this, but I would do the following:

• 选择了唯一的帐户代码列表；
• 为每个帐户代码创建了借方操作的汇总列表，其中这些代码位于贷方；
• 为贷方操作创建了相同的汇总清单，该帐户位于借方；
• 并将每个帐户代码放在中间.

所以类似的事情可能会完成这项工作:

So something like this might do the job:

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联接.外部执行所有分组. 看看结果.

In the inner part I'm simulating FULL OUTER join by uniting 2 other joins, LEFT and RIGHT ones. And the outer part performs all the groupings. Take a look at the result.

请注意，如果要从结果中添加/删除列，则应同时修改内部和外部查询.

Note, that if you'd like to add/remove columns from the result, you should modify both, inner and outer queries.

我希望这是您一直在寻找的东西.

I hope this is what you've been looking for.

这篇关于查询中分组依据的奇怪行为，需要进行优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！