统计，求指定时段内的平均值、最大（最小）值，以及最值出现的时刻

来源：互联网发布：禁毒知识网络竞赛编辑：程序博客网时间：2024/05/20 15:59

题：

/* ============================================================ */ /*  Table: t_scadaTMeas                                                  */ /* 分组，求最大（最小）值，以及该值出现的时刻。若最值多次出现，取第一次出现的时刻。 */ /* ============================================================ */ create table t_scadaTMeas (     dtScadaTime  datetime              not null,     nAloge      float                null    ,     iEquimentId  integer              not null,     cMeascode    character(8)          not null,     constraint PK_T_SCADAHOURMEAS primary key (dtScadaTime, iEquimentId, cMeascode) ) go insert into t_scadaTMeas values('2007/11/20 01:01:00',    1,  1, 'a1') insert into t_scadaTMeas values('2007/11/20 01:01:03',    3,  1, 'a1') insert into t_scadaTMeas values('2007/11/20 01:59:03',    3,  1, 'a1') insert into t_scadaTMeas values('2007/11/20 05:01:00',    3,  1, 'a1') insert into t_scadaTMeas values('2007/11/20 05:01:03',    2,  1, 'a1') insert into t_scadaTMeas values('2007/11/20 05:59:03',    1,  1, 'a1') insert into t_scadaTMeas values('2007/11/20 03:11:00',    1,  2, 'a2') insert into t_scadaTMeas values('2007/11/20 03:21:03',    2,  2, 'a2') insert into t_scadaTMeas values('2007/11/20 03:59:03',    1,  2, 'a2') insert into t_scadaTMeas values('2007/11/20 23:01:00',    3,  3, 'a3') insert into t_scadaTMeas values('2007/11/20 23:01:03',    3,  3, 'a3') insert into t_scadaTMeas values('2007/11/20 23:59:03',    3,  3, 'a3') /* === 查询结果 cMeascode  iEquimentId  ScadaTime              max_val  time_at_maxVal          min_val  time_at_minVal a1          1            '2007/11/20 01:00:00'        3  '2007/11/20 01:01:03'        1    '2007/11/20 01:01:00' a1          1            '2007/11/20 05:00:00'        3  '2007/11/20 05:01:00'        1    '2007/11/20 05:59:03' a2          2            '2007/11/20 03:00:00'        2  '2007/11/20 03:21:03'        1    '2007/11/20 03:11:00' a3          1            '2007/11/20 23:00:00'        3  '2007/11/20 23:01:00'        3    '2007/11/20 23:01:00' */

解：select
    b.cMeascode,b.iEquimentId,b.ScadaTime+':00:00',
    max_val=b.MaxnAloge,
    [time_at_maxVal]=max(case when a.nAloge=b.MaxnAloge then a.dtScadaTime end),
    min_val=b.MinnAloge,
    [time_at_minVal]=min(case when a.nAloge=b.MinnAloge then a.dtScadaTime end)
from
    t_scadaTMeas a
join
    (select cMeascode,iEquimentId,ScadaTime=convert(varchar(13),dtScadaTime,120),max(nAloge)MaxnAloge,min(nAloge)MinnAloge
    from t_scadaTMeas group by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)) b
    on a.cMeascode=b.cMeascode and a.iEquimentId=b.iEquimentId and convert(varchar(13),a.dtScadaTime,120)=b.ScadaTime and a.nAloge in(b.MaxnAloge,b.MinnAloge)
group by b.cMeascode,b.iEquimentId,b.ScadaTime,b.MinnAloge,b.MaxnAloge

/*
cMeascode iEquimentId                     max_val                time_at_maxVal          min_val                time_at_minVal
--------- ----------- ------------------- ---------------------- ----------------------- ---------------------- -----------------------
a1        1           2007-11-20 01:00:00 3                      2007-11-20 01:01:03.000 1                      2007-11-20 01:01:00.000
a1        1           2007-11-20 05:00:00 3                      2007-11-20 05:01:00.000 1                      2007-11-20 05:59:03.000
a2        2           2007-11-20 03:00:00 2                      2007-11-20 03:21:03.000 1                      2007-11-20 03:11:00.000
a3        3           2007-11-20 23:00:00 3                      2007-11-20 23:01:00.000 3                      2007-11-20 23:01:00.000

(4 個資料列受到影響)
*/