求一条sql语句
0
悬赏园豆：30
[已解决问题]
解决于 2008-08-13 14:14
<P>情况是这样的,一个话单表中每天有好些记录,我想先把每天话单里的的金额加起来,然后比如说要算一个5天的平均值,那我用5天的每天总计加起来/5</P>
<P> </P>
<P> </P>
最佳答案
0
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
其他回答(6)
0
select sum(金额) from 话费表 where 日期字段=指定的日期
这样可以吗？
0
select avg(金额）from 话费表 where 日期字段=指定的日期
0
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
看这个是不是你想要的。
0
select sum(金额)/5 from 话费表 where 日期字段>=指定的日期 and 日期字段<指定的日期+5
0
declare @start datetime
declare @day int
set @day=5
set @start='2008-7-22'
select sum(金额)/@day from 话费表 where 日期字段 between @start and @start+@day
0
select sum(money) from table group by date
如果date本来就是一天天的记录，没有时分秒，获取每天的金额
--------------------------------
如果date是一个实时的时间的话 有时分秒的情况 通过函数CONVERT(varchar(12) , date, 110 )
截取出只有日期的部分
select sum(money) as [money] , CONVERT(varchar(12) , date, 110 )
date from table group by CONVERT(varchar(12) , date, 110 )
每天的金额
-------------------------------
select avg(a.[money]) from (select sum(money) as [money] , CONVERT(varchar(12) , date, 110 )
date from table group by CONVERT(varchar(12) , date, 110 )
select avg(money) from table group by date
) a
几天以来的平均金额
------------------------------
每天的记录结果（我随便找了个表）
5577 06-26-2008
2805 06-27-2008
60372 09-05-2007
4745 09-06-2007
7995 11-22-2007
5389 12-04-2007
16431 12-07-2007
----------------------------------
品均记录
14759
前面几天的和 除以天数
