原始数据:ID CurDate Increment Value
59577f528da04ac2b098efa2b881746c 2010-7-1 0:04:38 1 1200
59577f528da04ac2b098efa2b881746c 2010-7-1 0:39:38 5 1210
59577f528da04ac2b098efa2b881746c 2010-7-1 1:14:41 20 1300
59577f528da04ac2b098efa2b881746c 2010-7-1 1:32:38 53 1410
59577f528da04ac2b098efa2b881746c 2010-7-1 2:04:40 80 1430
59577f528da04ac2b098efa2b881746c 2010-7-1 2:29:38 91 1500
59577f528da04ac2b098efa2b881746c 2010-7-1 3:34:38 111 1620
59577f528da04ac2b098efa2b881746c 2010-7-1 3:39:38 130 1660
59577f528da04ac2b098efa2b881746c 2010-7-1 4:24:40 210 1710
59577f528da04ac2b098efa2b881746c 2010-7-1 4:49:38 240 1800
期望结果如下: ID date hour Increment Value
59577f528da04ac2b098efa2b881746c 2010-7-1 0 26 1200
59577f528da04ac2b098efa2b881746c 2010-7-1 1 223 1300
59577f528da04ac2b098efa2b881746c 2010-7-1 2 121 1430
59577f528da04ac2b098efa2b881746c 2010-7-1 3 200 1620
59577f528da04ac2b098efa2b881746c 2010-7-1 4 130 1710 求SQL写法
59577f528da04ac2b098efa2b881746c 2010-7-1 0:04:38 1 1200
59577f528da04ac2b098efa2b881746c 2010-7-1 0:39:38 5 1210
59577f528da04ac2b098efa2b881746c 2010-7-1 1:14:41 20 1300
59577f528da04ac2b098efa2b881746c 2010-7-1 1:32:38 53 1410
59577f528da04ac2b098efa2b881746c 2010-7-1 2:04:40 80 1430
59577f528da04ac2b098efa2b881746c 2010-7-1 2:29:38 91 1500
59577f528da04ac2b098efa2b881746c 2010-7-1 3:34:38 111 1620
59577f528da04ac2b098efa2b881746c 2010-7-1 3:39:38 130 1660
59577f528da04ac2b098efa2b881746c 2010-7-1 4:24:40 210 1710
59577f528da04ac2b098efa2b881746c 2010-7-1 4:49:38 240 1800
期望结果如下: ID date hour Increment Value
59577f528da04ac2b098efa2b881746c 2010-7-1 0 26 1200
59577f528da04ac2b098efa2b881746c 2010-7-1 1 223 1300
59577f528da04ac2b098efa2b881746c 2010-7-1 2 121 1430
59577f528da04ac2b098efa2b881746c 2010-7-1 3 200 1620
59577f528da04ac2b098efa2b881746c 2010-7-1 4 130 1710 求SQL写法
Increment是加起来的
麻烦说下Increment的值怎么来的
ID date hour Increment Value
59577f528da04ac2b098efa2b881746c 2010-7-1 0 26 1200
59577f528da04ac2b098efa2b881746c 2010-7-1 1 73 1300
59577f528da04ac2b098efa2b881746c 2010-7-1 2 171 1430
59577f528da04ac2b098efa2b881746c 2010-7-1 3 241 1620
59577f528da04ac2b098efa2b881746c 2010-7-1 4 450 1710
id,convert(varchar(10),date,120) as dare,datepart(hh,date) as hour,sum(value) as value
from
tb
group by
id,convert(varchar(10),date,120) ,datepart(hh,date)
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (ID varchar(32),CurDate datetime,Increment int,Value int)
insert into [tb]
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:04:38',1,1200 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:39:38',5,1210 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:14:41',20,1300 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:32:38',53,1410 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:04:40',80,1430 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:29:38',91,1500 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:34:38',111,1620 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:39:38',130,1660 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:24:40',210,1710 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:49:38',240,1800--开始查询
select id,
[date]=CONVERT(varchar(10),CurDate,120),
[hour]=datepart(hour,CurDate),
Increment=SUM(Increment),
Value=MIN(Value)
from [tb]
group by id,CONVERT(varchar(10),CurDate,120),datepart(hour,CurDate)--结束查询
drop table [tb]/*
id date hour Increment Value
-------------------------------- ---------- ----------- ----------- -----------
59577f528da04ac2b098efa2b881746c 2010-07-01 0 6 1200
59577f528da04ac2b098efa2b881746c 2010-07-01 1 73 1300
59577f528da04ac2b098efa2b881746c 2010-07-01 2 171 1430
59577f528da04ac2b098efa2b881746c 2010-07-01 3 241 1620
59577f528da04ac2b098efa2b881746c 2010-07-01 4 450 1710(5 行受影响)
declare @T table (ID varchar(32),CurDate datetime,Increment int,Value int)
insert into @T
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:04:38',1,1200 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:39:38',5,1110 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:14:41',20,1300 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:32:38',53,1410 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:04:40',80,1430 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:29:38',91,1500 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:34:38',111,1620 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:39:38',130,1660 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:24:40',210,1710 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:49:38',240,1800select ID,
CurDate=max(convert(varchar(10),CurDate,120)),
hour=convert(varchar(2),CurDate,108)+0,
Increment=sum(Increment),Value=(select top 1 Value from @t where ID=a.ID and convert(varchar(2),CurDate,108)+0
=convert(varchar(2),a.CurDate,108)+0)
from @T a group by ID,convert(varchar(2),CurDate,108)+0
/*
ID CurDate hour Increment Value
-------------------------------- ---------- ----------- ----------- -----------
59577f528da04ac2b098efa2b881746c 2010-07-01 0 6 1200
59577f528da04ac2b098efa2b881746c 2010-07-01 1 73 1300
59577f528da04ac2b098efa2b881746c 2010-07-01 2 171 1430
59577f528da04ac2b098efa2b881746c 2010-07-01 3 241 1620
59577f528da04ac2b098efa2b881746c 2010-07-01 4 450 1710
*/
--我最后种数据加了个2000的,注意看,是不是这意思?--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (ID varchar(32),CurDate datetime,Increment int,Value int)
insert into [tb]
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:04:38',1,1200 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:39:38',5,1210 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:14:41',20,1300 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:32:38',53,1410 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:04:40',80,1430 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:29:38',91,1500 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:34:38',111,1620 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:39:38',130,1660 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:23:40',210,2000 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:24:40',210,1710 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:49:38',240,1800--开始查询
select id,
[date]=CONVERT(varchar(10),CurDate,120),
[hour]=datepart(hour,CurDate),
Increment=SUM(Increment),
Value=(select top 1 Value from [tb] where ID=a.ID
and CONVERT(varchar(10),CurDate,120)=CONVERT(varchar(10),a.CurDate,120)
and datepart(hour,CurDate)=datepart(hour,a.CurDate)
order by CurDate)
from [tb] a
group by id,CONVERT(varchar(10),CurDate,120),datepart(hour,CurDate)--结束查询
drop table [tb]/*
id date hour Increment Value
-------------------------------- ---------- ----------- ----------- -----------
59577f528da04ac2b098efa2b881746c 2010-07-01 0 6 1200
59577f528da04ac2b098efa2b881746c 2010-07-01 1 73 1300
59577f528da04ac2b098efa2b881746c 2010-07-01 2 171 1430
59577f528da04ac2b098efa2b881746c 2010-07-01 3 241 1620
59577f528da04ac2b098efa2b881746c 2010-07-01 4 660 2000(5 行受影响)
insert into @T
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:04:38',1,1200 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:39:38',5,1110 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:14:41',20,1300 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:32:38',53,1410 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:04:40',80,1430 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:29:38',91,1500 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:34:38',111,1620 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:39:38',130,1660 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:24:40',210,1710 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:49:38',240,1800select ID,
CurDate=max(convert(varchar(10),CurDate,120)),
hour=convert(varchar(2),CurDate,108)+0,
Increment=sum(Increment),Value=
(select top 1 Value from @t where ID=a.ID and convert(varchar(13),CurDate,120)+0
=convert(varchar(13),a.CurDate,120)+0 and )
from @T a group by ID,convert(varchar(2),CurDate,108)+0
/*
ID CurDate hour Increment Value
-------------------------------- ---------- ----------- ----------- -----------
59577f528da04ac2b098efa2b881746c 2010-07-01 0 6 1200
59577f528da04ac2b098efa2b881746c 2010-07-01 1 73 1300
59577f528da04ac2b098efa2b881746c 2010-07-01 2 171 1430
59577f528da04ac2b098efa2b881746c 2010-07-01 3 241 1620
59577f528da04ac2b098efa2b881746c 2010-07-01 4 450 1710
*/
insert into @T
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:04:38',1,1200 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 0:39:38',5,1110 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:14:41',20,1300 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 1:32:38',53,1410 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:04:40',80,1430 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 2:29:38',91,1500 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:34:38',111,1620 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 3:39:38',130,1660 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:24:40',210,1710 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-1 4:49:38',240,1800 union all
select '59577f528da04ac2b098efa2b881746c','2010-7-2 4:49:38',240,1800select ID,CurDate=convert(varchar(10),CurDate,120),
hour=convert(varchar(2),CurDate,108)+0,Increment=sum(Increment),
Value=(select top 1 Value from @t where ID=a.ID
and convert(varchar(2),CurDate,108)+0=convert(varchar(2),a.CurDate,108)+0)
from @T a
group by ID,convert(varchar(10),CurDate,120),convert(varchar(2),CurDate,108)+0
/*
ID CurDate hour Increment Value
-------------------------------- ---------- ----------- ----------- -----------
59577f528da04ac2b098efa2b881746c 2010-07-01 0 6 1200
59577f528da04ac2b098efa2b881746c 2010-07-01 1 73 1300
59577f528da04ac2b098efa2b881746c 2010-07-01 2 171 1430
59577f528da04ac2b098efa2b881746c 2010-07-01 3 241 1620
59577f528da04ac2b098efa2b881746c 2010-07-01 4 450 1710
59577f528da04ac2b098efa2b881746c 2010-07-02 4 240 1710
*/