17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM EMP;
CREATE TABLE t AS SELECT * FROM emp WHERE 1=2;
INSERT INTO t(empno) SELECT empno FROM emp;
SELECT EMPNO, SAL FROM T ORDER BY HIREDATE;
--嵌套两次
UPDATE T
SET SAL =
(SELECT EN
FROM (SELECT EMPNO, LAG(EMPNO, 1, 0) OVER(ORDER BY HIREDATE) EN
FROM EMP) TMP
WHERE TMP.EMPNO = T.EMPNO)
WHERE T.EMPNO > 7788;
SELECT EMPNO, SAL FROM T ORDER BY HIREDATE;
empno sal
------ -----------
7369
7499
7521
7566
7654
7698
7934 7902.00
7788
7839 7654.00
7844 7782.00
7876 7788.00
7900 7839.00
7902 7900.00
7782
--嵌套一次
UPDATE T
SET SAL =
(SELECT LAG(EMPNO, 1, 0) OVER(ORDER BY HIREDATE) EN
FROM EMP
WHERE EMP.EMPNO = T.EMPNO)
WHERE T.EMPNO > 7788;
SELECT EMPNO, SAL FROM T ORDER BY HIREDATE;
empno sal
------ --------
7369
7499
7521
7566
7654
7698
7934 0.00
7788
7839 0.00
7844 0.00
7876 0.00
7900 0.00
7902 0.00
7782