ORACLE表结构如下:
表A 字段code(字符型),pqr(数值型),bdatetime(时间日期型)形如:
code pqr bdatetime
1 20 2010-04-02 12:00:00
2 30 2010-04-02 12:00:00
。
1 30 2010-04-02 13:00:00
2 40 2010-04-02 13:00:00
。
1 40 2010-04-01 12:00:00
2 50 2010-04-01 12:00:00 。求SQL,列出按CODE列表中的PRQ值和24小时内的最大值
结果应该是形如:
code pqr bdatetime max(pqr)
1 20 2010-04-02 12:00:00 40
2 30 2010-04-02 12:00:00 50
。
1 30 2010-04-02 13:00:00 40
2 40 2010-04-02 13:00:00 50
。
1 40 2010-04-01 12:00:00 40
2 50 2010-04-01 12:00:00 50
表A 字段code(字符型),pqr(数值型),bdatetime(时间日期型)形如:
code pqr bdatetime
1 20 2010-04-02 12:00:00
2 30 2010-04-02 12:00:00
。
1 30 2010-04-02 13:00:00
2 40 2010-04-02 13:00:00
。
1 40 2010-04-01 12:00:00
2 50 2010-04-01 12:00:00 。求SQL,列出按CODE列表中的PRQ值和24小时内的最大值
结果应该是形如:
code pqr bdatetime max(pqr)
1 20 2010-04-02 12:00:00 40
2 30 2010-04-02 12:00:00 50
。
1 30 2010-04-02 13:00:00 40
2 40 2010-04-02 13:00:00 50
。
1 40 2010-04-01 12:00:00 40
2 50 2010-04-01 12:00:00 50
from A,
(select code ,max(pqr) AS max_pqr from A group by code ) B
WHERE A.CODE=B.CODE
from A,
(select code ,max(pqr) AS max_pqr from A group by trunc(bdatetime)) B
WHERE A.CODE=B.CODE
看你得到的数据都跨越天了,24?
from A, A B
where abs(a.bdatetime - b.bdatetime) <=1
and a.code = b.code