SQL语句练习实例之三——平均销售等待时间

复制代码代码如下:
---1.平均销售等待时间
---有一张Sales表，其中有销售日期与顾客两列，现在要求使用一条SQL语句实现计算
--每个顾客的两次购买之间的平均天数
--假设：在同一个人在一天中不会购买两次
create table sales
（
custname varchar（10） not null,
saledate datetime not null
）
go
insert sales
select "张三","2010-1-1" union
select "张三","2010-11-1" union
select "张三","2011-1-1" union
select "王五","2010-2-1" union
select "王五","2010-4-1" union
select "李四","2010-1-1" union
select "李四","2010-5-1" union
select "李四","2010-9-1" union
select "李四","2011-1-1" union
select "赵六","2010-1-1" union
select "钱途","2010-1-1" union
select "钱途","2011-3-1" union
select "张三","2011-9-1"
go
select custname,DATEDIFF（d,min（saledate）,max（saledate））/（COUNT（*）-1） as avgday
from sales
group by custname
having count（*）>1
go
select custname,case when count（*）>1 then DATEDIFF（d,min（saledate）,max（saledate））/（COUNT（*）-1）
else DATEDIFF（d,min（saledate）,max（saledate）） end
as avgday
from sales
group by custname
--having count（*）>1
go
drop table sales