请教一sql语句

[山野市民]

如表1
id（自增） name sale Volume date
1 张三 1000.00 4 2011-10-1
2 李四 1200.00 4 2011-10-1
3 王五 1000.00 2 2011-10-1
4 张三 0 0 2011-10-2
5 李四 2000.00 6 2011-10-2
6 王五 1500.00 4 2011-10-2
7 张三 3000.00 6 2011-10-3
8 李四 0 0 2011-10-3
9 王五 1700.00 6 2011-10-3

查询结果

id name sale Volume avg(sale) avg(Volume)
1 张三 4000.00 10 2000.00 5
2 李四 3200.00 10 1600.00 5
3 王五 4200.00 12 1400.00 4

备注： avg(sale) 为0的不算在内

[快溜]

select id=min(id),name,sale=sum(sale),Volume=sum(Volume),
       avg1=avg(sale),avg2=avg(Volume)
from where sale>0 tb group by name

[--小F--]

select
 min(id),name, sum(sale), sum(Volume),avg(sale), avg(Volume)
from
 tb
where
 sale>0  
group by
 name

[叶子]

declare @表1 table 
(id int identity(1,1),name varchar(4),sale numeric(6,2),Volume int,date datetime)
insert into @表1
select '张三',1000.00,4,'2011-10-1' union all
select '李四',1200.00,4,'2011-10-1' union all
select '王五',1000.00,2,'2011-10-1' union all
select '张三',0,0,'2011-10-2' union all
select '李四',2000.00,6,'2011-10-2' union all
select '王五',1500.00,4,'2011-10-2' union all
select '张三',3000.00,6,'2011-10-3' union all
select '李四',0,0,'2011-10-3' union all
select '王五',1700.00,6,'2011-10-3'

select min(id) as id,name,
sale=sum(sale),Volume=sum(Volume),
[avg(sale)]=sum(sale)/sum(case when sale=0 then 0 else 1 end),
[avg(Volume)]=sum(Volume)/sum(case when Volume=0 then 0 else 1 end)
from @表1 group by name order by 1
/*
id          name sale                                    Volume      avg(sale)                               avg(Volume)
----------- ---- --------------------------------------- ----------- --------------------------------------- -----------
1           张三   4000.00                                 10          2000.000000                             5
2           李四   3200.00                                 10          1600.000000                             5
3           王五   4200.00                                 12          1400.000000                             4
*/

[pengxuan]

if object_id('tb') is not null
   drop table tb
go
create table tb
(
 id int identity(1,1),
 name varchar(10),
 sale numeric(9,2),
 volume int,
 date datetime
)
go
insert into tb(name,sale,volume,date)
select '张三',1000,4,'2011-10-1' union all
select '李四',1200,4,'2011-10-1' union all
select '王五',1000,2,'2011-10-1' union all
select '张三',0,0,'2011-10-2' union all
select '李四',2000,6,'2011-10-2' union all
select '王五',1500,4,'2011-10-2' union all
select '张三',3000,6,'2011-10-3' union all
select '李四',0,0,'2011-10-3' union all
select '王五',1700,6,'2011-10-3'
go
select id=row_number() over(order by getdate()),name,sale=sum(sale),volume=sum(volume),[avg(sale)]=avg(sale),[avg(volume)]=avg(volume) from tb where volume>0 group by name
go
/*
id                   name       sale                                    volume      avg(sale)                               avg(volume)
-------------------- ---------- --------------------------------------- ----------- --------------------------------------- -----------
1                    李四         3200.00                                 10          1600.000000                             5
2                    王五         4200.00                                 12          1400.000000                             4
3                    张三         4000.00                                 10          2000.000000                             5

(3 行受影响)
*/