有两张表: 客户表customer: id 客户编号(主键) name 客户名称 销售数据表sell: id 销售单号 customer_id 客户编号(外键) name 商品名称 count 商品数量 price 商品价格 total 商品总价格(count*price得到) date 销售时间 一个客户可以有多条销售记录,现在要查询一个时间段内的销售记录,产生如下的查询结果,请问SQL如何写: 客户编号§客户名称§销售记录总数量§销售总价格
select a.id as 客户编号, a.name as 客户名称, (select sum([count]) from sell where b.id=id ) as 销售记录总数量,(select sum([total]) from sell where b.id=id ) as 销售总价格 from customer a,sell b
where a.id=b.customer_id and date between @begintime and @endtime
group by a.id,a.name
select a.id as 客户编号, a.name as 客户名称, (select sum([count]) from sell where b.id=id ) as 销售记录总数量,(select sum([total]) from sell where b.id=id ) as 销售总价格 from customer a,sell b
where a.id=b.customer_id
group by a.id,a.name
select a.客户编号,a.客户名称,
总数量 = sum(b.[count]),
总价格 = sum(b.[count]*b.price)
from customer as a left join sell as b on a.id = b.customerID
WHERE [date] between @起始日期 and @截止日期 /*指定时间段*/
group by a.客户编号,a.客户名称
select a.客户编号,a.客户名称,
总数量 = sum(b.[count]),
总价格 = sum(b.[count]*b.price)
from customer as a left join sell as b on a.id = b.customerID
WHERE [date] >= @起始日期 and [date]<=@截止日期
group by a.客户编号,a.客户名称