sql2005，用partition by 就可以了? 分组，求最大（最小）值即该值出现的时刻。

Question

樓主貼的數據和結果？不對
max(col)over(partition by 組列)--用的方法

Answer 1

以 cMeascode , iEquimentId 及整小时进行分组；也就是某个测量量，某时段（例如8点，9点...）进行统计，求最大值，最小值，以及最大最小值发生的时刻。

Answer 2

乱了点，请自己优化一下吧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') select distinct cMeascode,iEquimentId,
max(convert(datetime,convert(char(13),dtScadaTime,21)+':00:00')) 
    over(partition by cMeascode,convert(char(13),dtScadaTime,21)) as ScadaTime,
max(nAloge) 
    over(partition by cMeascode,convert(char(13),dtScadaTime,21)) as max_val,
max(dtScadaTime) 
    over(partition by cMeascode,convert(char(13),dtScadaTime,21)) as max_time,
min(nAloge) 
    over(partition by cMeascode,convert(char(13),dtScadaTime,21)) as min_val,
min(dtScadaTime) 
    over(partition by cMeascode,convert(char(13),dtScadaTime,21)) as min_time
from #t_scadaTMeas
/*
cMeas iEqui ScadaTime               max_val max_time                min_val min_time
----- ----- ----------------------- ------- ----------------------- ------- -----------------------
a1    1      2007-11-20 01:00:00.000 3      2007-11-20 01:59:03.000 1       2007-11-20 01:01:00.000
a1    1      2007-11-20 05:00:00.000 3      2007-11-20 05:59:03.000 1       2007-11-20 05:01:00.000
a2    2      2007-11-20 03:00:00.000 2      2007-11-20 03:59:03.000 1       2007-11-20 03:11:00.000
a3    3      2007-11-20 23:00:00.000 3      2007-11-20 23:59:03.000 3       2007-11-20 23:01:00.000(4 行受影响)
*/

Answer 3

select 
cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)+':00:00',
max_val=max(nAloge),[time_at_maxVal]=max(dtScadaTime),
min_val=min(nAloge),[time_at_minVal]=min(dtScadaTime)
from 
t_scadaTMeas
group by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)

Answer 4

select 
distinct
cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)+':00:00',
max_val=max(nAloge)over(partition by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)),
[time_at_maxVal]=max(dtScadaTime)over(partition by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)),
min_val=min(nAloge)over(partition by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)),
[time_at_minVal]=min(dtScadaTime)over(partition by cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120))
from 
t_scadaTMeas

Answer 5

這樣的結果最好還是用 group by 05以cMeascode,iEquimentId,convert(varchar(13),dtScadaTime,120)作為組

Answer 6

注意：time_at_maxVal   time_at_minVa这两列的值与需求是不吻合的。=== 要求的查询结果 
cMeascode  iEquimentId  ScadaTime              max_val  time_at_max            min_val  time_at_min 
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' 
a1          1            '2007/11/20 23:00:00'        3  '2007/11/20 23:01:00'        3    '2007/11/20 23:01:00' === 上面给出的查询语句，实际的查询结果 
a1      1 2007-11-20 01:00:00 3 2007-11-20 01:59:03.000 1 2007-11-20 01:01:00.000 
a1      1 2007-11-20 05:00:00 3 2007-11-20 05:59:03.000 1 2007-11-20 05:01:00.000 
a2      2 2007-11-20 03:00:00 2 2007-11-20 03:59:03.000 1 2007-11-20 03:11:00.000 
a3      3 2007-11-20 23:00:00 3 2007-11-20 23:59:03.000 3 2007-11-20 23:01:00.000 

Answer 7

--这个有什么问题?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')
;
with cte as
(
   select cMeascode ,iEquimentId,
          ScadaTime=convert(varchar(13),dtScadaTime,120)+':00:00',
          max_val=max(nAloge) over(partition by cMeascode ,iEquimentId,convert(varchar(13),dtScadaTime,120)),
          time_at_max=max(dtScadaTime) over(partition by cMeascode ,iEquimentId,convert(varchar(13),dtScadaTime,120)),
          min_val=min(nAloge) over(partition by cMeascode ,iEquimentId,convert(varchar(13),dtScadaTime,120)),
          time_at_min=min(dtScadaTime) over(partition by cMeascode ,iEquimentId,convert(varchar(13),dtScadaTime,120))
   from t_scadaTMeas
)
select distinct * from ctedrop table t_scadaTMeas/*
cMeascode iEquimentId ScadaTime           max_val                time_at_max             min_val                time_at_min
--------- ----------- ------------------- ---------------------- ----------------------- ---------------------- -----------------------
a1        1           2007-11-20 01:00:00 3                      2007-11-20 01:59:03.000 1                      2007-11-20 01:01:00.000
a1        1           2007-11-20 05:00:00 3                      2007-11-20 05:59:03.000 1                      2007-11-20 05:01:00.000
a2        2           2007-11-20 03:00:00 2                      2007-11-20 03:59:03.000 1                      2007-11-20 03:11:00.000
a3        3           2007-11-20 23:00:00 3                      2007-11-20 23:59:03.000 3                      2007-11-20 23:01:00.000(4 行受影响)
*/

Answer 8

select 
    b.cMeascode,b.iEquimentId,b.ScadaTime+':00:00',
    max_val=b.MaxnAloge,
[time_at_maxVal]=min(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 個資料列受到影響)

Answer 9

非常感谢Roy_88，中国风。
你的代码效率比我原先使用的子查询性能提高了一个数量级。完全满足需要(原先的查询成了一个性能瓶颈)，而且，并不需要在SQL2005下，SQL2000下也可以执行上面的代码。