sqlserver 根据两个表的数据，进行数据汇总的存储过程写法

--> 测试数据：#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 客户分类,商品