select starttime,sum([count]) as count from [user] group by starttime order by statttime
select convert(char(10),starttime,120),sum(count) as count from [user] where starttime>='2002-06-1' and starttime<'2002-07-01' group by convert(char(10),starttime,120) order by convert(char(10),starttime,120)
上一周: select convert(char(10),starttime,120),sum(count) as count from [user] where starttime>=dateadd(dd,-7,convert(datetime,'2002-06-29')) and starttime<'2002-06-29' group by convert(char(10),starttime,120) order by convert(char(10),starttime,120)上个月 select convert(char(10),starttime,120),sum(count) as count from [user] where starttime>=dateadd(mm,-1,convert(datetime,'2002-06-1')) and starttime<'2002-06-01' group by convert(char(10),starttime,120) order by convert(char(10),starttime,120)
--上个月 select convert(char(10),starttime,120) as starttime,sum(count) as count from [user] where starttime>=dateadd(m,-1,convert(char(10),dateadd(d,-day(getdate())+1,getdate()),120)) and starttime<convert(char(10),dateadd(d,-day(getdate())+1,getdate()),120) group by convert(char(10),starttime,120) order by convert(char(10),starttime,120)
select sum(count) as count, convert(char(10),starttime,120) as StartDate from Table1 where datepart(ww,starttime)=20 --- 20th week ---convert(char(7),starttime,120)='2002-06' --- month group by convert(char(10),starttime,120) order by convert(char(10),starttime,120)
last week: where datepart(ww,starttime)=datepart(ww,getdate())-1
--上周SET DATEFIRST 1 --规定的每周第一天:1 对应星期一,2 对应星期二,依次类推,用 7 对应星期日。select convert(char(10),starttime,120) as starttime,sum(count) as count from [user] where starttime>=convert(char(10),dateadd(d,-datepart(dw,getdate())+1-7,getdate()),120) and starttime<convert(char(10),dateadd(d,-datepart(dw,getdate())+1,getdate()),120) group by convert(char(10),starttime,120) order by convert(char(10),starttime,120)
group by starttime
order by statttime
where starttime>='2002-06-1' and starttime<'2002-07-01'
group by convert(char(10),starttime,120)
order by convert(char(10),starttime,120)
得到上一个月的每一天的统计记录,又该怎么做
select convert(char(10),starttime,120),sum(count) as count from [user]
where starttime>=dateadd(dd,-7,convert(datetime,'2002-06-29'))
and starttime<'2002-06-29'
group by convert(char(10),starttime,120)
order by convert(char(10),starttime,120)上个月
select convert(char(10),starttime,120),sum(count) as count from [user]
where starttime>=dateadd(mm,-1,convert(datetime,'2002-06-1'))
and starttime<'2002-06-01'
group by convert(char(10),starttime,120)
order by convert(char(10),starttime,120)
select convert(char(10),starttime,120) as starttime,sum(count) as count from [user]
where starttime>=dateadd(m,-1,convert(char(10),dateadd(d,-day(getdate())+1,getdate()),120))
and starttime<convert(char(10),dateadd(d,-day(getdate())+1,getdate()),120)
group by convert(char(10),starttime,120)
order by convert(char(10),starttime,120)
from Table1
where datepart(ww,starttime)=20 --- 20th week
---convert(char(7),starttime,120)='2002-06' --- month
group by convert(char(10),starttime,120)
order by convert(char(10),starttime,120)
where datepart(ww,starttime)=datepart(ww,getdate())-1
--规定的每周第一天:1 对应星期一,2 对应星期二,依次类推,用 7 对应星期日。select convert(char(10),starttime,120) as starttime,sum(count) as count from [user]
where starttime>=convert(char(10),dateadd(d,-datepart(dw,getdate())+1-7,getdate()),120)
and starttime<convert(char(10),dateadd(d,-datepart(dw,getdate())+1,getdate()),120)
group by convert(char(10),starttime,120)
order by convert(char(10),starttime,120)
select @day='2002-02-02' --任意一天
select datename(wk,@day)....
where datename(wk,@day) - datename(wk,starttime)=1 --1周
....
有索引的话,用不到,我的方法能用到索引,加快速度。select datepart(ww,'2002-06-29'),datepart(ww,'2001-6-29')都返回26。
DATEPART(wk, 'Jan 1, xxxx') = 1,此处 xxxx 代表任一年。
如果有多年的数据,下面的语句是错误的。
last week: where datepart(ww,starttime)=datepart(ww,getdate())-1默认每周第一天是星期日,要用SET DATEFIRST设成星期一。