1.递归查询 查询smith有哪些下级 SELECT ename FROM emp START WITH ename=‘SMITH’ --从谁开始查(当前节点) CONNECT BY PRIOR empno=mgr --查询的方式是从上往下,还是从下往上 PRIOR 修饰下级当前节点向下找, 修饰上级从当前节点往上找
查询smith有哪些领导 SELECT ename FROM emp START WITH ename=‘SMITH’ --从谁开始查 CONNECT BY empno=PRIOR mgr --查询的方式是从上往下,还是从下往上
显示等级 SELECT LEVEL ,ename FROM emp WHERE LEVEL=2 START WITH ename=‘KING’ --从谁开始查 CONNECT BY PRIOR empno= mgr
2.随机抽取n条数据 固定抽取前十条数据 SELECT * FROM emp WHERE ROWNUM <=10
随机抽取 dbsm_random.random() dbms_random是一个存储过程, random() 代表是存储过程的一个方法 SELECT * FROM (SELECT * FROM emp ORDER BY dbms_random.random()) WHERE ROWNUM<=10
生成一个1-10的随机小数 SELECT dbms_random.value(1,10) FROM dual 生成一个1-10随机整数 SELECT TRUNC (dbms_random.value(1,10)) FROM dual
统计, 每个部门有多少人, 每个部门每个工种有多少人 UNION ,列的数量一致,类型一致,名称一致
SELECT deptno,NULL AS job, COUNT(1) FROM emp GROUP BY deptno UNION SELECT deptno,job,COUNT(1) FROM emp GROUP BY deptno,job
3.ROLLUP,CUBE ,GROUPING SET SELECT deptno,job,COUNT(1) FROM emp GROUP BY ROLLUP(deptno,job) ROLLUP(A,B,C)—从右往左依次分组 GROUP BY abc GROUP BY ab GROUP BY a
SELECT deptno,job,COUNT(1) FROM emp GROUP BY ROLLUP(job,(deptno,ename)) 带括号的分组,括号内为一组不可分割 job, (deptno,ename) job
各种组合分组 SELECT deptno,job,ename,COUNT(1) FROM emp GROUP BY CUBE(deptno,job,ename)
GROUPING SETS 按照单列汇总,没有总汇总 SELECT deptno,job,COUNT(1) FROM emp GROUP BY GROUPING SETS(deptno,job)
deptno job
MERGE 用一个结果集去更新另一张表 MERGE INTO 目标表 别名 USING (sql) 别名 ON(条件) WHEN MATCHED THEN UPDATE DELETE WHEN NOT MATCHED THEN INSERT 更新bonus表,如果bonus中没有该员工信息,则插入员工信息并给该员工添加奖金,奖金为工资的10%， 如果已有该员工信息，则将奖金更新为工资的10% MERGE INTO bonus b USING (SELECT * FROM emp) e ON(b.ename=e.ename) WHEN MATCHED THEN UPDATE SET b.comm=e.sal0.2 WHEN NOT MATCHED THEN INSERT (ename,job,sal,comm) VALUES(e.ename,e.job,e.sal,e.sal0.1); （WHERE e.sal>3000）;–只改工资高于3000的员工奖金
