Merkwürdiges Verhalten von Group by in Query, das optimiert werden muss
Kann mir jemand helfen diese Abfrage zu optimieren
<code>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 </code>
Tatsächlich versuche ich in dieser Abfrage, Daten für Debit- und Kreditseite für alle Konten abzurufen. Sie müssen bemerkt haben, dass Unterabfragen wiederholt werden, aber unterschiedliche Spalten auswählen. Diese Abfrage holt perfekte Ergebnisse, aber ich möchte, dass es optimiert wird. Hier ist der Link zu meinem Schema
http://www.sqlfiddle.com/#!2/82274/6
Zuvor hatte ich diese beiden Abfragen, die ich zu kombinieren versuchte
<code>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 </code>
Und
<code>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 </code>
Auch ich möchte nullsatz entfernen, der geholt wird. Anmerkung: Sie sollten auch beachten, dass ich in den Unterabfragen ein bisschen andere Bedingungen verwende, die sich gemäß meinen Anforderungen ergeben.
EDITS
Ich habe das Problem behoben, den Nulldatensatz zu entfernen, aber die Optimierung wurde beibehalten.
NEUE EDITS
Hier ist was ich mit semi join ausprobiert habe
<code>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 </code>
Das Seltsame ist, dass wenn ich die Gruppe ändere, indem ich DebitCode habe und danach bestelle, es perfekte Datensätze für die Debitseite bringt, und wenn ich dies mit CreditCode ändere, wenn es perfekte Datensätze für die Kreditseite bringt. Gibt es eine Möglichkeit, dieses Problem oder eine Alternative zu überwinden.