急求按产品分类进行销售统计的sql

[eyebyeye]

产品及类别表 product
id pid code name
1 0 A
2 1 01 B
3 1 02 C
4 2 0101 D
5 3 0201 E
6 1 0001 F
........

其中code编码很长，根据分类自动增长,有的根目录也会挂接产品，如id=6是A类的直属产品

销售表 sale
id productId num price
1 4 2 5.5
2 5 4 6.5
3 6 1 20
......

现在老板要求将销售结果按产品类聚类统计出来，如
name totalNum totalPrice
A 7 57.0
B 2 11.0
C 4 26.0
D 2 11.0
E 4 26.0
F 1 20.0
对于上述的sql实现最好给出通用的实现方法，万分感激！

[pt1314917]

--> 测试数据: [product]
if object_id('[product]') is not null drop table [product]
create table [product] (id int,pid int,code varchar(4),name varchar(1))
insert into [product]
select 1,0,null,'A' union all
select 2,1,'01','B' union all
select 3,1,'02','C' union all
select 4,2,'0101','D' union all
select 5,3,'0201','E' union all
select 6,1,'0001','F'
--> 测试数据: [sale]
if object_id('[sale]') is not null drop table [sale]
create table [sale] (id int,productId int,num int,price numeric(8,1))
insert into [sale]
select 1,4,2,5.5 union all
select 2,5,4,6.5 union all
select 3,6,1,20

;with wsp
as
(
	select a.id,pid,name,num,totalprice=num*price from product a,sale b where a.id=b.productid
	union all
	select a.id,a.pid,a.name,num=b.num,totalprice=b.totalprice from product a,wsp b where a.id=b.pid
)	
select name,totalNum=sum(num),totalPrice=sum(totalprice)  from wsp group by name

/*结果：
name totalNum    totalPrice
---- ----------- ---------------------------------------
A    7           57.0
B    2           11.0
C    4           26.0
D    2           11.0
E    4           26.0
F    1           20.0
*/