select j.流水号,j.桌号,w.数量 * w.价格 as 物耗金额,x.服务员,j.总额
from 结算表 as j
left 消费表 as x on j.流水号 = x.流水号
left 物耗表 as w on j.流水号 = w.流水号
group by
j.流水号
from 结算表 as j
left 消费表 as x on j.流水号 = x.流水号
left 物耗表 as w on j.流水号 = w.流水号
group by
j.流水号
from
结算表 a
left join
(select 流水号,金额=sum(数量*价格) from 物耗 b group by 流水号 )b on a.流水号=b.流水号
left join
(select 流水号,max(服务员)as 服员 from 消费记录 c group by 流水号)c on a.流水号=c.流水号
,c.物耗金额
,服务员=(select top 1 服务员 from 消费记录 where 流水号=a.流水)
,a.总额
from 结算表 a,(
select 流水号,物耗金额=sum(数量*价格)
from 物耗 group by 流水号
)c where a.流水号=c.流水号