22,300
社区成员




--> 测试数据:#customer
if object_id('tempdb.dbo.#customer') is not null drop table #customer
create table #customer([客户代码] int,[客户名称] varchar(6),[客户分类] varchar(1))
insert #customer
select 545,'蒋少鹏','C' union all
select 338,'王于平','F' union all
select 567,'曹坤','C' union all
select 448,'蒋宗强','G' union all
select 447,'赵丽','E' union all
select 123,'刘小珂','C' union all
select 130,'曹王扬','C' union all
select 132,'王平','C' union all
select 136,'蒲川','C' union all
select 142,'韩小淼','C'
--> 测试数据:#order
if object_id('tempdb.dbo.#order') is not null drop table #order
create table #order([日期] datetime,[客户代码] int,[客户名称] varchar(6),[商品] varchar(6),[买量] int)
insert #order
select '20161010',545,'蒋少鹏','红糖',19 union all
select '20161010',338,'王于平','白糖',67 union all
select '20161010',567,'曹坤','白糖',98 union all
select '20161010',448,'蒋宗强','白糖',11 union all
select '20161010',447,'赵丽','白糖',250 union all
select '20161010',123,'刘小珂','红糖',160 union all
select '20161010',130,'曹王扬','白糖',19 union all
select '20161010',132,'王平','枣子',8 union all
select '20161010',136,'蒲川','白糖',20 union all
select '20161010',142,'韩小淼','白糖',8 union all
select '20161010',142,'韩小淼','黑胡椒',7 union all
select '20161010',142,'韩小淼','辣椒',6
SELECT a.客户分类, b.日期, b.商品, SUM(b.买量) 买量
FROM #customer a
LEFT JOIN #order b
ON b.客户代码 = a.客户代码
GROUP BY a.客户分类, b.日期, b.商品;
/*
客户分类 日期 商品 买量
C 2016-10-10 00:00:00.000 白糖 145
C 2016-10-10 00:00:00.000 黑胡椒 7
C 2016-10-10 00:00:00.000 红糖 179
C 2016-10-10 00:00:00.000 辣椒 6
C 2016-10-10 00:00:00.000 枣子 8
E 2016-10-10 00:00:00.000 白糖 250
F 2016-10-10 00:00:00.000 白糖 67
G 2016-10-10 00:00:00.000 白糖 11
*/
select 客户分类,商品,SUM(买量) as 小计
from 客户表 A
join 销售表 B on A.客户代码=B.客户代码
where 日期='20161010'
group by 客户分类,商品