Group by 在查询中的奇怪行为需要优化

2022-01-23 00:00:00 optimization subquery mysql

谁能帮我优化这个查询

选择`debit_side`.`account_code` CODE,GROUP_CONCAT(DISTINCT accounts.name) 作为借记账户，GROUP_CONCAT(debit_side.amount) 作为 DebitAmount，GROUP_CONCAT(transaction_info.voucher_date) 作为 DebitVoucherDate，(选择GROUP_CONCAT(DISTINCT 帐户名称)从(账户)LEFT JOIN debit_sideON accounts.code = debit_side.account_code左加入 credit_sideON debit_side.transaction_id_dr = credit_side.transaction_id_cr左加入交易信息ON transaction_info.transaction_id = credit_side.transaction_id_crGROUP BY credit_side.account_codeHAVING credit_side.account_code = `Code`) 作为 CreditAccount，(选择GROUP_CONCAT(credit_side.amount) 作为 CreditAmount从(账户)LEFT JOIN debit_sideON accounts.code = debit_side.account_code左加入 credit_sideON debit_side.transaction_id_dr = credit_side.transaction_id_cr左加入交易信息ON transaction_info.transaction_id = credit_side.transaction_id_crGROUP BY credit_side.account_codeHAVING credit_side.account_code = `Code`) 作为 CreditAmount,(选择GROUP_CONCAT(transaction_info.voucher_date) 作为 CreditVoucherDate从(账户)LEFT JOIN debit_sideON accounts.code = debit_side.account_code左加入 credit_sideON debit_side.transaction_id_dr = credit_side.transaction_id_cr左加入交易信息ON transaction_info.transaction_id = credit_side.transaction_id_crGROUP BY credit_side.account_codeHAVING credit_side.account_code = `Code`) AS CreditVoucherDate从(`帐户`)左连接`credit_side`ON `accounts`.`code` = `credit_side`.`account_code`左联接`debit_side`ON `debit_side`.`transaction_id_dr` = `credit_side`.`transaction_id_cr`左连接`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

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

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

以前我有这两个查询，我试图合并

选择debit_side.account_code DebitCode,group_concat(distinct accounts.name) 作为 DebitAccount，group_concat(debit_side.amount) 作为 DebitAmount，group_concat(transaction_info.voucher_date) 作为 DebitVoucherDate来自(`帐户`)左连接`credit_side`ON `accounts`.`code` = `credit_side`.`account_code`左联接`debit_side`ON `debit_side`.`transaction_id_dr` = `credit_side`.`transaction_id_cr`左连接`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

和

选择credit_side.account_code CreditCode,group_concat(distinct accounts.name) 作为 CreditAccount，group_concat(credit_side.amount) 作为 CreditAmount，group_concat(transaction_info.voucher_date) 作为 CreditVoucherDate来自(`帐户`)左联接`debit_side`ON `accounts`.`code` = `debit_side`.`account_code`左连接`credit_side`ON `debit_side`.`transaction_id_dr` = `credit_side`.`transaction_id_cr`左连接`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

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

编辑

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

新的编辑

这是我尝试使用半连接的方法

选择`lds`.`account_code` DebitCode,group_concat(distinct la.name) 作为 DebitAccount，group_concat(lds.amount) 作为 DebitAmount，group_concat(lti.voucher_date) 作为 DebitVoucherDate，`rcs`.`account_code` CreditCode,group_concat(distinct ra.name) 作为 CreditAccount，group_concat(rcs.amount) 作为 CreditAmount，group_concat(rti.voucher_date) 作为 CreditVoucherDateFROM 帐户为 laLEFT 以 ra 身份加入帐户ON ra.`code` = la.`code`LEFT JOIN `credit_side` 作为 lcsON `la`.`code` = `lcs`.`account_code`LEFT JOIN `debit_side` 作为 ldsON `lds`.`transaction_id_dr` = `lcs`.`transaction_id_cr`LEFT JOIN `transaction_info` 作为 ltiON `lti`.`transaction_id` = `lcs`.`transaction_id_cr`LEFT JOIN `debit_side` 作为 rdsON `ra`.`code` = `rds`.`account_code`左连接`credit_side` rcsON `rds`.`transaction_id_dr` = `rcs`.`transaction_id_cr`LEFT JOIN `transaction_info` 作为 rtiON `rti`.`transaction_id` = `rcs`.`transaction_id_cr`按信用代码"分组有 `CreditCode` 不为空ORDER BY `CreditCode` ASC

奇怪的是，如果我通过使用 DebitCode 来更改组并订购，它会为借方带来完美的记录，如果我使用 CreditCode 更改它，如果为贷方带来完美的记录.有没有办法克服这个问题或任何替代方案.

解决方案

我一直在研究你的架构和 SQL，但我不太明白你的逻辑.我所看到的:

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

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

CREATE VIEW all_transactions AS选择 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 catFROM transaction_info ti加入 debit_side ds ON ds.transaction_id_dr = ti.transaction_id加入 credit_side cs ON cs.transaction_id_cr = ti.transaction_id加入账户 da ON da.code = ds.account_code加入账户 ca ON ca.code = cs.account_code;

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

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

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

SELECT group_concat(dacc) "D-Accounts",group_concat(damt) "D-数量",group_concat(daname) "D 名称",group_concat(dvdt) "D-日期",代码名称，group_concat(cacc) "C 账户",group_concat(camt) "C-Amounts",group_concat(caname) "C 名称",group_concat(cvdt) "C-日期"从 (选择 atl.dacc、atl.damt、atl.daname、atl.tvdt dvdt、a.code、a.name、NULL cacc、NULL camt、NULL caname、NULL cvdtFROM 帐户LEFT JOIN all_transactions atl ON atl.cacc = a.code联合所有选择 NULL，NULL，NULL，NULL，a.code，a.name，atr.cacc、atr.camt、atr.caname、atr.tvdt cvdtFROM 帐户右连接 all_transactions atr ON atr.dacc = a.code) 全连接GROUP BY 代码、名称按代码订购；

在内部，我通过联合其他 2 个连接 LEFT 和 RIGHT 来模拟 FULL OUTER 连接.外部执行所有分组.看看结果.

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

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

Can anyone one help me optimize this 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

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

And

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.

EDITS

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

NEW EDITS

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

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.

解决方案

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:

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.

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:

selected a list of unique account codes;
created an aggregated list of debit-side operations for each account code, where such code was on the credit side;
created same aggregated list for credit-side operations, where such account was on debit side;
and put each account code in the middle.

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;

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.

相关文章