mysql 如何减少 Copying to tmp table 的 开销时间

报表查询 Copying to tmp table 的 开销时间较长!

SQL如下：

SELECT

bill.fdd_paydate as '统计日期',

bill.fdi_ognid as '门店ID',

bill.fdc_ognname as '门店名称',

SUM(bill.fdm_liushui+bill.fdm_vouchernoconsume) as '销售流水',

SUM(bill.fdm_freeAmount) as '赠送',

SUM(if(bill.fdi_dstype=1 or bill.fdi_dstype is null,bill.fdm_discountamount,0)) as '折扣',

SUM(if(bill.fdi_dstype=2,bill.fdm_discountamount,0)) as '满减优惠',

SUM(bill.fdm_dandiscountamount) as '单品折扣',

SUM(bill.fdm_changeamount+bill.fdm_percentchangeamount) as '抹零',

SUM(bill.fdm_pricediff) as '会员优惠',

SUM(bill.fdm_specialoffer) as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

0 as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

'' as '微信',

0 as '微信金额',

SUM(IF(T.fdb_isnoopentable=1 and (bill.fdc_tableno REGEXP '^[0-9]*$')=1,bill.fdi_tables,0)) as '桌数',

SUM(IF(if(ifnull(T.fdi_fatherid,0)<>0,D.fdb_isnoopentable,T.fdb_isnoopentable)=1 ,bill.fdi_member,0)) as '就餐人数',

0 as '总台数',

0 as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

SUM(bill.fdm_noconsume+bill.fdm_vouchernoconsume) as '营业外收入',

IFNULL(fdc_servicename,'服务费') as '服务费名称',

SUM(bill.fdm_serviceamount) as '服务费',

IFNULL(fdc_teaservicename,'茶位费') as '茶位费名称',

SUM(bill.fdm_teaamount) as '茶位费'

FROM rept_bill bill

left join sys_table T on T.fdi_tableid=bill.fdi_tableid and T.fdi_ognid=bill.fdi_ognid #如果是搭台，则为搭台桌

left join sys_table D on T.fdi_fatherid=D.fdi_tableid and D.fdi_ognid=T.fdi_ognid #如果是搭台则观察主桌台，如果是主桌台，则不使用D

where  bill.fdd_paydate between '2017/7/1' and '2017/7/7'  and bill.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)   and bill.fdi_billstatus=1

GROUP BY bill.fdi_ognid

UNION ALL

-- 不计入实收

SELECT

billpay.fdd_paydate as '统计日期',

billpay.fdi_ognid as '门店ID',

billpay.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

billpay.fdc_pmname as '不计入实收的支付方式名称',

SUM(billpay.fdm_amount) as '不计入实收金额',

0 as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

'' as '微信',

0 as '微信金额',

0 as '桌数',

0 as '就餐人数',

0 as '总台数',

0 as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

FROM rept_billpay billpay

where billpay.fdi_paymode=0 and billpay.fdm_amount<>0   and  billpay.fdd_paydate between '2017/7/1' and '2017/7/7'  and billpay.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)   

GROUP BY billpay.fdi_ognid,billpay.fdi_pmid

UNION ALL

-- 计入实收的支付方式

SELECT

billpay.fdd_paydate as '统计日期',

billpay.fdi_ognid as '门店ID',

billpay.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

0 as '营业实收',

billpay.fdc_pmname as '计入实收名称',

SUM(billpay.fdm_amount) as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

'' as '微信',

0 as '微信金额',

0 as '桌数',

0 as '就餐人数',

0 as '总台数',

0 as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

FROM rept_billpay billpay

where billpay.fdi_paymode=1 and billpay.fdm_amount<>0  and billpay.fdi_pmid<>1 AND billpay.fdi_pmid<>2 and  billpay.fdd_paydate between '2017/7/1' and '2017/7/7'  and billpay.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)   

GROUP BY billpay.fdi_ognid,billpay.fdi_pmid

UNION ALL

-- 支付宝

SELECT

billpay.fdd_paydate as '统计日期',

billpay.fdi_ognid as '门店ID',

billpay.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

0 as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

billpay.fdc_pmname as '支付宝',

