报表查询 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
收起