|
|
EDA365欢迎您登录！
您需要 登录 才可以下载或查看，没有帐号？注册
x
本帖最后由 wan525jun 于 2023-4-20 23:14 编辑
l6 p2 _8 b& }& ]4 b" Q& j, @
' d2 x4 r1 ?$ \分析函数last_value的疑惑
+ E# x8 N5 `+ `7 F
0 Q! P9 U2 j( L' Z+ x) x- V+ I6 [# T7 @# j* u$ {
[php]SQL> select * from z;
" Z5 P3 @' w$ i; [: h0 \
8 u7 e+ S# E/ y) y2 ^' N* ]3 lEMP_ID MANAGER_ID LEV PART$ ]3 \' i: ?# B* W. K% F" K. S
---------- ---------- ---------- ----------0 u% ~+ U1 [8 j2 k! v2 h- M
001 101 1 0( M& M. l* g& H/ w. P8 E0 Z) F
101 201 2 0 _ ^( E" o2 j! Y5 n9 s
201 301 3 00 k0 I8 K" | ]/ M$ Q" e
401 405 1 1
/ a% l+ [/ w. K108 401 2 1
|4 _8 N; b; g1 \$ u
% ^1 h5 v+ l) ~( ?$ a0 nSQL> select first_value (emp_id) over (partition by part
; ?4 {' C9 `4 [; [* G- n! o
, E! a8 M; z" Korder by lev) e_id,& K8 g2 Y, M0 l! V% p3 x& q
2 manager_id m_id,
" o' E4 X' }" x' J5 X; k- k 3 lev,# n' k/ f: l; Z5 a3 X4 h' j2 o
4 part: ?8 I2 W7 K: ~. n* C# x
5 from z;4 |& \1 K# k6 ]# \
- N1 x3 L0 z8 q* z, ^ yE_ID M_ID LEV PART, L8 i8 `; x# Z2 t4 H* L$ e7 c W
---------- ---------- ---------- ----------
& t7 I6 l+ |3 I, C7 [8 J001 101 1 03 m7 D4 S% d9 ^ J
001 201 2 0
* L2 x3 N7 Y# l* o6 m! E2 t001 301 3 0/ B5 A. J; K% K1 R
401 405 1 1- S6 Q. B8 ^4 b$ S; R
401 401 2 1
! @9 S% [! p1 w8 c9 N) @0 Z+ @0 ]3 }# _1 T; @
SQL> select last_value (emp_id) over (partition by part ?7 ?# x& U }1 d# P
! V; }; O E# [( D
order by lev) e_id,% w7 F5 Z) `- S# \: R1 R6 U
2 manager_id m_id,
* g, O9 C. d: k" E1 n# q 3 lev,5 f' f' O) s% k1 E" `7 a
4 part
2 Q, P; Y/ d, b% i5 { 5 from z;
" b+ `: X# b9 R" H4 [+ e5 V3 I- n; s& K4 H0 _! v
E_ID M_ID LEV PART
% s- d+ r& R( M( X) k. b" `---------- ---------- ---------- ----------
; |% A7 v- a* A8 s001 101 1 0
7 |+ y2 @: Y9 s U' |# G101 201 2 0, w/ k. B) B+ T. B# f( x) R
201 301 3 0
2 R8 Q' r( S$ F4 [( u401 405 1 1
4 y" m% J6 p7 ^% U& Z- z108 401 2 1
. M8 t) c2 V& i( W6 E: X6 V1 D) Y-------------------------------------------------------
0 E/ e+ s: k1 U0 o( L5 ~/ t[/php]
$ Y+ U) L$ c# G4 }+ B+ S7 E/ V1 |# J! _( B
6 D& y; }( d0 J/ N
; x$ B3 ?1 W5 C0 `5 u# h' W- F5 M9 l! w( E0 s* M( r5 c
- [& X* \& M- U# z i5 h# Q
; V5 O8 k) R0 W, F% r* Z1 a& L- R
. V* d% B0 M x [. h
|
|
/1 
发表于 2023-4-20 23:06
收藏
淘帖
支持！
反对！