最好先做一个视图,把两个表关联起来,然后在视图的基础上做查询就简单很多了。
视图: select t1.*,t2.人数,t2.其他 from t1,t2 where t1.流水号=t2.流水号
然后用group by 流水号 可以计算出你要的全部咚咚!
视图: select t1.*,t2.人数,t2.其他 from t1,t2 where t1.流水号=t2.流水号
然后用group by 流水号 可以计算出你要的全部咚咚!
客人总数=(select sum(人数)as 人数 from (select distinct 流水号,renshu from 消费记录表) a ),
总消费时间=(select 时间=sum(DATEDIFF(minute,开始时间,结束时间)) from 结算表),
总消费额=(select sum(消费总额)as 总额 from 结算表),
平均消费=(case when (select sum(人数)as 人数 from (select distinct 流水号,人数 from 消费记录表) a )=0 then 0
else
(select sum(消费总额)as 总额 from 结算表 )/(select sum(人数)as 人数 from (select distinct 流水号,人数 from 消费记录表) a )end),
平均消费时间=(select 时间=sum(DATEDIFF(minute,开始时间,结束时间)) from 结算表)/(select count(*)from 结算表),
客次=(select count(*)from 结算表)
from 消费记录表
sum(a.消费时间) as 总消费时间,
sum(a.消费金额) as 总消费额,
case when sum(a.消费时间)=0 then 0
else sum(a.消费金额)/sum(a.消费时间)end as 平均消费,
sum(a.消费时间)/count(b.流水号) as 平均消费时间,
count(b.流水号) as 总客次
from (select 流水号,datediff(mi,结束时间,开始时间) as 消费时间,消费金额
from 结算表 )a
inner join (select 流水号,人数 from 消费记录表 group by 流水号,人数 ) b
on a.流水号=b.流水号