需求:
根据下面查询语句,能得到每10分钟一个rawstatus,
能否直接添加语句,实现自动计算一个小时内,rawstatus 的最大值?select
assignmentname,
datetime,
rawstatus
from Nodes a left join Custompollerassignment b
on a.nodeid=b.nodeid
left join CustomPollerStatistics_Detail c on b.CustomPollerAssignmentID = c.CustomPollerAssignmentID
where assignmentname like '%ActiveConnectionDOM on DOM - Cuca%'
and convert(varchar(10),datetime,120) between '2009-11-16' and '2009-12-15'
and Rawstatus between 1 and 120
assignmentname datetime rawstatus
------------------------------------------------------------------------
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:06:58.750 32
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:16:58.750 30
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:26:58.750 32
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:36:58.750 25
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:46:58.750 29
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:56:58.750 25
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:06:58.750 28
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:16:58.750 27
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:26:58.750 24
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:36:58.767 26
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:46:58.767 21
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:56:58.767 22
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:06:58.767 25
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:16:58.780 23
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:26:58.780 24
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:36:58.780 20
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:46:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:56:58.780 21
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:06:58.780 22
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:16:58.780 20
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:26:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:36:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:46:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:56:58.780 17
ActiveConnectionDOM on DOM - Cuca 2009-11-16 04:06:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 04:16:58.780 24
ActiveConnectionDOM on DOM - Cuca 2009-11-16 04:26:58.780 19........期望得到如下查询结果:datetime 可以根据需要,变动。assignmentname datetime Maxofrawstatus
------------------------------------------------------------------------
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:00 32
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:00 28
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:00 25
根据下面查询语句,能得到每10分钟一个rawstatus,
能否直接添加语句,实现自动计算一个小时内,rawstatus 的最大值?select
assignmentname,
datetime,
rawstatus
from Nodes a left join Custompollerassignment b
on a.nodeid=b.nodeid
left join CustomPollerStatistics_Detail c on b.CustomPollerAssignmentID = c.CustomPollerAssignmentID
where assignmentname like '%ActiveConnectionDOM on DOM - Cuca%'
and convert(varchar(10),datetime,120) between '2009-11-16' and '2009-12-15'
and Rawstatus between 1 and 120
assignmentname datetime rawstatus
------------------------------------------------------------------------
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:06:58.750 32
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:16:58.750 30
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:26:58.750 32
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:36:58.750 25
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:46:58.750 29
ActiveConnectionDOM on DOM - Cuca 2009-11-16 00:56:58.750 25
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:06:58.750 28
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:16:58.750 27
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:26:58.750 24
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:36:58.767 26
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:46:58.767 21
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:56:58.767 22
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:06:58.767 25
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:16:58.780 23
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:26:58.780 24
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:36:58.780 20
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:46:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:56:58.780 21
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:06:58.780 22
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:16:58.780 20
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:26:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:36:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:46:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:56:58.780 17
ActiveConnectionDOM on DOM - Cuca 2009-11-16 04:06:58.780 18
ActiveConnectionDOM on DOM - Cuca 2009-11-16 04:16:58.780 24
ActiveConnectionDOM on DOM - Cuca 2009-11-16 04:26:58.780 19........期望得到如下查询结果:datetime 可以根据需要,变动。assignmentname datetime Maxofrawstatus
------------------------------------------------------------------------
ActiveConnectionDOM on DOM - Cuca 2009-11-16 01:00 32
ActiveConnectionDOM on DOM - Cuca 2009-11-16 02:00 28
ActiveConnectionDOM on DOM - Cuca 2009-11-16 03:00 25
select
assignmentname,
datetime,
max(rawstatus)
from Nodes a left join Custompollerassignment b
on a.nodeid=b.nodeid
left join CustomPollerStatistics_Detail c on b.CustomPollerAssignmentID = c.CustomPollerAssignmentID
where assignmentname like '%ActiveConnectionDOM on DOM - Cuca%'
and convert(varchar(10),datetime,120) between '2009-11-16' and '2009-12-15'
and Rawstatus between 1 and 120
group by assignmentname,convert(char(14),datetime,120)
datetime 显示不出来。
assignmentname,
convert(char(14),datetime,120) [datetime],
max(rawstatus)
from ...
group by assignmentname,convert(char(14),datetime,120)
--把你的原始数据插入到了一张表,当然也可以整合到你的前一条语句中,稍微复杂点而已。
set nocount on
if object_id('dbo.#temp') is not null
drop table dbo.#temp;
go
create table #temp
(
assignmentname varchar(30),
dt datetime,
rawstatus int
);insert into #temp
select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 00:06:58.750', 32
union all select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 00:16:58.750', 30
union all select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 00:26:58.750', 32
union all select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 01:06:58.750', 28
union all select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 01:16:58.750', 27
union all select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 01:26:58.750', 24
union all select 'ActiveConnectionDOM on DOM - Cuca', '2009-11-16 03:06:58.780', 22
union all select 'ActiveConnectionDOM on DOM - Cuca' ,'2009-11-16 03:16:58.780', 20
go
with cte as
(
select assignmentname,convert(char(10),dt,120)as dt,cast(datepart(hh,dt)as int) as hh,max(rawstatus) as max_rawstatus from #temp
group by assignmentname,convert(char(10),dt,120),datepart(hh,dt)
)
select assignmentname,dt+
case hh when 0 then ' 01'
when 1 then ' 02'
when 2 then ' 03'
when 3 then ' 04' --请补齐其余的数据
else '' end + ':00',
max_rawstatus
from cte
drop table #temp/*
assignmentname max_rawstatus
------------------------------ ---------------- -------------
ActiveConnectionDOM on DOM - C 2009-11-16 01:00 32
ActiveConnectionDOM on DOM - C 2009-11-16 02:00 28
ActiveConnectionDOM on DOM - C 2009-11-16 04:00 22*/