甲骨文检索最大记录

问题描述

Table_A

A_id
1



Tale_B
B_id       A_id
1            1
2            1
3            1

Table_C

B_id      Process_date
1   20130101 12:20:01
2   20130101 12:10:01
3   20130101 13:00:01

如何基于Table_C计时窗口使用Table_A A_id的引用从Table_C检索最大process_date.如果我想在计时窗口20130101 12:09:00到12:21:00检索Table_C id和max(process_date)，则它应返回id为1，process_date为12:20:01

How to retrieve the maximum process_date from Table_C with references of Table_A A_id based on Table_C timing window.If i want to retrieve Table_C id and max(process_date) in timing window 20130101 12:09:00 to 12:21:00 then it should return id as 1 and process_date as 12:20:01

推荐答案

您可以使用获取max(process_date)的子查询:

You can use a subquery that gets the max(process_date):

select c1.b_id,
  c2.MaxDate
from table_a a
inner join table_b b
  on a.a_id = b.a_id
inner join table_c c1
  on b.b_id = c1.b_id
inner join
(
  select max(process_date) MaxDate
  from table_c
) c2
  on c1.process_date = c2.maxdate;

请参见带演示的SQL提琴

或者您可以使用row_number():

select b_id, process_date
from
(
  select c1.b_id,
    c1.process_date,
    row_number() over(partition by a.a_id order by c1.process_date desc) rn
  from table_a a
  inner join table_b b
    on a.a_id = b.a_id
  inner join table_c c1
    on b.b_id = c1.b_id
)
where rn = 1

请参见带有演示的SQL提琴

这篇关于甲骨文检索最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！