求助：求指定时间内的最大值最小值
问题描述：现有如下表，每五分钟一行数据，要求：
1.每个stid的Z在输入的时间范围内的最大值和最小值，并返回最大值、最小值对应的时间。
2.用输入的日期判断年度最大值，最小值，如输入2010-2-18，找出这年的Z的最大值和最小值。
3.用输入的日期找出当年每月的Z最大值和最小值。
以上都要返回对应的时间或日期。
stid tm z1 2010-02-18 22:30:00.000 .0301 2010-02-18 22:35:00.000 .0501 2010-02-18 22:40:00.000 .0801 2010-02-18 22:45:00.000 .1001 2010-02-18 22:50:00.000 .1301 2010-02-18 22:55:00.000 .1501 2010-02-18 23:00:00.000 .1802 2010-02-18 22:30:00.000 -.8302 2010-02-18 22:35:00.000 -.8102 2010-02-18 22:40:00.000 -.7702 2010-02-18 22:45:00.000 -.7402 2010-02-18 22:50:00.000 -.7002 2010-02-18 22:55:00.000 -.6702 2010-02-18 23:00:00.000 -.6305 2010-02-18 22:30:00.000 -.7405 2010-02-18 22:35:00.000 -.7005 2010-02-18 22:40:00.000 -.6605 2010-02-18 22:45:00.000 -.6205 2010-02-18 22:50:00.000 -.5905 2010-02-18 22:55:00.000 -.5605 2010-02-18 23:00:00.000 -.540
--这下完整了，不知有没有误解，楼主到自己的环境中试试：--> 生成测试数据表: [tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb] ([stid] [int],[tm] [datetime],[z] [numeric](3,3))INSERT INTO [tb]SELECT '1','2010-02-18 22:30:00.000','.030' UNION ALLSELECT '1','2010-02-18 22:35:00.000','.050' UNION ALLSELECT '1','2010-02-18 22:40:00.000','.080' UNION ALLSELECT '1','2010-02-18 22:45:00.000','.100' UNION ALLSELECT '1','2010-02-18 22:50:00.000','.130' UNION ALLSELECT '1','2010-02-18 22:55:00.000','.150' UNION ALLSELECT '1','2010-02-18 23:00:00.000','.180' UNION ALLSELECT '2','2010-02-18 22:30:00.000','-.830' UNION ALLSELECT '2','2010-02-18 22:35:00.000','-.810' UNION ALLSELECT '2','2010-02-18 22:40:00.000','-.770' UNION ALLSELECT '2','2010-02-18 22:45:00.000','-.740' UNION ALLSELECT '2','2010-02-18 22:50:00.000','-.700' UNION ALLSELECT '2','2010-02-18 22:55:00.000','-.670' UNION ALLSELECT '2','2010-02-18 23:00:00.000','-.630' UNION ALLSELECT '5','2010-02-18 22:30:00.000','-.740' UNION ALLSELECT '5','2010-02-18 22:35:00.000','-.700' UNION ALLSELECT '5','2010-02-18 22:40:00.000','-.660' UNION ALLSELECT '5','2010-02-18 22:45:00.000','-.620' UNION ALLSELECT '5','2010-02-18 22:50:00.000','-.590' UNION ALLSELECT '5','2010-02-18 22:55:00.000','-.560' UNION ALLSELECT '5','2010-02-18 23:00:00.000','-.540'-->SQL查询如下:--1.每个stid的Z在输入的时间范围内的最大值和最小值，并返回最大值、最小值对应的时间。DECLARE @BT DATETIME,@ET DATETIMESELECT @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,zFROM T WHERE RN1=1 OR RN2=1/*stid tm z----------- ----------------------- ---------------------------------------1 2010-02-18 23:00:00.000 0.1801 2010-02-18 22:40:00.000 0.0802 2010-02-18 23:00:00.000 -0.6302 2010-02-18 22:40:00.000 -0.7705 2010-02-18 23:00:00.000 -0.5405 2010-02-18 22:40:00.000 -0.660(6 行受影响)*/--2.用输入的日期判断年度最大值，最小值，如输入2010-2-18，找出这年的Z的最大值和最小值。DECLARE @D DATETIMESET @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,zFROM T WHERE RN1=1 OR RN2=1/*stid tm z----------- ----------------------- ---------------------------------------1 2010-02-18 23:00:00.000 0.1802 2010-02-18 22:30:00.000 -0.830(2 行受影响)*/--3.用输入的日期找出当年每月的Z最大值和最小值。DECLARE @Dt DATETIMESET @Dt='2010-2-18';WITH T AS( SELECT RN1=ROW_NUMBER()OVER(PARTITION BY CONVERT(CHAR(7),tm,23) ORDER BY Z), RN2=ROW_NUMBER()OVER(PARTITION BY CONVERT(CHAR(7),tm,23) ORDER BY Z DESC), ym=CONVERT(CHAR(7),tm,23),* FROM [tb] WHERE DATEDIFF(YY,@Dt,tm)=0)SELECT ym,stid,tm,zFROM T WHERE RN1=1 OR RN2=1/*ym stid tm z------- ----------- ----------------------- ---------------------------------------2010-02 1 2010-02-18 23:00:00.000 0.1802010-02 2 2010-02-18 22:30:00.000 -0.830(2 行受影响)*/
[解决办法]
--2，3你自己连吧，我累了create table tb(stid int, tm datetime, z numeric(10,3))insert tbselect 1,'2010-02-18 22:30:00.000',.030 union allselect 1,'2010-02-18 22:35:00.000',.050 union allselect 1,'2010-02-18 22:40:00.000',.080 union allselect 1,'2010-02-18 22:45:00.000',.100 union allselect 1,'2010-02-18 22:50:00.000',.130 union allselect 1,'2010-02-18 22:55:00.000',.150 union allselect 1,'2010-02-18 23:00:00.000',.180 union allselect 2,'2010-02-18 22:30:00.000',-.830 union allselect 2,'2010-02-18 22:35:00.000',-.810 union allselect 2,'2010-02-18 22:40:00.000',-.770 union allselect 2,'2010-02-18 22:45:00.000',-.740 union allselect 2,'2010-02-18 22:50:00.000',-.700 union allselect 2,'2010-02-18 22:55:00.000',-.670 union allselect 2,'2010-02-18 23:00:00.000',-.630 union allselect 5,'2010-02-18 22:30:00.000',-.740 union allselect 5,'2010-02-18 22:35:00.000',-.700 union allselect 5,'2010-02-18 22:40:00.000',-.660 union allselect 5,'2010-02-18 22:45:00.000',-.620 union allselect 5,'2010-02-18 22:50:00.000',-.590 union allselect 5,'2010-02-18 22:55:00.000',-.560 union allselect 5,'2010-02-18 23:00:00.000',-.5401.每个stid的Z在输入的时间范围内的最大值和最小值，并返回最大值、最小值对应的时间。select m.stid,m.tm [max tm],m.z [max z],n.tm [min tm],n.z [min z] from(select stid,tm,z from tb t where tm between '2010-01-01' and '2010-06-01' and not exists(select 1 from tb where tm between '2010-01-01' and '2010-06-01' and t.stid=stid and z>t.z)) m --最大 left join (select stid,tm,z from tb t where tm between '2010-01-01' and '2010-06-01' and not exists(select 1 from tb where tm between '2010-01-01' and '2010-06-01' and t.stid=stid and z<t.z)) n--最小on m.stid=n.stid/*stid max tm max z min tm min z ----------- ------------------------------------------------------ ------------ ------------------------------------------------------ ------------ 1 2010-02-18 23:00:00.000 .180 2010-02-18 22:30:00.000 .0302 2010-02-18 23:00:00.000 -.630 2010-02-18 22:30:00.000 -.8305 2010-02-18 23:00:00.000 -.540 2010-02-18 22:30:00.000 -.740（所影响的行数为 3 行）*/2.用输入的日期判断年度最大值，最小值，如输入2010-2-18，找出这年的Z的最大值和最小值。select stid,tm,z from tb t where datediff(y,tm,'2010-2-18' )=0 and not exists(select 1 from tb where datediff(y,tm,'2010-2-18' )=0 and t.stid=stid and z>t.z)--最大select stid,tm,z from tb t where datediff(y,tm,'2010-2-18' )=0 and not exists(select 1 from tb where datediff(y,tm,'2010-2-18' )=0 and t.stid=stid and z<t.z)--最3.用输入的日期找出当年每月的Z最大值和最小值。select stid,tm,z from tb t where datediff(y,tm,'2010-2-18' )=0 and not exists(select 1 from tb where datediff(y,tm,'2010-2-18' )=0 and month(t.tm)=month(tm) and t.stid=stid and z>t.z)--最大select stid,tm,z from tb t where datediff(y,tm,'2010-2-18' )=0 and not exists(select 1 from tb where datediff(y,tm,'2010-2-18' )=0 and month(t.tm)=month(tm) and t.stid=stid and z<t.z)--最
[解决办法]
create table tb( stid int, tm datetime, z numeric(10,3))insert into tbselect 1, '2010-02-18 22:30:00.000' , .030 union allselect 1, '2010-02-18 22:35:00.000' , .050 union allselect 1, '2010-02-18 22:40:00.000' , .080 union allselect 1, '2010-02-18 22:45:00.000' , .100 union allselect 1, '2010-02-18 22:50:00.000' , .130 union allselect 1, '2010-02-18 22:55:00.000' , .150 union allselect 1, '2010-02-18 23:00:00.000' , .180 union allselect 2, '2010-02-18 22:30:00.000' , -.830 union allselect 2, '2010-02-18 22:35:00.000' , -.810 union allselect 2, '2010-02-18 22:40:00.000' , -.770 union allselect 2, '2010-02-18 22:45:00.000' , -.740 union allselect 2, '2010-02-18 22:50:00.000' , -.700 union allselect 2, '2010-02-18 22:55:00.000' , -.670 union allselect 2, '2010-02-18 23:00:00.000' , -.630 union allselect 5, '2010-02-18 22:30:00.000' , -.740 union allselect 5, '2010-02-18 22:35:00.000' , -.700 union allselect 5, '2010-02-18 22:40:00.000' , -.660 union allselect 5, '2010-02-18 22:45:00.000' , -.620 union allselect 5, '2010-02-18 22:50:00.000' , -.590 union allselect 5, '2010-02-18 22:55:00.000' , -.560 union all select 5, '2010-02-18 23:00:00.000' , -.540 select a.stid ,max(case z when maxz then tm else null end) as maxtm , sum(case z when maxz then z else 0 end) maxz, max(case z when minz then tm else null end) as mintm , sum(case z when minz then z else 0 end) minzfrom tb a join (select stid, MAX(z) maxz,MIN(z) minz from tb group by stid) b on a.stid=b.stidwhere convert(varchar(10),a.tm,120)='2010-02-18'group by a.stiddrop table tb