declare @bdate datetime,@edate datetime declare @n int set @bdate='2005/01/01' set @edate=dateadd(yy,1,@bdate) select top 366 identity ( int ) as datenum into #t from dbo.syscolumns a, dbo.syscolumns b
select convert(char(10),dateadd( d ,datenum-1, @bdate ),111) date into #T2 from #t select UserID,TTime.date,sum(Duration) from (select * from #t2 where date>=@bdate and date<@edate) as TTime inner join yourtable on yourtable.convert(char(10),LoginTime,111)= TTime.date group by UserID,TTime.datedrop table #t drop table #t2
--建立测试环境 Create Table 表(UserID varchar(10),LoginTime datetime,Duration integer) --插入数据 insert into 表 select '1','2001-01-01 12:00:00','30' union select '1','2001-01-01 20:00:01','56' union select '1','2001-01-20 2:00:00','10' select * from 表 --测试语句 select userid,sum(duration)as duration,convert(char(10),logintime,120)as Date from 表 group by userid,convert(char(10),logintime,120) having userid=1 --删除测试环境 Drop Table 表
哈杂!估计作者是用Oracle或MySql,对于这个问题要弄清楚几个地方: 1.已知的查询条件:UserID,年份; 2.LoginTime格式必须是yy-mm-dd hh-mm-ss; 3.Duration最好是秒为单位,这样统计流量更准确; ==================================================== 我以前做过每天各个时段的短信上行流量统计,原理差不多.以下是我给的解法,(可能需要些调试) select datepart(mm,LoginTime),datepart(dd,LoginTime),sum(Duration) from TableName where UserID='User1' and datepart(yy,LoginTime)='2004' group by datepart(mm,LoginTime),datepart(dd,LoginTime) order by datepart(mm,LoginTime),datepart(dd,LoginTime) asc
--建立测试环境 Create Table 表(UserID varchar(10),LoginTime datetime,Duration integer) --插入数据 insert into 表 select '1','2001-01-01 12:00:00','30' union select '1','2001-01-01 20:00:01','56' union select '1','2001-01-20 2:00:00','10' select * from 表 --测试语句 select userid,sum(duration)as duration,convert(char(10),logintime,120)as Date from 表 group by userid,convert(char(10),logintime,120) having userid=1 --删除测试环境 Drop Table 表
declare @n int
set @bdate='2005/01/01'
set @edate=dateadd(yy,1,@bdate)
select top 366 identity ( int ) as datenum
into #t from dbo.syscolumns a, dbo.syscolumns b
select convert(char(10),dateadd( d ,datenum-1, @bdate ),111) date into #T2 from #t select UserID,TTime.date,sum(Duration) from (select * from #t2 where date>=@bdate and date<@edate) as TTime inner join yourtable on yourtable.convert(char(10),LoginTime,111)= TTime.date
group by UserID,TTime.datedrop table #t
drop table #t2
Create Table 表(UserID varchar(10),LoginTime datetime,Duration integer)
--插入数据
insert into 表
select '1','2001-01-01 12:00:00','30' union
select '1','2001-01-01 20:00:01','56' union
select '1','2001-01-20 2:00:00','10'
select * from 表
--测试语句
select userid,sum(duration)as duration,convert(char(10),logintime,120)as Date from 表
group by userid,convert(char(10),logintime,120)
having userid=1
--删除测试环境
Drop Table 表
1.已知的查询条件:UserID,年份;
2.LoginTime格式必须是yy-mm-dd hh-mm-ss;
3.Duration最好是秒为单位,这样统计流量更准确;
====================================================
我以前做过每天各个时段的短信上行流量统计,原理差不多.以下是我给的解法,(可能需要些调试)
select datepart(mm,LoginTime),datepart(dd,LoginTime),sum(Duration) from TableName
where UserID='User1' and datepart(yy,LoginTime)='2004'
group by datepart(mm,LoginTime),datepart(dd,LoginTime)
order by datepart(mm,LoginTime),datepart(dd,LoginTime) asc
Create Table 表(UserID varchar(10),LoginTime datetime,Duration integer)
--插入数据
insert into 表
select '1','2001-01-01 12:00:00','30' union
select '1','2001-01-01 20:00:01','56' union
select '1','2001-01-20 2:00:00','10'
select * from 表
--测试语句
select userid,sum(duration)as duration,convert(char(10),logintime,120)as Date from 表
group by userid,convert(char(10),logintime,120)
having userid=1
--删除测试环境
Drop Table 表