SELECT COUNT(*),SUM(ITEM01)/COUNT(*),SUM(ITEM01)
FROM V_GZGL_PERSON A,(SELECT ITEMID FROM DT_ZZGL_UNIT
START WITH ITEMID='408130000000004' CONNECT BY FITEMID=PRIOR ITEMID) B
WHERE DWID =ITEMID
...全文
615打赏收藏
忙烦大侠们解释一下sql语句的意思 ,好了绝对给分(50)
SELECT COUNT(*),SUM(ITEM01)/COUNT(*),SUM(ITEM01) FROM V_GZGL_PERSON A,(SELECT ITEMID FROM DT_ZZGL_UNIT START WITH ITEMID='408130000000004' CONNECT BY FITEMID=PRIOR ITEMID) B WHERE DWID =ITEMID
select lpad(' ',4*(level-1))||ename name ,empno,mgr from emp
start with mgr is null
connect by prior empno=mgr;
NAME EMPNO MGR
-------------------- --------- ---------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
§7.3.2 遍历至根
例2:现在要从某个雇员往他的上级列出该雇员的层次结构(从顶到底):
SQL> col ename for a30
SQL> l
select lpad(' ',4*(level-1))||ename ename,mgr,empno from emp
start with mgr=7788
connect by prior mgr= empno
SQL> /
ENAME MGR EMPNO
------------------------------ ----- ---------
ADAMS 7788 7876
SCOTT 7566 7788
JONES 7839 7566
KING 7839
例3:现在要列出所有雇员的层次结构(从顶到底):
select lpad(' ',4*(level-1))||ename name ,empno,mgr from emp
start with mgr is not null
connect by empno=prior mgr
NAME EMPNO MGR
-------------------- --------- -----
SMITH 7369 7902
FORD 7902 7566
JONES 7566 7839
KING 7839
ALLEN 7499 7698
BLAKE 7698 7839
KING 7839
WARD 7521 7698
BLAKE 7698 7839
KING 7839
JONES 7566 7839
KING 7839
MARTIN 7654 7698
BLAKE 7698 7839
KING 7839
BLAKE 7698 7839
KING 7839
CLARK 7782 7839
KING 7839
SCOTT 7788 7566
JONES 7566 7839
KING 7839
TURNER 7844 7698
BLAKE 7698 7839
KING 7839
ADAMS 7876 7788
SCOTT 7788 7566
JONES 7566 7839
KING 7839
JAMES 7900 7698
BLAKE 7698 7839
KING 7839
FORD 7902 7566
JONES 7566 7839
KING 7839
MILLER 7934 7782
CLARK 7782 7839
KING 7839