我有下面的更新表.....
| refer | event_date | column detail | event_type | cat1 |
|---|---|---|---|---|
| 2 | yesterday | abc | type 3 | cat x |
| 2 | last week | abc3 | type 11 | cat b |
| 2 | today | abc123 | type 4 | cat a |
| 2 | last month | xyz | type 22 | cat z |
| 2 | last year | wtf | type 11 | cat z |
因此，对于REFER=2
abc123 is the latest update based on latest date.
abc3 is the latest update for event_type 11 and cat = b
xyz is the latest update for cat z
是否可以在单个查询中完成此操作？我可以获得结果的唯一方法是使用单独的查询或CTE:
with cte1 as (
select t.refer,
t.detail
from
(
select ch.refer,
ch.detail,
row_number() over (partition by refer order by event_date desc) as rn
from table ch
) as latest
where ch.rn = 1
)
cte2 as(
select t.refer,
t.detail
from
(
select ch.refer,
ch.detail,
row_number() over (partition by refer order by event_date desc) as rn
from table ch
where event_type = '11'
and cat = 'b'
) as latest
where ch.rn = 1
)
cte3 as(
select t.refer,
t.detail
from
(
select ch.ref,
ch.detail,
row_number() over (partition by refer order by event_date desc) as rn
from table ch
where event_type = 'z'
) as latest
where ch.rn = 1
and cat = 'z'
);