两张表的最大值查寻SQL语句

[leeyunpeng]

有两张表，第一张：表名yc_001，域名cur_001,occur_time；第二张：表名yc_002，域名cur_002,occur_time，其中两张表的occur_time表时间，且值相同，求两张表的“cur_001＋cur_002”的最大值和时间。

[人生无悔]

select y1.cur_001+y2.cur_002,to_char(y1.occur_time,'yyyy-mm-dd')
from yc_001 y1,yc_002 y2
where y1.occur_time=y2.occur_time
	and y1.cur_001+y2.cur_002=(select max(y3.cur_001+y4.cur_002) from yc_001 y3,yc_002 y4
		where y3.occur_time=y4.occur_time);

[fw0124]

with t as (
select cur_001, cur_002, yc_001.occur_time from yc_001 join yc_002
  on yc_001.occur_time=yc_002.occur_time
)
select  total, occur_time from
(select cur_001+cur_002 as total, rank() over (order by (cur_001+cur_002) desc) rk, occur_time from t)
where rk=1;