查詢陳述句。
(
SELECT
`payments_total`.`pay_type`,
`payments_total`.`store_id`,
SUM(
billing_details.import_money
) AS sum_import_money,
SUM(
billing_details.explode_money
) AS sum_explode_money,
SUM(
billing_details.service_money
) AS sum_service_money,
`member`.`name`
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`
ORDER BY
`billing_details`.`t_time` DESC
)
UNION
(
SELECT
`payments_total`.`pay_type`,
`payments_total`.`store_id`,
SUM(
billing_details.import_money
) AS sum_import_money,
SUM(
billing_details.explode_money
) AS sum_explode_money,
SUM(
billing_details.service_money
) AS sum_service_money,
`member`.`name`
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`
ORDER BY
`billing_details`.`t_time` DESC
)
EXPLAIN 下的情況
1 PRIMARY sdb_rjt_member ALL 897 Using temporary; Using filesort
1 PRIMARY sdb_rjt_billing_details ALL 1196 Using where; Using 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 where
2 UNION sdb_rjt_member ALL 897 Using temporary; Using filesort
2 UNION sdb_rjt_billing_details ALL 1196 Using where; Using join buffer
2 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 where
UNION RESULT <union1,2> ALL
uj5u.com熱心網友回復：
先子查詢sum統計完了再left joinuj5u.com熱心網友回復：
select 子句中的列沒有包含在group by中，這樣沒有問題嗎uj5u.com熱心網友回復：
沒有問題呀，查詢速度很慢，有什么好的優化查詢建議嗎轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106448.html
標籤：MySQL
上一篇：hbase 自定義過濾器問題