有一个数据表(Sales),表结构与数据如下(SaleDate:销售日期 SaleCount:销售数量):
SaleDate SaleCount
2011.08.03 40
2011.08.04 22
2011.08.05 30
2011.08.06 35
2011.08.07 45
2011.08.08 47
.
.
.
2011.08.29 30
2011.08.30 36
2011.08.31 48若今天是2011.08.31,需要以7天为一周期往前推,统计出每个周期的销售数据,这样的语句怎么写?
谢谢各位大侠。想要得到的结果是如下每个时间段周期的销售数量:
2011.08.25 ~ 2011.08.31
2011.08.18 ~ 2011.08.24
2011.08.11 ~ 2011.08.17
2011.08.3 ~ 2011.08.10
解决方案 »
- 这个sql怎么写
- 向高手请教:汉字顺序排列问题和统计问题,在 线等,谢谢!
- 为什么数据库DATA文件中的zbpg_log.LDF文件会异常的大?
- 哪位先生或女士知道sql server2000 enterprise 英文版在哪下载吗?
- 关于delphi中使用事务引发后台触发器的疑问?
- SQL SERVER 2005 ROW_NUMBER() OVER() 排序无效
- 触发器执行不了
- SQL SERVER 2000 动态增加数据库表的列的问题,求救!!!
- help me! sql server中的linked server for ORACLE 用不来。
- 连接ms sql server 问题, 我用ado连接本机sql server, 在设计中没问题, 但一运行,就会出现,类型转换错误,(我一行代码都没写) 怎么回事?
- 数据文件大小问题,求解释!
- SQL查询问题。。。
sum(SaleCount) as SaleCount
from tb
group by datediff(dd,SaleDate,getdate())/7
不要重复发帖,试试看!
create table tb(SaleDate datetime,SaleCount int)
insert into tb
select '2011-08-23' ,40 union all
select '2011-08-24' ,22 union all
select '2011-08-25' ,30 union all
select '2011-08-26' ,35 union all
select '2011-08-27' ,45 union all
select '2011-08-28' ,47 union all
select '2011-08-29' ,30 union all
select '2011-08-30' ,36 union all
select '2011-08-31' ,48
go--2011.08.25 ~ 2011.08.31
--2011.08.18 ~ 2011.08.24
--2011.08.11 ~ 2011.08.17
--2011.08.3 ~ 2011.08.10select convert(varchar(10),min(SaleDate),120)+' ~ '+convert(varchar(10),max(SaleDate),120) as date,
sum(SaleCount) as SaleCount
from tb
group by datediff(dd,SaleDate,getdate())/7drop table tb/************date SaleCount
----------------------- -----------
2011-08-25 ~ 2011-08-31 271
2011-08-23 ~ 2011-08-24 62(2 行受影响)
insert into tb select '2011.08.03',40
insert into tb select '2011.08.04',22
insert into tb select '2011.08.05',30
insert into tb select '2011.08.06',35
insert into tb select '2011.08.07',45
insert into tb select '2011.08.08',47
insert into tb select '2011.08.09',40
insert into tb select '2011.08.10',22
insert into tb select '2011.08.11',30
insert into tb select '2011.08.12',35
insert into tb select '2011.08.13',45
insert into tb select '2011.08.14',47
insert into tb select '2011.08.15',40
insert into tb select '2011.08.16',22
insert into tb select '2011.08.17',30
insert into tb select '2011.08.18',35
insert into tb select '2011.08.19',45
insert into tb select '2011.08.20',47
insert into tb select '2011.08.21',30
insert into tb select '2011.08.22',35
insert into tb select '2011.08.23',45
insert into tb select '2011.08.24',47
insert into tb select '2011.08.25',40
insert into tb select '2011.08.26',22
insert into tb select '2011.08.27',30
insert into tb select '2011.08.28',35
insert into tb select '2011.08.29',45
insert into tb select '2011.08.30',47
insert into tb select '2011.08.31',48
go
select min(SaleDate),max(SaleDate),sum(SaleCount) from tb group by (datediff(d,SaleDate,'2011-08-31'))/7
/*
----------------------- ----------------------- -----------
2011-08-25 00:00:00.000 2011-08-31 00:00:00.000 267
2011-08-18 00:00:00.000 2011-08-24 00:00:00.000 284
2011-08-11 00:00:00.000 2011-08-17 00:00:00.000 249
2011-08-04 00:00:00.000 2011-08-10 00:00:00.000 241
2011-08-03 00:00:00.000 2011-08-03 00:00:00.000 40(5 行受影响)*/
go
drop table tb
convert(varchar(10),min(SaleDate),120)+' ~ '+convert(varchar(10),max(SaleDate),120) as date,sum(SaleCount) as SaleCount
from
tb
group by
datediff(dd,SaleDate,getdate())/7