ID Date_Time ValueQuality P Q U
2 2010-04-01 00:00:00.000 101 10 10 10
3 2010-04-01 00:15:00.000 101 11 11 11
4 2010-04-01 00:30:00.000 101 12 12 12
5 2010-04-01 00:45:00.000 101 13 13 13
6 2010-04-01 01:00:00.000 112 14 14 14
7 2010-04-01 01:15:00.000 123 15 15 15
8 2010-04-01 01:30:00.000 1213 16 16 16
9 2010-04-01 01:45:00.000 154 9 10 12
10 2010-04-02 00:00:00.000 101 10 10 10
11 2010-04-02 00:15:00.000 101 11 11 11
12 2010-04-02 00:30:00.000 101 12 12 12
13 2010-04-02 00:45:00.000 101 13 13 13
14 2010-04-02 01:00:00.000 112 14 14 14
15 2010-04-02 01:15:00.000 123 15 15 15
表结构如上。隔15分钟有一行数据。
求出某段时间内(按天算)对应天的P 最大值和其对应时间,最小值和其对应时间。
2 2010-04-01 00:00:00.000 101 10 10 10
3 2010-04-01 00:15:00.000 101 11 11 11
4 2010-04-01 00:30:00.000 101 12 12 12
5 2010-04-01 00:45:00.000 101 13 13 13
6 2010-04-01 01:00:00.000 112 14 14 14
7 2010-04-01 01:15:00.000 123 15 15 15
8 2010-04-01 01:30:00.000 1213 16 16 16
9 2010-04-01 01:45:00.000 154 9 10 12
10 2010-04-02 00:00:00.000 101 10 10 10
11 2010-04-02 00:15:00.000 101 11 11 11
12 2010-04-02 00:30:00.000 101 12 12 12
13 2010-04-02 00:45:00.000 101 13 13 13
14 2010-04-02 01:00:00.000 112 14 14 14
15 2010-04-02 01:15:00.000 123 15 15 15
表结构如上。隔15分钟有一行数据。
求出某段时间内(按天算)对应天的P 最大值和其对应时间,最小值和其对应时间。
CONVERT(CHAR(10),DATE_TIME,120) TIME,MAX(P) ,MIN(P)
FROM TB
GROUP BY CONVERT(CHAR(10),DATE_TIME,120)
select convert(varchar(10),date,120),max(p)
from tb group by convert(varchar(10),date,120)--2
select convert(varchar(10),date,120),min(p)
from tb group by convert(varchar(10),date,120)
where P=(select max(P) from tb where convert(varchar,Date_Time,23)=(varchar,t.Date_Time,23) and Date_Time between @开始时间 and @结束时间)
and Date_Time between @开始时间 and @结束时间
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-22 14:45:55
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Date_Time] datetime,[ValueQuality] int,[P] int,[Q] int,[U] int)
insert [tb]
select 2,'2010-04-01 00:00:00.000',101,10,10,10 union all
select 3,'2010-04-01 00:15:00.000',101,11,11,11 union all
select 4,'2010-04-01 00:30:00.000',101,12,12,12 union all
select 5,'2010-04-01 00:45:00.000',101,13,13,13 union all
select 6,'2010-04-01 01:00:00.000',112,14,14,14 union all
select 7,'2010-04-01 01:15:00.000',123,15,15,15 union all
select 8,'2010-04-01 01:30:00.000',1213,16,16,16 union all
select 9,'2010-04-01 01:45:00.000',154,9,10,12 union all
select 10,'2010-04-02 00:00:00.000',101,10,10,10 union all
select 11,'2010-04-02 00:15:00.000',101,11,11,11 union all
select 12,'2010-04-02 00:30:00.000',101,12,12,12 union all
select 13,'2010-04-02 00:45:00.000',101,13,13,13 union all
select 14,'2010-04-02 01:00:00.000',112,14,14,14 union all
select 15,'2010-04-02 01:15:00.000',123,15,15,15
--------------开始查询--------------------------
select
convert(varchar(10),Date_Time,120) as 时间,
max(p) as 最大值,min(p) as 最小值
from
tb
group by
convert(varchar(10),Date_Time,120)
----------------结果----------------------------
/* 时间 最大值 最小值
---------- ----------- -----------
2010-04-01 16 9
2010-04-02 15 10(2 行受影响)*/
where P=(select max(P) from tb where convert(varchar,Date_Time,23)=(varchar,t.Date_Time,23) )
or p=
(select min(P) from tb where convert(varchar,Date_Time,23)=(varchar,t.Date_Time,23) )
select '最大',* from tb t
where P in (
SELECT
MAX(b.P)
FROM TB b
where CONVERT(CHAR(10),b.DATE_TIME,120)=CONVERT(CHAR(10),t.DATE_TIME,120)
GROUP BY CONVERT(CHAR(10),b.DATE_TIME,120)
)
union
select '最小',* from tb t
where P in (
SELECT
MIN(b.P)
FROM TB b
where CONVERT(CHAR(10),b.DATE_TIME,120)=CONVERT(CHAR(10),t.DATE_TIME,120)
GROUP BY CONVERT(CHAR(10),b.DATE_TIME,120)
)
ID Date_Time ValueQuality P Q U
---- ----------- ------------------------------------------------------ ------------ ----------- ----------- -----------
最大 8 2010-04-01 01:30:00.000 1213 16 16 16
最大 15 2010-04-02 01:15:00.000 123 15 15 15
最小 9 2010-04-01 01:45:00.000 154 9 10 12
最小 10 2010-04-02 00:00:00.000 101 10 10 10(所影响的行数为 4 行)
where P=(select max(P) from tb where convert(varchar,Date_Time,23)=convert(varchar,t.Date_Time,23) )
or p=
(select min(P) from tb
where convert(varchar,Date_Time,23)=convert(varchar,t.Date_Time,23) )
order by convert(varchar,Date_Time,23) asc,P desc
(
P int,
Date_Time datetime
)
insert into tb(P,Date_Time)
(
select 120,'2010-04-01 00:00:00.000' union
select 465,'2010-04-01 00:15:00.000 ' union
select 234,'2010-04-01 00:30:00.000' union
select 876,'2010-04-01 00:45:00.000' union
select 345,'2010-04-01 01:00:00.000 '
)
select max(P),min(P)from tb
group by CONVERT(varchar(8),Date_Time,112)
go
select t.Date_Time,t.P
from tb t
where P in
(
select max(P)
from tb
where convert(varchar,Date_Time,23)=convert(varchar,t.Date_Time,23)
union
select min(P)
from tb
where convert(varchar,Date_Time,23)=convert(varchar,t.Date_Time,23)
)
and t.Date_Time >= CAST('2010-04-01' as datetime)
and t.Date_Time < CAST('2010-04-30' as datetime)
order by convert(varchar,t.Date_Time,23) asc,P desc