​

聚合函数 和 分析函数的区别 ？
1、聚合函数是汇总计算返回一个计算结果 ，而分析函数会根据表的行数 每行返回一个计算结果 。
聚合函数集合分组 GROUP BY 使用时，每组返回一个计算结果 。
2、聚合函数使用的时候有很多语法限制，分析函数几乎没有 。
3、聚合函数分组使用 GROUP BY ，会对分组字段去重；
而分析函数分组使用 PARTITION BY ，PARTITION BY 没有去重的功能。
4、聚合函数可以直接放在SQL中当条件使用，比如在 HAVING 后面可以直接用 聚合函数当条件 ；
分析函数不可以直接当条件，必须对分析函数的查询结果 使用子查询套一下 再进行过滤， 否则会报语法错误。
5、ORDER BY 在聚合函数中是对查询结果排序 ，而在分析函数中有2个作用 排序 和 累计计算 。
6、在使用分组 聚合后 ORDER BY 后面只能跟 分组字段 或者 聚合函数 。
而分析函数中 没有这种限制 。

子查询
是把一个 SQL查询子句 放在另外一个 SQL语句中执行这种写法叫做 子查询。
子查询的用法：
1、可以当条件
-- 子查询当条件 返回单行 用 = 过滤
查出跟 SCOTT 在同一个 部门的其余员工
SELECT *
FROM EMP E
WHERE E.DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT') ;

-- 子查询当条件 返回多行 用 IN 过滤

查出所有员工的领导信息
SELECT *
FROM EMP E
WHERE E.EMPNO IN (SELECT MGR FROM EMP) ;

-- 子查询返回 多行 多列 用 IN 过滤
查出跟SCOTT从事相同工作 并且 工资也相同的员工
SELECT
FROM EMP E
WHERE E.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SCOTT')
AND E.SAL = (SELECT SAL FROM EMP WHERE ENAME = 'SCOTT')
-- 将上面的结果 转换成如下形式
SELECT
FROM EMP E
WHERE (E.JOB ,E.SAL) IN (SELECT JOB ,SAL FROM EMP WHERE ENAME = 'SCOTT')

2、子查询可以当表
-- 子查询当表
查出 各部门的 员工信息 和 部门的平均工资
SELECT E.* ,F.AVG_SAL
FROM EMP E , (SELECT DEPTNO ,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO ) F
WHERE E.DEPTNO = F.DEPTNO ;

查出工资高于员工所在部门平均工资的员工的信息
SELECT E.* ,F.AVG_SAL
FROM EMP E , (SELECT DEPTNO ,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO ) F
WHERE E.DEPTNO = F.DEPTNO
AND E.SAL > F.AVG_SAL;

SELECT
FROM (
SELECT E. ,AVG(E.SAL)OVER(PARTITION BY E.DEPTNO) AVG_SAL
FROM EMP E )
WHERE SAL > AVG_SAL;

3、子查询可以当结果
-- 子查询当结果
查出 各部门的 员工信息 和 部门的平均工资
SELECT E.*, (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO ) AVG_SAL
FROM EMP E ;

SELECT E.* ,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO ) AVG_SAL
FROM EMP E
WHERE E.SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO )

/ SELECT
FROM ( SELECT E. ,
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO ) AVG_SAL ,
AVG(E.SAL)OVER(PARTITION BY E.DEPTNO) AVG_SAL1
FROM EMP E)
WHERE SAL > AVG_SAL
/

关联查询的不同写法

内关联
SQL写法
SELECT
FROM EMP E
/INNER/ JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE ;
内关联
ORACLE写法
SELECT
FROM EMP E , DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND ;

外关联
SQL写法 左外 LEFT OUTER JOIN 右外 RIGHT OUTER JOIN
SELECT
FROM EMP E
LEFT /OUTER*/ JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE ;

ORACLE写法
SELECT *
FROM EMP E , DEPT D
WHERE E.DEPTNO = D.DEPTNO(+)
AND ;

全外关联
的SQL写法 和 ORACEL写法相同
SELECT
FROM EMP E
FULL /OUTER*/ JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE ;

-------------------------- SQL写法中的笛卡尔积 和 oracle的笛卡尔积 --------------------------
oracle写法
SELECT *
FROM EMP E, DEPT D ;

sql写法
SELECT *
FROM EMP E
JOIN DEPT D
ON 1 = 1 ;

END