SUM(billpay.fdm_amount) as '支付宝金额',

'' as '微信',

0 as '微信金额',

0 as '桌数',

0 as '就餐人数',

0 as '总台数',

0 as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

FROM rept_billpay billpay

where billpay.fdi_pmid=1 and billpay.fdm_amount>0 and  billpay.fdd_paydate between '2017/7/1' and '2017/7/7'  and billpay.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)   

GROUP BY billpay.fdi_ognid

UNION ALL

-- 微信

SELECT

billpay.fdd_paydate as '统计日期',

billpay.fdi_ognid as '门店ID',

billpay.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

0 as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

billpay.fdc_pmname as '微信',

SUM(billpay.fdm_amount) as '微信金额',

0 as '桌数',

0 as '就餐人数',

0 as '总台数',

0 as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

FROM rept_billpay billpay

where billpay.fdi_pmid=2 and billpay.fdm_amount>0 and  billpay.fdd_paydate between '2017/7/1' and '2017/7/7'  and billpay.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)   

GROUP BY billpay.fdi_ognid

UNION ALL

-- 营业实收

SELECT

billpay.fdd_paydate as '统计日期',

billpay.fdi_ognid as '门店ID',

billpay.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

SUM(billpay.fdm_amount) as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

'' as '微信',

0 as '微信金额',

0 as '桌数',

0 as '就餐人数',

0 as '总台数',

0 as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

FROM rept_billpay billpay

where billpay.fdi_paymode=1 and billpay.fdm_amount<>0 and  billpay.fdd_paydate between '2017/7/1' and '2017/7/7'  and billpay.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)   

GROUP BY billpay.fdi_ognid

UNION ALL

-- 菜类收入

SELECT

billdetail.fdd_paydate as '统计日期',

billdetail.fdi_ognid as '门店ID',

billdetail.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

0 as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

'' as '微信',

0 as '微信金额',

0 as '桌数',

0 as '就餐人数',

0 as '总台数',

0 as '餐段数',

fdc_categoryname as '菜品类别名称',

SUM(ifnull(dishesamount,0)+ifnull(packdetailcountamount,0)) as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

FROM rept_billdetailed billdetail  where  billdetail.fdd_paydate between '2017/7/1' and '2017/7/7'  and billdetail.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)    and billdetail.fdb_packageflag=0

GROUP BY billdetail.fdi_ognid,billdetail.fdi_categoryid

UNION ALL

-- 总台数

SELECT  

abcd.fdd_paydate as '统计日期',

abcd.fdi_ognid as '门店ID',

abcd.fdc_ognname as '门店名称',

0 as '销售流水',

0 as '赠送',

0 as '折扣',

0 as '满减优惠',

0 as '单品折扣',

0 as '抹零',

0 as '会员优惠',

0 as '特价优惠',

'-1' as '不计入实收的支付方式名称',

0 as '不计入实收金额',

0 as '营业实收',

'-1' as '计入实收名称',

0 as '计入实收的金额',

'' as '支付宝',

0 as '支付宝金额',

'' as '微信',

0 as '微信金额',

0 as '桌数',

0 as '就餐人数',

acac.`总台数`  as '总台数',

abcd.fdi_mealcount as '餐段数',

'-1' as '菜品类别名称',

0 as '菜品类别收入',

0 as '营业外收入',

'' as '服务费名称',

0 as '服务费',

'' as '茶位费名称',

0 as '茶位费'

from

(SELECT fdi_ognid,fdc_ognname,fdd_paydate,fdi_payptid,fdi_mealcount from rept_bill bill where  bill.fdd_paydate between '2017/7/1' and '2017/7/7'  and bill.fdi_ognid  in (2,4,5,6,3,8,9,10,11,12,13,14,15,17,16,7,21,22,23,24)    and  fdi_billstatus=1  GROUP BY fdi_ognid,fdd_paydate)abcd

INNER JOIN (SELECT fdi_ognid,COUNT(IF((fdc_tableno REGEXP '^[0-9]*$')=1,1,0)) '总台数' from sys_table where fdb_isnoopentable=1 GROUP BY fdi_ognid)acac on acac.fdi_ognid=abcd.fdi_ognid