再送分...分组求最大最小值之后,还要取得该最大最小值出现的时间...(问题已获解决,现寻求可读性最优的代码.)

Question

感谢昨夜小楼...
以下是昨夜小楼的简洁代码.select
    a.cMeascode,
    a.iEquimentId,
    ScadaTime=convert(varchar(14),a.dtScadaTime,120)+'00:00',
    max_val=max(a.nAloge),
    time_at_max=min(a.dtScadaTime),
    min_val=min(b.nAloge),
    time_at_min=min(b.dtScadaTime)
from @t_scadaTMeas a join @t_scadaTMeas b on a.cMeascode=b.cMeascode and a.iEquimentId=b.iEquimentId and convert(varchar(14),a.dtScadaTime,120)=convert(varchar(14),b.dtScadaTime,120)
where a.nAloge=(select max(nAloge) from @t_scadaTMeas where cMeascode=a.cMeascode and iEquimentId=a.iEquimentId and convert(varchar(13),dtScadaTime,120)=convert(varchar(13),a.dtScadaTime,120))
    and b.nAloge=(select min(nAloge) from @t_scadaTMeas where cMeascode=b.cMeascode and iEquimentId=b.iEquimentId and convert(varchar(13),dtScadaTime,120)=convert(varchar(13),b.dtScadaTime,120))
group by a.cMeascode,a.iEquimentId,convert(varchar(14),a.dtScadaTime,120)+'00:00'
/*
cMeascode iEquimentId ScadaTime           max_val time_at_max              min_val time_at_min
--------- ----------- ------------------- ------- ------------------------ ------- ------------------------
a1        1           2007-11-20 01:00:00 3.0     2007-11-20 01:01:03.000  1.0     2007-11-20 01:01:00.000
a1        1           2007-11-20 05:00:00 3.0     2007-11-20 05:01:00.000  1.0     2007-11-20 05:59:03.000
a2        2           2007-11-20 03:00:00 2.0     2007-11-20 03:21:03.000  1.0     2007-11-20 03:11:00.000
a3        3           2007-11-20 23:00:00 3.0     2007-11-20 23:01:00.000  3.0     2007-11-20 23:01:00.000
*/

Answer 1

如果是sql2005，用partition by 就可以了

Answer 2

增加了求平均值之后...仍然是正确的.
再次,非常感谢昨天晚上的小楼...select
    a.cMeascode,
    a.iEquimentId,
    ScadaTime=convert(varchar(14),a.dtScadaTime,120)+'00:00',
    avg(c.nAloge) avg_val,
    max(a.nAloge) max_val,
    max(a.dtScadaTime) time_at_max,
    min(b.nAloge) min_val,
    min(b.dtScadaTime) time_at_min
from t_scadaTMeas a join t_scadaTMeas b on a.cMeascode=b.cMeascode and a.iEquimentId=b.iEquimentId and convert(varchar(14),a.dtScadaTime,120)=convert(varchar(14),b.dtScadaTime,120) 
                    Join t_scadaTMeas c on (a.cMeascode=c.cMeascode and a.iEquimentId=c.iEquimentId and convert(varchar(14),a.dtScadaTime,120)=convert(varchar(14),c.dtScadaTime,120))
where a.nAloge=(select max(nAloge) from t_scadaTMeas where cMeascode=a.cMeascode and iEquimentId=a.iEquimentId and convert(varchar(13),dtScadaTime,120)=convert(varchar(13),a.dtScadaTime,120))
    and b.nAloge=(select min(nAloge) from t_scadaTMeas where cMeascode=b.cMeascode and iEquimentId=b.iEquimentId and convert(varchar(13),dtScadaTime,120)=convert(varchar(13),b.dtScadaTime,120))
group by a.cMeascode,a.iEquimentId,convert(varchar(14),a.dtScadaTime,120)+'00:00'

Answer 3

……
我以为失忆了，明明昨晚在CSDN没发言。

Answer 4

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')
GOSELECT   x.h dt_Hour,
 x.i iEquimentId,
 x.c cMeascode,
 x.dt max_nAloge_time,
 x.maxN max_nAloge,
 y.dt min_nAloge_time,
 y.minN min_nAloge,
 avgN avg_nAloge
