各种按日期、时间段统计SQL语句

--前一日
select *
from 表名
where datediff(d,cast(日期 as datetime),getdate())=1

--上周
select *
from 表名 where
datediff(ww,cast(日期 as datetime),getdate())=1

--上周
select *
from 表名
where DATEPART(ww, 日期) = DATEPART(ww, GETDATE())-1 and
      DATEPART(yy, 日期) = DATEPART(yy, GETDATE())

--上月
select *
from 表名
where datediff(month,cast(日期 as datetime),getdate())=1

--上年
select *
from 表名
where datediff(year,cast(日期 as datetime),getdate())=1

--按日统计
select count(*),day([date])
from 表名
where year([date]) = '2006'
group by day([date])

--按周
select count(*),datename(week,[date])
from 表名
where year([date]) = '2006'
group by datename(week,[date])

--按月
select count(*),month([date])
from 表名
where year([date]) = '2006'
group by month([date])

--按季
select count(*),datename(quarter,[date])
from 表名
where year([date]) = '2006'
group by datename(quarter,[date])

--按年
select count(*),year([date])
from 表名
where year([date]) >= '2000' group by year([date])

参考：