--如何实现求连续3小时最大累计值的记录：
GO
CREATE TABLE [dbo].[TB_RUNDATA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[STCD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TM] [datetime] NOT NULL,
[Z] [decimal](9, 3) NOT NULL,
CONSTRAINT [PK_TB_RUNDATA] PRIMARY KEY CLUSTERED
(
[STCD] ASC,
[TM] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[TB_RUNDATA] on
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(7, 'ST101', '2009-10-24 00:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(8, 'ST101', '2009-10-24 01:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(9, 'ST101', '2009-10-24 02:00:00', 2.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(10, 'ST101', '2009-10-24 03:00:00', 3.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(11, 'ST101', '2009-10-24 04:00:00', 5.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(12, 'ST101', '2009-10-24 05:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(13, 'ST101', '2009-10-24 06:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(14, 'ST101', '2009-10-24 07:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(15, 'ST101', '2009-10-24 08:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(16, 'ST101', '2009-10-24 09:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(17, 'ST101', '2009-10-24 10:00:00', 34.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(18, 'ST101', '2009-10-24 11:00:00', 5.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(19, 'ST101', '2009-10-24 12:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(1, 'ST101', '2009-10-24 13:00:00', 11.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(5, 'ST101', '2009-10-24 14:00:00', 31.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(3, 'ST101', '2009-10-24 15:00:00', 30.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(20, 'ST101', '2009-10-24 16:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(21, 'ST101', '2009-10-24 17:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(22, 'ST101', '2009-10-24 18:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(23, 'ST101', '2009-10-24 19:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(24, 'ST101', '2009-10-24 20:00:00', 12.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(25, 'ST101', '2009-10-24 21:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(26, 'ST101', '2009-10-24 22:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(27, 'ST101', '2009-10-24 23:00:00', 0.000);
select row_number()over(order by TM asc) as num,* from [dbo].[TB_RUNDATA]
select *from [dbo].[TB_RUNDATA]
drop table TB_RUNDATA
----求连续3小时最大累计值的记录
declare @mintime int
select @mintime=min(datepart(hh,TM)) from [TB_RUNDATA]
print @mintime
--方法一：
;with T
as
(
select *,num=row_number() over (partition by [STCD] order by [TM])
from [TB_RUNDATA]
),
M as
(
select a.*
from T a join T b on a.STCD = b.STCD and b.num = a.num + 1 and datediff(hh,a.TM,b.TM) = 1
union
select a.*
from T a join T b on a.STCD = b.STCD and a.num = b.num + 1 and datediff(hh,b.TM,a.TM) = 1
),
N as
(
select a.STCD,a.num,a.Z+b.Z+c.Z Z
from M a join M b on a.STCD = b.STCD and a.num + 1 = b.num
join M c on a.STCD = c.STCD and a.num + 2 = c.num
)
select a.*
from M a join N b on a.STCD = b.STCD and a.num between b.num and b.num + 2
where not exists (select 1 from N where STCD = b.STCD and Z > b.Z)
--方法二：
SELECT * FROM TB_RUNDATA a
INNER JOIN (
SELECT * from
(
SELECT *,(SELECT SUM(z) FROM tb_rundata b WHERE b.Tm BETWEEN a.tm
AND dateadd(hh,2,a.tm)) sumb
from TB_RUNDATA a
)b
WHERE NOT EXISTS (
SELECT *
from TB_RUNDATA a WHERE b.sumb < (SELECT SUM(z) FROM tb_rundata b
WHERE b.Tm BETWEEN a.tm AND dateadd(hh,2,a.tm))
)) b ON a.Tm BETWEEN b.tm AND dateadd(hh,2,b.tm)
/*
ID STCD TM Z num
5 ST101 2009-10-24 14:00:00.000 31.000 15
3 ST101 2009-10-24 15:00:00.000 30.000 16
1 ST101 2009-10-24 13:00:00.000 11.000 14
*/
自己写的一种方法
if OBJECT_ID('test')is not null
drop table test
go
create table test(
dates date,
value int
)
go
insert test
select '2012-01-01',2 union all
select '2012-01-02',3 union all
select '2012-01-03',2 union all
select '2012-01-04',7 union all
select '2012-01-05',6 union all
select '2012-01-07',3 union all
select '2012-01-09',5 union all
select '2012-01-10',6 union all
select '2012-01-11',8 union all
select '2012-01-13',4 union all
select '2012-01-14',9 union all
select '2012-01-15',4 union all
select '2012-01-16',1 union all
select '2012-01-18',2
go
;with t
as(
select
dateadd(dd,-ROW_NUMBER()over(order by dates asc),dates) as px,
* from test
),m
as(
select id=ROW_NUMBER()over(partition by px order by dates asc),
px,dates,value from t where px in(
select px from(
select px,
min(dates) as startdate,MAX(dates) as enddate
from t a group by px)a where datediff(dd,a.startdate,a.enddate)>=2)
),
g as(
select px,dates,
(select SUM(value) from m s
where s.id between a.id-2 and a.id and a.px=s.px) as totals from m a
where exists(select 1 from m b where a.px=b.px and b.dates=DATEADD(DD,-2,a.dates))
)
select top 3 dates,totals from g
order by totals desc
/*
dates totals
---------------------------
2012-01-11 19
2012-01-15 17
2012-01-05 15
*/
