SELECT *
FROM (SELECT
bh,
RQ,
SUM(SE) OVER(PARTITION BY bh ) HJ
FROM SB_q
WHERE
MONTHS_BETWEEN(SYSDATE,RQ) <= 3) b
WHERE HJ = 0
这个语句在 oracle 10g 上能运行 但是在 oracle8.1上好像有问题
FROM (SELECT
bh,
RQ,
SUM(SE) OVER(PARTITION BY bh ) HJ
FROM SB_q
WHERE
MONTHS_BETWEEN(SYSDATE,RQ) <= 3) b
WHERE HJ = 0
这个语句在 oracle 10g 上能运行 但是在 oracle8.1上好像有问题
SELECT
bh,
RQ,
SUM(SE) OVER(PARTITION BY bh ) HJ
FROM SB_q
WHERE
MONTHS_BETWEEN(SYSDATE,RQ) <= 3
不加嵌套的能运行
不会吧。我印象中,over也是9以后的函数
SQL*Plus: Release 3.3.4.0.0 - Production on Wed Mar 18 13:25:26 2009Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - ProductionSQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12る-80 800 20
7499 ALLEN SALESMAN 7698 20-2る -81 1600 300 30
7521 WARD SALESMAN 7698 22-2る -81 1250 500 30
7566 JONES MANAGER 7839 02-4る -81 2975 20
7654 MARTIN SALESMAN 7698 28-9る -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5る -81 2850 30
7782 CLARK MANAGER 7839 09-6る -81 2450 10
7839 KING PRESIDENT 17-11る-81 5000 10
7844 TURNER SALESMAN 7698 08-9る -81 1500 0 30
7900 JAMES CLERK 7698 03-12る-81 950 30
7902 FORD ANALYST 7566 03-12る-81 3000 20
7934 MILLER CLERK 7782 23-1る -82 1300 10SQL> select empno,ename,job,sum(sal) over (partition by job) salary from emp
2 where deptno = '30'; EMPNO ENAME JOB SALARY
---------- ---------- --------- ----------
7900 JAMES CLERK 950
7698 BLAKE MANAGER 2850
7499 ALLEN SALESMAN 5600
7521 WARD SALESMAN 5600
7844 TURNER SALESMAN 5600
7654 MARTIN SALESMAN 5600
/*增加函数可用*/
SQL> select empno,ename,job,sum(sal) over (partition by job) salary from emp
2 where deptno = '30'
3 and months_between(sysdate,hiredate) > 330; EMPNO ENAME JOB SALARY
---------- ---------- --------- ----------
7698 BLAKE MANAGER 2850
7499 ALLEN SALESMAN 4350
7844 TURNER SALESMAN 4350
7521 WARD SALESMAN 4350/*子查询可用*/
SQL> select * from
2 (select empno,ename,job,sum(sal) over (partition by job) salary from emp
3 where deptno = '30'
4 and months_between(sysdate,hiredate) > 330); EMPNO ENAME JOB SALARY
---------- ---------- --------- ----------
7698 BLAKE MANAGER 2850
7499 ALLEN SALESMAN 4350
7844 TURNER SALESMAN 4350
7521 WARD SALESMAN 4350SQL>