日期:2014-05-18  浏览次数:20832 次

求助:求指定时间内的最大值最小值
问题描述:现有如下表,每五分钟一行数据,要求:
1.每个stid的Z在输入的时间范围内的最大值和最小值,并返回最大值、最小值对应的时间。
2.用输入的日期判断年度最大值,最小值,如输入2010-2-18,找出这年的Z的最大值和最小值。
3.用输入的日期找出当年每月的Z最大值和最小值。
以上都要返回对应的时间或日期。
SQL code

stid          tm                     z
1    2010-02-18 22:30:00.000    .030
1    2010-02-18 22:35:00.000    .050
1    2010-02-18 22:40:00.000    .080
1    2010-02-18 22:45:00.000    .100
1    2010-02-18 22:50:00.000    .130
1    2010-02-18 22:55:00.000    .150
1    2010-02-18 23:00:00.000    .180
2    2010-02-18 22:30:00.000    -.830
2    2010-02-18 22:35:00.000    -.810
2    2010-02-18 22:40:00.000    -.770
2    2010-02-18 22:45:00.000    -.740
2    2010-02-18 22:50:00.000    -.700
2    2010-02-18 22:55:00.000    -.670
2    2010-02-18 23:00:00.000    -.630
5    2010-02-18 22:30:00.000    -.740
5    2010-02-18 22:35:00.000    -.700
5    2010-02-18 22:40:00.000    -.660
5    2010-02-18 22:45:00.000    -.620
5    2010-02-18 22:50:00.000    -.590
5    2010-02-18 22:55:00.000    -.560
5    2010-02-18 23:00:00.000    -.540



用ASP或者SQL完成都行。谢谢各位回帖的朋友。。!


------解决方案--------------------
SQL code
--这下完整了,不知有没有误解,楼主到自己的环境中试试:

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([stid] [int],[tm] [datetime],[z] [numeric](3,3))
INSERT INTO [tb]
SELECT '1','2010-02-18 22:30:00.000','.030' UNION ALL
SELECT '1','2010-02-18 22:35:00.000','.050' UNION ALL
SELECT '1','2010-02-18 22:40:00.000','.080' UNION ALL
SELECT '1','2010-02-18 22:45:00.000','.100' UNION ALL
SELECT '1','2010-02-18 22:50:00.000','.130' UNION ALL
SELECT '1','2010-02-18 22:55:00.000','.150' UNION ALL
SELECT '1','2010-02-18 23:00:00.000','.180' UNION ALL
SELECT '2','2010-02-18 22:30:00.000','-.830' UNION ALL
SELECT '2','2010-02-18 22:35:00.000','-.810' UNION ALL
SELECT '2','2010-02-18 22:40:00.000','-.770' UNION ALL
SELECT '2','2010-02-18 22:45:00.000','-.740' UNION ALL
SELECT '2','2010-02-18 22:50:00.000','-.700' UNION ALL
SELECT '2','2010-02-18 22:55:00.000','-.670' UNION ALL
SELECT '2','2010-02-18 23:00:00.000','-.630' UNION ALL
SELECT '5','2010-02-18 22:30:00.000','-.740' UNION ALL
SELECT '5','2010-02-18 22:35:00.000','-.700' UNION ALL
SELECT '5','2010-02-18 22:40:00.000','-.660' UNION ALL
SELECT '5','2010-02-18 22:45:00.000','-.620' UNION ALL
SELECT '5','2010-02-18 22:50:00.000','-.590' UNION ALL
SELECT '5','2010-02-18 22:55:00.000','-.560' UNION ALL
SELECT '5','2010-02-18 23:00:00.000','-.540'


-->SQL查询如下:
--1.每个stid的Z在输入的时间范围内的最大值和最小值,并返回最大值、最小值对应的时间。
DECLARE @BT DATETIME,@ET DATETIME
SELECT @BT = '2010-02-18 22:40',@ET='2010-02-18 23:00'

;WITH T AS
(
    SELECT RN1=ROW_NUMBER()OVER(PARTITION BY stid ORDER BY Z),
        RN2=ROW_NUMBER()OVER(PARTITION BY stid ORDER BY Z DESC),* 
    FROM [tb] 
    WHERE tm BETWEEN @BT AND @ET 
)
SELECT stid,tm,z
FROM T 
WHERE RN1=1 OR RN2=1
/*
stid        tm                      z
----------- ----------------------- ---------------------------------------
1           2010-02-18 23:00:00.000 0.180
1           2010-02-18 22:40:00.000 0.080
2           2010-02-18 23:00:00.000 -0.630
2           2010-02-18 22:40:00.000 -0.770
5           2010-02-18 23:00:00.000 -0.540
5           2010-02-18 22:40:00.000 -0.660

(6 行受影响)
*/

--2.用输入的日期判断年度最大值,最小值,如输入2010-2-18,找出这年的Z的最大值和最小值。
DECLARE @D DATETIME
SET @D='2010-2-18'
;WITH T AS
(
    SELECT RN1=ROW_NUMBER()OVER(ORDER BY Z),
        RN2=ROW_NUMBER()OVER(ORDER BY Z DESC),* 
    FROM [tb] 
    WHERE DATEDIFF(YY,@D,tm)=0
)
SELECT stid,tm,z
FROM T 
WHERE RN1=1 OR RN2=1
/*
stid        tm                      z
----------- ----------------------- ---------------------------------------
1           2010-02-18 23:00:00.000 0.180
2           2010-02-18 22:30:00.000 -0.830

(2 行受影响)
*/
--3.用输入的日期找出当年每月的Z最大值和最小值。
DECLARE @Dt DATETIME
SE