create table test3
(日期 datetime,时间 int,数量1 int,数量2 int
)insert into test3 select '2012-3-1',6 ,10,20
insert into test3 select '2012-3-1',7 ,10,20
insert into test3 select '2012-3-1',8 ,10,20
insert into test3 select '2012-3-1',9 ,10,20
insert into test3 select '2012-3-1',10 ,10,20
insert into test3 select '2012-3-1',11,10,20
insert into test3 select '2012-3-1',12 ,10,20
insert into test3 select '2012-3-1',13 ,10,20
insert into test3 select '2012-3-1',14,10,20
insert into test3 select '2012-3-1',15,10,20
insert into test3 select '2012-3-1',16,10,20
insert into test3 select '2012-3-1',17,10,20
insert into test3 select '2012-3-1',18,10,20
insert into test3 select '2012-3-1',19,10,20
insert into test3 select '2012-3-1',20,10,20
insert into test3 select '2012-3-1',21,10,20
insert into test3 select '2012-3-1',22,10,20
insert into test3 select '2012-3-1',23,10,20
insert into test3 select '2012-3-1',0,10,20
insert into test3 select '2012-3-1',1 ,10,20
insert into test3 select '2012-3-1',2 ,10,20
insert into test3 select '2012-3-1',3 ,10,20
insert into test3 select '2012-3-1',4 ,10,20
insert into test3 select '2012-3-1',5 ,10,20drop table test3要得到以下的数据:如果要查询 6点的数据,就是时间在6点钟的记录,即 数量1+数量2=30如果要查询 7点的数据 ,就是把6点钟和7点的记录都加起来 ,也就是 60如果要查询 8点的数据 ,就是把6点钟,7点的,8点钟记录都加起来 ,也就是 90以此类推 如果要查询5点钟的记录,就把5点钟以前(包括5点)的记录都加起来次序不能变,就是从6点一直到第二天5点,记录里面的日期都是2012-3-1,,这个日期无关紧要,
就是要统计从6点到5点的,截止到每个时间段的所有记录总和。
set @num = 6select sum(数量1+数量2) from tb where 时间<=@num
select *,rn=row_number() over(order by getdate()) from test3
)
select 日期,时间,
数量=(select sum(数量1+数量2) from cte where rn<=t.rn)
from cte t
/**
日期 时间 数量
----------------------- ----------- -----------
2012-03-01 00:00:00.000 6 30
2012-03-01 00:00:00.000 7 60
2012-03-01 00:00:00.000 8 90
2012-03-01 00:00:00.000 9 120
2012-03-01 00:00:00.000 10 150
2012-03-01 00:00:00.000 11 180
2012-03-01 00:00:00.000 12 210
2012-03-01 00:00:00.000 13 240
2012-03-01 00:00:00.000 14 270
2012-03-01 00:00:00.000 15 300
2012-03-01 00:00:00.000 16 330
2012-03-01 00:00:00.000 17 360
2012-03-01 00:00:00.000 18 390
2012-03-01 00:00:00.000 19 420
2012-03-01 00:00:00.000 20 450
2012-03-01 00:00:00.000 21 480
2012-03-01 00:00:00.000 22 510
2012-03-01 00:00:00.000 23 540
2012-03-01 00:00:00.000 0 570
2012-03-01 00:00:00.000 1 600
2012-03-01 00:00:00.000 2 630
2012-03-01 00:00:00.000 3 660
2012-03-01 00:00:00.000 4 690
2012-03-01 00:00:00.000 5 720(24 行受影响)
**/
(日期 datetime,时间 int,数量1 int,数量2 int
)insert into test3 select '2012-3-1',6 ,10,20
insert into test3 select '2012-3-1',7 ,10,20
insert into test3 select '2012-3-1',8 ,10,20
insert into test3 select '2012-3-1',9 ,10,20
insert into test3 select '2012-3-1',10 ,10,20
insert into test3 select '2012-3-1',11,10,20
insert into test3 select '2012-3-1',12 ,10,20
insert into test3 select '2012-3-1',13 ,10,20
insert into test3 select '2012-3-1',14,10,20
insert into test3 select '2012-3-1',15,10,20
insert into test3 select '2012-3-1',16,10,20
insert into test3 select '2012-3-1',17,10,20
insert into test3 select '2012-3-1',18,10,20
insert into test3 select '2012-3-1',19,10,20
insert into test3 select '2012-3-1',20,10,20
insert into test3 select '2012-3-1',21,10,20
insert into test3 select '2012-3-1',22,10,20
insert into test3 select '2012-3-1',23,10,20
insert into test3 select '2012-3-1',0,10,20
insert into test3 select '2012-3-1',1 ,10,20
insert into test3 select '2012-3-1',2 ,10,20
insert into test3 select '2012-3-1',3 ,10,20
insert into test3 select '2012-3-1',4 ,10,20
insert into test3 select '2012-3-1',5 ,10,20/*
要得到以下的数据:
如果要查询 6点的数据,就是时间在6点钟的记录,即 数量1+数量2=30
如果要查询 7点的数据 ,就是把6点钟和7点的记录都加起来 ,也就是 60
如果要查询 8点的数据 ,就是把6点钟,7点的,8点钟记录都加起来 ,也就是 90
以此类推
如果要查询5点钟的记录,就把5点钟以前(包括5点)的记录都加起来
次序不能变,就是从6点一直到第二天5点,记录里面的日期都是2012-3-1,,这个日期无关紧要,
就是要统计从6点到5点的,截止到每个时间段的所有记录总和。
*/go
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp @time int
as
if @time<=5
select (SUM(数量1)+SUM(数量2)) as total
from test3 where 时间<=5
else if @time=6
select (数量1+数量2) as total from test3 where 时间=6
else
select (SUM(数量1)+SUM(数量2)) as total
from test3 where 时间 between (@time-1) and @timeexec p_sp 8
create table test3
(日期 datetime,时间 int,数量1 int,数量2 int
)insert into test3 select '2012-3-1',6 ,10,20
insert into test3 select '2012-3-1',7 ,10,20
insert into test3 select '2012-3-1',8 ,10,20
insert into test3 select '2012-3-1',9 ,10,20
insert into test3 select '2012-3-1',10 ,10,20
insert into test3 select '2012-3-1',11,10,20
insert into test3 select '2012-3-1',12 ,10,20
insert into test3 select '2012-3-1',13 ,10,20
insert into test3 select '2012-3-1',14,10,20
insert into test3 select '2012-3-1',15,10,20
insert into test3 select '2012-3-1',16,10,20
insert into test3 select '2012-3-1',17,10,20
insert into test3 select '2012-3-1',18,10,20
insert into test3 select '2012-3-1',19,10,20
insert into test3 select '2012-3-1',20,10,20
insert into test3 select '2012-3-1',21,10,20
insert into test3 select '2012-3-1',22,10,20
insert into test3 select '2012-3-1',23,10,20
insert into test3 select '2012-3-1',0,10,20
insert into test3 select '2012-3-1',1 ,10,20
insert into test3 select '2012-3-1',2 ,10,20
insert into test3 select '2012-3-1',3 ,10,20
insert into test3 select '2012-3-1',4 ,10,20
insert into test3 select '2012-3-1',5 ,10,20/*
要得到以下的数据:
如果要查询 6点的数据,就是时间在6点钟的记录,即 数量1+数量2=30
如果要查询 7点的数据 ,就是把6点钟和7点的记录都加起来 ,也就是 60
如果要查询 8点的数据 ,就是把6点钟,7点的,8点钟记录都加起来 ,也就是 90
以此类推
如果要查询5点钟的记录,就把5点钟以前(包括5点)的记录都加起来
次序不能变,就是从6点一直到第二天5点,记录里面的日期都是2012-3-1,,这个日期无关紧要,
就是要统计从6点到5点的,截止到每个时间段的所有记录总和。
*/go
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp @time int
as
if @time<=5
select (SUM(数量1)+SUM(数量2)) as total
from test3 where 时间<=5
else if @time=6
select (数量1+数量2) as total from test3 where 时间=6
else
select (SUM(数量1)+SUM(数量2)) as total
from test3 where 时间 between (@time-1) and @timeexec p_sp 8--已测试好了
go
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp @time int
as
if @time<=5
select (SUM(数量1)+SUM(数量2)) as total
from test3 where 时间<=5
else if @time=6
select (数量1+数量2) as total from test3 where 时间=6
else
select (SUM(数量1)+SUM(数量2)) as total
from test3 where 时间 between 6 and @timeexec p_sp 8修改了一个地方,没注意是从六点开始统计
create table test3
(
日期 datetime,
时间 int,
数量1 int,
数量2 int
)insert into test3 select '2012-3-1',6 ,10,20
insert into test3 select '2012-3-1',7 ,10,20
insert into test3 select '2012-3-1',8 ,10,20
insert into test3 select '2012-3-1',9 ,10,20
insert into test3 select '2012-3-1',10 ,10,20
insert into test3 select '2012-3-1',11,10,20
insert into test3 select '2012-3-1',12 ,10,20
insert into test3 select '2012-3-1',13 ,10,20
insert into test3 select '2012-3-1',14,10,20
insert into test3 select '2012-3-1',15,10,20
insert into test3 select '2012-3-1',16,10,20
insert into test3 select '2012-3-1',17,10,20
insert into test3 select '2012-3-1',18,10,20
insert into test3 select '2012-3-1',19,10,20
insert into test3 select '2012-3-1',20,10,20
insert into test3 select '2012-3-1',21,10,20
insert into test3 select '2012-3-1',22,10,20
insert into test3 select '2012-3-1',23,10,20
insert into test3 select '2012-3-1',0,10,20
insert into test3 select '2012-3-1',1 ,10,20
insert into test3 select '2012-3-1',2 ,10,20
insert into test3 select '2012-3-1',3 ,10,20
insert into test3 select '2012-3-1',4 ,10,20
insert into test3 select '2012-3-1',5 ,10,20
declare @x int
select @x=6
select @x '查询时间',sum(数量1)+sum(数量2) '数量'
from test3
where 时间>=6 and 时间<=@x查询时间 数量
----------- -----------
6 30
declare @x int
select @x=7
select @x '查询时间',sum(数量1)+sum(数量2) '数量'
from test3
where 时间>=6 and 时间<=@x查询时间 数量
----------- -----------
7 60
declare @x int
select @x=8
select @x '查询时间',sum(数量1)+sum(数量2) '数量'
from test3
where 时间>=6 and 时间<=@x查询时间 数量
----------- -----------
8 90
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp @time int
as
if @time<=5
select cast(@time as varchar)+'点之前' as 时间段,(SUM(数量1)+SUM(数量2)) as 合计
from test3 where 时间<=5
else if @time=6
select cast(@time as varchar)+'点' as 时间段 ,(数量1+数量2) as 合计 from test3 where 时间=6
else
select ('6点到'+cast(@time as varchar)+'点') as 时间段,(SUM(数量1)+SUM(数量2)) as 合计
from test3 where 时间 between 6 and @time
--调用存储过程:
exec p_sp 8
/*
时间段 合计
6点到8点 90
*/
exec p_sp 5
/*
时间段 合计
5点之前 180
*/
exec p_sp 6
/*
时间段 合计
6点 30
*/
declare @num int
set @num = 6select sum(数量1+数量2) from test3 where 时间<=@num
这个不对,是210,实际是30
日期不用考虑,只考虑时间,时间从6点---5点,次序不能颠倒,注意5点的数据,要把5点之前都加起来
------------------------------------------------------------
2.josy
我用的环境是sql 2000,测不了,抱歉了---------------------------------------------------------------------------------
3.TravyLee
谢谢您写了这么多,到0点以后的数据就不对了,0点的数据应该是从6点到0点所有数据之和,5点钟的数据应该是6点到5点所有数据之和,也就是10*24+20*24=720.
-----------------------------------------------------------------------------------
4.ap0405140
declare @x int
select @x=5
select @x '查询时间',sum(数量1)+sum(数量2) '数量'
from test3
where 时间>=6 and 时间<=@x
如果@x=5,就没有值了
-----------------------------------------------------------------------------------就是说,如果是0点,就要把从6点到0点的数据都加起来,如果是1点,就要把从6点到1点的数据都加起来,如果是5点,就要把从6点到5点的数据都加起来
go
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp @time int
as
create table #T(
序号 int identity(1,1),
日期 datetime,
时间 int,
数量1 int,
数量2 int
)
insert #T
select *from test3
if @time=6
select cast(@time as varchar)+'点' as 时间段 ,(数量1+数量2) as 合计 from #T where 时间=6
else if @time>6
select ('6点到'+cast(@time as varchar)+'点') as 时间段,(SUM(数量1)+SUM(数量2)) as 合计
from #T where 时间 between 6 and @time
else
select ('6点到第二天'+cast(@time as varchar)+'点') as 时间段,
(SUM(数量1)+SUM(数量2)) as 合计 from #T
where 序号 between(select 序号 from #T where 时间=6)
and (select 序号 from #T where 时间=@time)
--调用存储过程:
exec p_sp 5
/*
时间段 合计
6点到第二天5点 720
*/搞定,分给我,哈哈
6 30
7 60
8 90
9 120
10 150
11 180
12 210
13 240
14 270
15 300
16 330
17 360
18 390
19 420
20 450
21 480
22 510
23 540
0 570
1 600
2 630
3 660
4 690
5 720