首页 新闻 搜索 专区 学院

求一条sql语句

0
悬赏园豆:30 [已解决问题] 解决于 2008-08-13 14:14
<P>情况是这样的,一个话单表中每天有好些记录,我想先把每天话单里的的金额加起来,然后比如说要算一个5天的平均值,那我用5天的每天总计加起来/5</P> <P>&nbsp;</P> <P>&nbsp;</P>
Jerry Qian的主页 Jerry Qian | 初学一级 | 园豆:19
提问于:2008-08-04 16:07
< >
分享
最佳答案
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
roboth | 初学一级 |园豆:28 | 2008-08-05 14:02
其他回答(6)
0
select sum(金额) from 话费表 where 日期字段=指定的日期 这样可以吗?
张荣华 | 园豆:2020 (老鸟四级) | 2008-08-04 16:23
0
select avg(金额)from 话费表 where 日期字段=指定的日期
Tony Lu | 园豆:32 (初学一级) | 2008-08-04 16:52
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 看这个是不是你想要的。
MartinGao | 园豆:663 (小虾三级) | 2008-08-04 16:52
0
select sum(金额)/5 from 话费表 where 日期字段>=指定的日期 and 日期字段<指定的日期+5
jackzhang | 园豆:190 (初学一级) | 2008-08-04 17:16
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
侯垒 | 园豆:3435 (老鸟四级) | 2008-08-04 17:31
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 前面几天的和 除以天数
小眼睛老鼠 | 园豆:2774 (老鸟四级) | 2008-08-04 20:42
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册