FROM
(SELECT MIN(dtScadaTime) dt,maxN,h,i,c,avg(nAloge) avgN
FROM t_scadaTMeas a
INNER JOIN
(SELECT MAX(nAloge) maxN,CONVERT(VARCHAR(13),dtScadaTime,120) h,iEquimentId i,cMeascode c
FROM t_scadaTMeas GROUP BY CONVERT(VARCHAR(13),dtScadaTime,120) ,iEquimentId,cMeascode
) b
ON h=CONVERT(VARCHAR(13),dtScadaTime,120) AND maxN=nAloge AND iEquimentId=i AND cMeascode=c
GROUP BY maxN,h,i,c
) x
INNER JOIN
(SELECT MIN(a.dtScadaTime) dt,minN,h,i,c 
FROM t_scadaTMeas a
INNER JOIN
(SELECT MIN(nAloge) minN,CONVERT(VARCHAR(13),dtScadaTime,120) h,iEquimentId i,cMeascode c
FROM t_scadaTMeas GROUP BY CONVERT(VARCHAR(13),dtScadaTime,120) ,iEquimentId,cMeascode
) b
ON h=CONVERT(VARCHAR(13),dtScadaTime,120) AND minN=nAloge AND iEquimentId=i AND cMeascode=c
GROUP BY minN,h,i,c
) y
ON x.h=y.h AND x.i=y.i AND y.c=x.c

Answer 5

Fcuandy,随手敲的,不排除手误...
性能最高,高出昨夜小楼给出代码的两个数量级...
而可阅读性中等,特表示感谢! 改天重新开贴散分.

Answer 6

SELECT      x.h dt_Hour,
     x.i iEquimentId,
     x.c cMeascode,
     x.dt max_nAloge_time,
     x.maxN max_nAloge,
     y.dt min_nAloge_time,
     y.minN min_nAloge,
avgN
FROM
    (SELECT Max(dtScadaTime) dt,maxN,h,i,c
        FROM t_scadaTMeas a
    INNER JOIN
        (SELECT MAX(nAloge) maxN,CONVERT(VARCHAR(13),dtScadaTime,120) h,iEquimentId i,cMeascode c
            FROM t_scadaTMeas GROUP BY CONVERT(VARCHAR(13),dtScadaTime,120) ,iEquimentId,cMeascode
        ) b
        ON h=CONVERT(VARCHAR(13),dtScadaTime,120) AND maxN=nAloge AND iEquimentId=i AND cMeascode=c
        GROUP BY maxN,h,i,c
    ) xINNER JOIN
    (SELECT MIN(a.dtScadaTime) dt,minN,h,i,c 
        FROM t_scadaTMeas a
    INNER JOIN
        (SELECT MIN(nAloge) minN,CONVERT(VARCHAR(13),dtScadaTime,120) h,iEquimentId i,cMeascode c
            FROM t_scadaTMeas GROUP BY CONVERT(VARCHAR(13),dtScadaTime,120) ,iEquimentId,cMeascode
        ) b
        ON h=CONVERT(VARCHAR(13),dtScadaTime,120) AND minN=nAloge AND iEquimentId=i AND cMeascode=c
        GROUP BY minN,h,i,c
    ) y    ON x.h=y.h AND x.i=y.i AND x.c=y.c INNER JOIN
    (SELECT avgN,h,i,c 
        FROM t_scadaTMeas a
    INNER JOIN
        (SELECT avg(nAloge) avgN,CONVERT(VARCHAR(13),dtScadaTime,120) h,iEquimentId i,cMeascode c
            FROM t_scadaTMeas GROUP BY CONVERT(VARCHAR(13),dtScadaTime,120) ,iEquimentId,cMeascode
        ) b
        ON h=CONVERT(VARCHAR(13),dtScadaTime,120) AND iEquimentId=i AND cMeascode=c
        GROUP BY avgN,h,i,c
    ) zOn x.h=z.h AND x.i=z.i AND x.c=z.c