Query,
(
SELECT
` payments_total `. ` pay_type `,
` payments_total `. ` store_id `,
The SUM (
Billing_details. Import_money
) AS sum_import_money,
The SUM (
Billing_details. Explode_money
) AS sum_explode_money,
The SUM (
Billing_details. Service_money
) AS sum_service_money,
` member `. ` name `
The FROM
` billing_details `
INNER JOIN ` payments_total ` ON payments_total. Payment_id=billing_details. Payment_id
INNER JOIN ` member ` ON member, shop_Id=billing_details. Store_id
WHERE
Payments_total. ` status `='succ'
AND billing_details. ` status `=1
AND payments_total. Disabled='false'
AND billing_details. Disabled='false'
AND payments_total. Trade_code IN (
'3021',
'3030',
'3031',
'3026',
'3027',
'3028'
)
GROUP BY
` payments_total `. ` store_id `
The ORDER BY
` billing_details `. ` t_time ` DESC
)
The UNION
(
SELECT
` payments_total `. ` pay_type `,
` payments_total `. ` store_id `,
The SUM (
Billing_details. Import_money
) AS sum_import_money,
The SUM (
Billing_details. Explode_money
) AS sum_explode_money,
The SUM (
Billing_details. Service_money
) AS sum_service_money,
` member `. ` name `
The FROM
` billing_details `
INNER JOIN ` payments_total ` ON payments_total. Payment_id=billing_details. Payment_id
INNER JOIN ` member ` ON member, shop_Id=billing_details. Store_id
WHERE
Payments_total. ` status `='succ'
AND billing_details. ` status `=1
AND payments_total. Disabled='false'
AND billing_details. Disabled='false'
AND payments_total. Trade_code IN (
'3041',
'3042',
'3043',
'3044',
'3045',
'3046'
)
GROUP BY
` payments_total `. ` store_id `
The ORDER BY
` billing_details `. ` t_time ` DESC
)

EXPLAIN the
the condition of the1 PRIMARY sdb_rjt_member ALL 897 Using temporary; Using filesort
1 PRIMARY sdb_rjt_billing_details ALL 1196 Using the where; Using the join buffer
1 PRIMARY sdb_rjt_payments_total eq_ref PRIMARY index_payment_id, index_trade_code PRIMARY 302 dev_mmj_main sdb_rjt_billing_details. Payment_id 1 Using the where
2 the UNION sdb_rjt_member ALL 897 Using temporary; Using filesort
2 the UNION sdb_rjt_billing_details ALL 1196 Using the where; Using the join buffer
2 the UNION sdb_rjt_payments_total eq_ref PRIMARY index_payment_id, index_trade_code PRIMARY 302 dev_mmj_main sdb_rjt_billing_details. Payment_id 1 Using the where
The UNION RESULT & lt; Union1, 2 & gt; ALL

CodePudding user response:

Finish the subquery first sum statistical again left the join

CodePudding user response:

No column in the select clause included in the group by, so there is no problem

CodePudding user response:

There is no problem ah, query speed is slow, have what good optimized query Suggestions