求一sql

[haibozhang]

create table BANDWIDTHPOWER
(
DATETIME date not null,
DESCONTENT NUMBER(10),
FVALUE NUMBER(10) not null,
TS0AVGDISTURBPOW NUMBER(10),
TS0AVGBANDWIDTHPOW NUMBER(10)
}

现在需要求在一个时间段内，TS0AVGDISTURBPOW和TS0AVGBANDWIDTHPOW的最大值，
并求此段时间内，最近的那个时间的DESCONTENT值？如何写sql?

[complayer]

可以分两步做：
select max(TS0AVGDISTURBPOW),max(TS0AVGBANDWIDTHPOW) from BANDWIDTHPOWER
where Datetime between d1 and d2;

select descontent from BANDWIDTHPOWER
where datetime=( select max(Datetime) from bandwidthpower
where datetime between d1 and d2);

[haibozhang]

能不能在一个sql里面搞定?

[waterfirer]

select max(TS0AVGDISTURBPOW),max(TS0AVGBANDWIDTHPOW),(select descontent
from BANDWIDTHPOWER where datetime=( select max(Datetime) from bandwidthpower
where datetime between d1 and d2))
from BANDWIDTHPOWER
where Datetime between d1 and d2;

[henghowzc]

上面的大哥牛啊，这个可以运行吗？你用的是oracle吗？
我怎么不行啊，怀疑中....
你写的根本不行啊。我倒

[henghowzc]

select descontent
from BANDWIDTHPOWER where datetime=( select max(Datetime) from bandwidthpower
where datetime between d1 and d2 ＝×

select max(TS0AVGDISTURBPOW),max(TS0AVGBANDWIDTHPOW),(×)
from BANDWIDTHPOWER
where Datetime between d1 and d2;

有这样套用的啊？晕死，根本不可以的

[waterfirer]

SQL> select max(b),max(c),max(a) from
2 (select datetime,b,c,
3 (select a from test where datetime=
4 (select max(datetime) from test where
5 datetime between sysdate-5 and sysdate-2)) a
6 from test) table1
7 where table1.datetime between sysdate-5 and sysdate;

MAX(B) MAX(C) MAX(A)
---------- ---------- ----------
20 52 2

SQL> select * from test;

DATETIME A B C
---------- ---------- ---------- ----------
26-7月 -05 1 1 1
25-7月 -05 2 2 2
24-7月 -05 5 20 2
23-7月 -05 3 10 52
22-7月 -05 7 6 8

SQL>

[沝林]

select distinct max(TS0AVGDISTURBPOW) over(),
max(TS0AVGBANDWIDTHPOW) over(),
first_value(DESCONTENT) over(order by DATETIME)
from BANDWIDTHPOWER
where DATETIME between ...and ...

[沝林]

上面是最小的日期的DESCONTENT，改正下：

select distinct max(TS0AVGDISTURBPOW) over(),
max(TS0AVGBANDWIDTHPOW) over(),
first_value(DESCONTENT) over(order by DATETIME desc)
from BANDWIDTHPOWER
where DATETIME between ...and ...

[henghowzc]

顶上楼