table1字段1: time1时间类型 某一天 字段2:moeny1货币类型 营业额表样式
time1 money1
2013-01-01 5.00
2013-01-02 3.50
2013-02-01 4.00
2014-01-01 3.40求统计 日营业额 sql
周营业额 sql
月营业额 sql
季营业额 sql
年营业额 sql
time1 money1
2013-01-01 5.00
2013-01-02 3.50
2013-02-01 4.00
2014-01-01 3.40求统计 日营业额 sql
周营业额 sql
月营业额 sql
季营业额 sql
年营业额 sql
select sum(money1)money1,datepart(wk,time1)wk from table1 group by datepart(wk,time1)
--其他类似
季度的时间范围来汇总
一个表:字段,年 int ,月 int , 日 int ,季度 int ,周 int ,然后time1 字段,money1 字段。
如果不设计的话,那你得这么查:
select sum(money1) from table group by datepart(dd,time1)
union all
select sum(money1) from table group by datepart(weekday,time1)
union all
一直到年
--select year(time1)年,datepart(wk,time1)周,sum(money1) from tb group by year(time1),datepart(wk,time1)//周
--select convert(varchar(7),time1,120),sum(money1) from tb group by convert(varchar(7),time1,120)//月
--select year(time1)年,'第'+rtrim(month(time1)/3%4+1)+'季度'季度,sum(money1) from tb group by year(time1),month(time1)/3%4//季度
--select year(time1)年,sum(money1) from tb group by year(time1)//年