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中获取最大的process_date,并基于Table_C计时窗口引用Table_A a__id。如果我想在定时窗口20130101 12:09:00到12:21:00中检索Table_C id和max(process_date),那么它应该返回id为1,process_date为12:20:01
您可以使用获取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 Fiddle with Demo
或者您可以使用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 Fiddle with Demo
这个查询应该得到每个Table_A
中引用的B_id
的最大process_date
:
SELECT c.B_id, MAX(Process_date)
FROM Table_C c
INNER JOIN Table_B b
ON b.B_id = c.B_id
INNER JOIN Table_A a
ON a.A_ID = b.A_id
GROUP BY c.B_id
只要从选择中删除c.B_id
和GROUP BY
,如果你想要所有B_id的最大process_date
与Table_A
的引用。