递归查询+随机抽取数据+grouping rollup cube

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的员工奖金