求一条sql语句

Question

<P>情况是这样的,一个话单表中每天有好些记录,我想先把每天话单里的的金额加起来,然后比如说要算一个5天的平均值,那我用5天的每天总计加起来/5</P> <P>&nbsp;</P> <P>&nbsp;</P>

Answer 1

lz说的有点晕乎。 看看这个如何》？ --create table DECLARE @t table(id int,Phone varchar(11),dte smalldatetime,cost int) --prepare data insert into @t select 1,'13146653802',GETDATE(),132 UNION ALL SELECT 2,'13146653803',DATEADD(hour,1,GETDATE()),1423 UNION ALL SELECT 3,'13146653804',DATEADD(hour,2,GETDATE()),41 UNION ALL SELECT 4,'13146653805',DATEADD(hour,3,GETDATE()),412 UNION ALL SELECT 5,'13146653806',DATEADD(day,1,GETDATE()),543 UNION ALL SELECT 6,'13146653807',DATEADD(day,1,GETDATE()),64 --display all data select * from @t --split dte SELECT * ,Year(dte) as y ,Month(dte) as m,Day(dte) as d FROM @t --group by dte's item SELECT a.y,a.m,a.d,SUM(a.cost) as cst FROM (SELECT * ,Year(dte) as y ,Month(dte) as m,Day(dte) as d FROM @t) a GROUP BY a.y,a.m,a.d --select data between 5 and 6 SELECT * FROM ( SELECT a.y,a.m,a.d,SUM(a.cost) as cst FROM (SELECT * ,Year(dte) as y ,Month(dte) as m,Day(dte) as d FROM @t) a GROUP BY a.y,a.m,a.d )b where b.d between 5 and 6

Answer 2

Days Cost 2008-6-5 0:00:00 100 2008-6-6 0:00:00 50 2008-6-7 0:00:00 63 2008-6-8 0:00:00 95 2008-6-9 0:00:00 24 create proc CalcAverage @StartTime datetime, @Endtime datetime as declare @num float select @num=sum(Cost) from PhoneList where Days between @StartTime and @Endtime select @num/5 go CalcAverage '2008-06-05','2008-06-09' 结果：66.4 看这个是不是你想要的。