您可以使用聚合的keep子句返回由另一个排序的第一个/最后一个值:
create table t (
c1 date,c2 varchar2(10),c3 varchar2(10)
);
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
insert into t values ( '2019-05-27 08:37:07','0242625','00265929' );
insert into t values ( '2019-05-27 07:52:29','0282971','0282972','0696864','00265929' );
insert into t values ( '2018-02-13 22:57:09','0282984','0395347','0242712','0242624','0441449','0400026','00265929' );
select max ( c1 ) c1,max ( c2 ) keep (
dense_rank first
order by c1 desc
) c2,c3
from t
group by c3;
C1 C2 C3
2019-05-27 08:37:07 0242625 00265929
,
分析功能可能会有所帮助。您需要的代码从第6行开始。
SQL> with test (upd_dtime,vo_no,item_no) as
2 (select 20190527,242625,265929 from dual union all
3 select 20190213,282984,265929 from dual union all
4 select 20190118,400026,265929 from dual
5 )
6 select upd_dtime,item_no
7 from (
8 select upd_dtime,item_no,9 row_number() over (partition by item_no order by upd_dtime desc) rn
10 from test
11 )
12 where rn = 1;
UPD_DTIME VO_NO ITEM_NO
---------- ---------- ----------
20190527 242625 265929
SQL>
[编辑:已应用于您的表]
由于您无法将上述代码调整为实际表(下一次,发布包含CREATE TABLE和INSERT INTO示例数据的测试用例将是一个好主意),如下所示:>
-
yourq
CTE代表您发布的查询。我不知道您为什么使用嵌入式视图(其别名为y
);为什么不像我一样简单地加入vo_order_t
和vo_item_t
?
- 其余与我之前发布的完全相同
因此:复制/粘贴此代码,然后在您的模式中执行它。如果我没有打错字,那应该没问题。如我所说,如果没有的话,请发布测试用例。
with
yourq as
-- your query,rewritten
(select b.upd_dtime,b.vo_no,a.item_no
from vo_order_t b join vo_item_t a on a.vo_no = b.vo_no
where a.item_no = '00265929'
)
select upd_dtime,item_no
from (
select upd_dtime,row_number() over (partition by item_no order by upd_dtime desc) rn
from yourq
)
where rn = 1;
,
您可以使用MAX() OVER (PARTITION BY ...)
分析函数:
WITH t AS
(
SELECT MAX(v.upd_dtime) OVER (PARTITION BY v.item_no) AS MaxT,v.vo_no AS vo_no,v.item_no,v.upd_dtime
FROM vo_order_t v
WHERE v.item_no = '00265929'
)
SELECT *
FROM t
WHERE upd_dtime = MaxT
如果您使用的是12c +版本,请直接使用FETCH FIRST 1 ROW ONLY
来获取最新文章:
SELECT MAX(v.upd_dtime) OVER (PARTITION BY v.item_no) AS MaxT,v.upd_dtime
FROM vo_order_t v
WHERE v.item_no = '00265929'
FETCH FIRST 1 ROW ONLY
Demo
本文链接:https://www.f2er.com/2964252.html