8:00属于 7:00-8:00
也就是计算的时候不是 between 开始时间 and 结束时间而是
>开始时间 and <= 结束时间
也就是计算的时候不是 between 开始时间 and 结束时间而是
>开始时间 and <= 结束时间
解决方案 »
- 求一SQL语句
- 各位老大,如果利用存储过程向SQLSERVER2000内插入图片呢?
- sqlserver 2k 在win2k3中安装的问题!
- 一个关于sql的综合应用的问题
- 由于数据移动,未能继续以nolock方式进行扫描
- 两个个人版SQL Server2000可以设置数据同步吗?
- 如何查看一个软件系统操作中涉及到相关的表和列?
- 在线等待一个存储过程和一个触发器的问题
- 在MSSQL2000中如何用存储过程删除或压缩主日志文件?
- vfp里面的select 1,use *.dbf是不是只要申请了一次,在以后的程序中,用select 1就表示use *.dbf吗?
- 菜鸟求教!急急急
- 请教一个交叉查询语句
declare @ss int --间隔时间
declare @ee datetime --结束时间
declare @tt datetime --临时变量
set @hh = '2005-9-1 4:00:00'
set @ss = 3
set @ee = '2005-9-1 18:00:00'
set @tt = @hh
while @tt <= @ee
begin
set @hh = dateadd(hh,@ss,@hh)
select
cast(min(datepart(yy,saledate)) as nvarchar(50)) + '年' +
cast(min(datepart(mm,saledate)) as nvarchar(50)) + '月' +
cast(min(datepart(dd,saledate)) as nvarchar(50)) + '日' +
cast(min(datepart(hh,saledate)) as nvarchar(50)) + '时' +
+ ' - ' + cast(min(datepart(hh,saledate))+1 as nvarchar(50)) + '时',
sum(quantity)
from #sales
group by datepart(hour,saledate)
having datepart(hour,saledate) > datepart(hour,@hh) - @ss
and datepart(hour,saledate) <= datepart(hour,@hh)
set @tt = dateadd(hh,@ss,@tt)
end
declare @hh datetime --开始时间
declare @ss int --间隔时间
declare @ee datetime --结束时间
declare @tt datetime --临时变量
set @hh = '2005-9-1 4:00:00'
set @ss = 3
set @ee = '2005-9-1 18:00:00'
set @tt = @hh
while @tt <= @ee
begin
set @hh = dateadd(hh,@ss,@hh)
select productid,
cast(min(datepart(yy,saledate)) as nvarchar(50)) + '年' +
cast(min(datepart(mm,saledate)) as nvarchar(50)) + '月' +
cast(min(datepart(dd,saledate)) as nvarchar(50)) + '日' +
cast(min(datepart(hh,saledate)) as nvarchar(50)) + '时' +
+ ' - ' + cast(min(datepart(hh,saledate))+1 as nvarchar(50)) + '时',
sum(quantity)
from #sales
group by datepart(hour,saledate),productid
having datepart(hour,saledate) > datepart(hour,@hh) - @ss
and datepart(hour,saledate) <= datepart(hour,@hh)
order by productid
set @tt = dateadd(hh,@ss,@tt)
end
这个完全符合要求
declare @hh datetime --开始时间
declare @ss int --间隔时间
declare @ee datetime --结束时间
declare @tt datetime --临时变量
declare @or int
declare @tb table(Oid int,productid nvarchar(4),timen nvarchar(100),amount nvarchar(50))
set @hh = '2005-9-1 12:00:00'
set @ss = 3
set @ee = '2005-9-1 18:00:00'
set @tt = @hh
set @or = 1
while @tt <= @ee
begin
set @hh = dateadd(hh,@ss,@hh)
insert @tb
select cast(@or as nvarchar(4)),cast(productid as nvarchar(30)),
cast(min(datepart(yy,saledate)) as nvarchar(50)) + '年' +
cast(min(datepart(mm,saledate)) as nvarchar(50)) + '月' +
cast(min(datepart(dd,saledate)) as nvarchar(50)) + '日' +
cast(min(datepart(hh,saledate)) as nvarchar(50)) + '时' +
+ ' - ' + cast(min(datepart(hh,saledate))+1 as nvarchar(50)) + '时' ,
cast(sum(quantity) as nvarchar(50))
from #sales
group by datepart(hour,saledate),productid
having datepart(hour,saledate) > datepart(hour,@hh) - @ss
and datepart(hour,saledate) <= datepart(hour,@hh)
union select 1,'---','------------------------','--------'
set @tt = dateadd(hh,@ss,@tt)
set @or = @or +1
end
select Productid,timen,amount from @tb
下面的代码就行了declare @hh datetime --开始时间
declare @ss int --间隔时间
declare @ee datetime --结束时间
declare @tt datetime --临时变量
declare @or int
declare @tb table(Oid int,productid nvarchar(4),timen nvarchar(100),amount nvarchar(50))
set @hh = '2005-9-1 7:00:00'
set @ss = 1
set @ee = '2005-9-1 18:00:00'
set @tt = @hh
set @or = 1
while @tt <= @ee
begin
set @hh = dateadd(hh,@ss,@hh)
insert @tb
select cast(@or as nvarchar(4)),cast(productid as nvarchar(30)),
cast(min(datepart(yy,@hh)) as nvarchar(50)) + '年' +
cast(min(datepart(mm,@hh)) as nvarchar(50)) + '月' +
cast(min(datepart(dd,@hh)) as nvarchar(50)) + '日' +
cast(min(datepart(hh,@hh)) as nvarchar(50)) + '时' +
+ ' - ' + cast(min(datepart(hh,@hh))+@ss as nvarchar(50)) + '时',
cast(sum(quantity) as nvarchar(50))
from #sales
where datepart(hour,saledate) > datepart(hour,@hh) - @ss
and datepart(hour,saledate) <= datepart(hour,@hh)
group by productidset @tt = dateadd(hh,@ss,@tt)
set @or = @or +1
end
select Productid,timen,amount from @tb order by productid,oid在
set @hh = '2005-9-1 7:00:00'
set @ss = 3
set @ee = '2005-9-1 18:00:00'
时
结果
/----------------------
1 2005年9月1日10时 - 13时 22
1 2005年9月1日13时 - 16时 15
1 2005年9月1日16时 - 19时 20
2 2005年9月1日10时 - 13时 20
2 2005年9月1日16时 - 19时 30
---------------------------------/
刚刚有人告诉我还是错了
:-P 汗,努力学习中
修改版本
declare @hh datetime --开始时间
declare @ss int --间隔时间
declare @ee datetime --结束时间
declare @tt datetime --临时变量
declare @or int
declare @tb table(Oid int,productid nvarchar(4),timen nvarchar(100),amount nvarchar(50))
set @hh = '2005-9-1 7:00:00'
set @ss = 3
set @ee = '2005-9-1 18:00:00'
set @tt = @hh
set @or = 1
while @tt <= @ee
begin
insert @tb
select cast(@or as nvarchar(4)),cast(productid as nvarchar(30)),
cast(min(datepart(yy,@hh)) as nvarchar(50)) + '年' +
cast(min(datepart(mm,@hh)) as nvarchar(50)) + '月' +
cast(min(datepart(dd,@hh)) as nvarchar(50)) + '日' +
cast(min(datepart(hh,@hh))-@ss as nvarchar(50)) + '时' +
+ ' - ' + cast(min(datepart(hh,@hh)) as nvarchar(50)) + '时',
cast(sum(quantity) as nvarchar(50))
from #sales
where datepart(hour,saledate) > datepart(hour,@hh) - @ss
and datepart(hour,saledate) <= datepart(hour,@hh)
group by productidset @hh = dateadd(hh,@ss,@hh)
set @tt = dateadd(hh,@ss,@tt)
set @or = @or +1
end
select Productid,timen,amount from @tb order by productid,oid
我试了一下,我试着增加了两条记录(不是同一天的)
insert into #sales values ('001','2005-9-2 14:00:00',20)
insert into #sales values ('002','2005-9-2 15:00:00',30)结果得出的结果明显出错
--测试环境
create table #sales (productid varchar(20),saledate datetime,quantity int)
insert into #sales values ('001','2005-9-1 08:00:00',10)
insert into #sales values ('001','2005-9-1 09:00:00',12)
insert into #sales values ('001','2005-9-1 13:00:00',15)
insert into #sales values ('001','2005-9-1 14:00:00',20)
insert into #sales values ('002','2005-9-1 07:00:00',10)
insert into #sales values ('002','2005-9-1 10:00:00',20)
insert into #sales values ('002','2005-9-1 15:00:00',30)
--查询(注意:我把08:00数据向前推了3个小时)
select productid,
saledate=convert(varchar(20),dateadd(hour,-3,A.saledate),120)+'-'+convert(varchar(20),saledate,120),
quantity=(select sum(quantity) from #sales where productid=A.productid and saledate>dateadd(hour,-3,A.saledate) and saledate<=A.saledate )
from #sales A
where productid='001'
union all
select productid,
saledate=convert(varchar(20),dateadd(hour,-3,A.saledate),120)+'-'+convert(varchar(20),saledate,120),
quantity=(select sum(quantity) from #sales where productid=A.productid and saledate>dateadd(hour,-3,A.saledate) and saledate<=A.saledate )
from #sales A
where productid='002'--结果productid saledate quantity
-------------------- ----------------------------------------- -----------
001 2005-09-01 05:00:00-2005-09-01 08:00:00 10
001 2005-09-01 06:00:00-2005-09-01 09:00:00 22
001 2005-09-01 10:00:00-2005-09-01 13:00:00 15
001 2005-09-01 11:00:00-2005-09-01 14:00:00 35
002 2005-09-01 04:00:00-2005-09-01 07:00:00 10
002 2005-09-01 07:00:00-2005-09-01 10:00:00 20
002 2005-09-01 12:00:00-2005-09-01 15:00:00 30--删除测试环境
Drop table #sales--1小时见另一个贴,6小时的楼主类推http://community.csdn.net/Expert/topic/4352/4352203.xml?temp=.3475611--这个可以封装成存储过程,然后传递参数类推!
Create Proc P_GetVale(@flag int)
as
begin
select productid,
saledate=convert(varchar(20),dateadd(hour,-@flag,A.saledate),120)+'-'+convert(varchar(20),saledate,120),
quantity=(select sum(quantity) from #sales where productid=A.productid and saledate>dateadd(hour,-@flag,A.saledate) and saledate<=A.saledate )
from #sales A
end--1小时数据
exec P_GetVale 1
--结果
productid saledate quantity
-------------------- ----------------------------------------- -----------
001 2005-09-01 07:00:00-2005-09-01 08:00:00 10
001 2005-09-01 08:00:00-2005-09-01 09:00:00 12
001 2005-09-01 12:00:00-2005-09-01 13:00:00 15
001 2005-09-01 13:00:00-2005-09-01 14:00:00 20
002 2005-09-01 06:00:00-2005-09-01 07:00:00 10
002 2005-09-01 09:00:00-2005-09-01 10:00:00 20
002 2005-09-01 14:00:00-2005-09-01 15:00:00 30
--3小时数据
exec P_GetVale 3
--结果
productid saledate quantity
-------------------- ----------------------------------------- -----------
001 2005-09-01 05:00:00-2005-09-01 08:00:00 10
001 2005-09-01 06:00:00-2005-09-01 09:00:00 22
001 2005-09-01 10:00:00-2005-09-01 13:00:00 15
001 2005-09-01 11:00:00-2005-09-01 14:00:00 35
002 2005-09-01 04:00:00-2005-09-01 07:00:00 10
002 2005-09-01 07:00:00-2005-09-01 10:00:00 20
002 2005-09-01 12:00:00-2005-09-01 15:00:00 30
--6小时数据
exec P_GetVale 6
--结果
productid saledate quantity
-------------------- ----------------------------------------- -----------
001 2005-09-01 02:00:00-2005-09-01 08:00:00 10
001 2005-09-01 03:00:00-2005-09-01 09:00:00 22
001 2005-09-01 07:00:00-2005-09-01 13:00:00 37
001 2005-09-01 08:00:00-2005-09-01 14:00:00 47
002 2005-09-01 01:00:00-2005-09-01 07:00:00 10
002 2005-09-01 04:00:00-2005-09-01 10:00:00 30
002 2005-09-01 09:00:00-2005-09-01 15:00:00 50--删除环境
Drop table #sales
Drop proc P_GETValue
--------------------------------------------------
declare @hh datetime --开始时间
declare @ss int --间隔时间
declare @ee datetime --结束时间
declare @tt datetime --临时变量
declare @or int
declare @tb table(Oid int,productid nvarchar(4),timen nvarchar(100),amount nvarchar(50))
set @hh = '2005-9-1 7:00:00'
set @ss = 3
set @ee = '2005-9-2 18:00:00'
set @tt = @hh
set @or = 1
while @tt <= @ee
begin
set @tt = dateadd(hh,@ss,@tt)
insert @tb
select cast(@or as nvarchar(4)),cast(productid as nvarchar(30)),
cast(@hh as nvarchar(50)) + ' - ' +cast(@tt as nvarchar(50)),
cast(sum(quantity) as nvarchar(50))
from #sales
where saledate > @hh
and saledate <= @tt
--and datepart(day,saledate) = datepart(day,@tt)
group by productid
set @hh = dateadd(hh,@ss,@hh)
set @or = @or +1
end
select Productid,timen,amount from @tb order by productid,oid结果为
/-------------------------------
1 09 1 2005 7:00AM - 09 1 2005 10:00AM 22
1 09 1 2005 10:00AM - 09 1 2005 1:00PM 15
1 09 1 2005 1:00PM - 09 1 2005 4:00PM 20
1 09 2 2005 1:00PM - 09 2 2005 4:00PM 20
2 09 1 2005 7:00AM - 09 1 2005 10:00AM 20
2 09 1 2005 1:00PM - 09 1 2005 4:00PM 30
2 09 2 2005 1:00PM - 09 2 2005 4:00PM 30
------------------------------/
@bdate datetime, --开始时间
@edate datetime, --结束时间
@timejg int --时间间隔,以小时为单位
as begin
--建立时间序列
declare @a table( id int,Edate datetime,Bdate datetime)
declare @bdate1 datetime
declare @edate1 datetime
declare @i --循环记数
select
@bdate1=@bdate,
@edate1=(case when dateadd(hour,@timejg,@bdate)>=@edate then
@edate
else
dateadd(hour,@timejg,@bdate) end),
@i=1
while 1=1 begin
insert into @a(id,bdate,edate)
values(@i,@bdate1,@edate1)
if @edate1 >=@edate break
select
@bdate1=@bdate,
@edate1=(case when dateadd(hour,@timejg,@bdate)>=@edate then
@edate
else
dateadd(hour,@timejg,@bdate) end),
@i=@i+1
end
--取查询数据
select
productid=a.productid,
saledate =convert(varchar(20),b.bdate,20)+'-'+convert(varchar(20),b.edate,20)
quantity =sum(a.quantity)
from
@a b
left join sales a on a.saledate>b.bdate
and a.saledate<=b.edate
group by
a.productid,
b.bdate,
b.edate
end