Может ли кто-нибудь помочь мне оптимизировать этот запрос
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, если приносит идеальные записи для кредитной стороны. Есть ли способ преодолеть эту проблему или какие-либо альтернативы.