运维开发网

聚合函数 和 分析函数的区别 ?子查询 ?关联查询?

运维开发网 https://www.qedev.com 2021-05-19 14:38 出处:51CTO 作者:mob604756fc093d
聚合函数 和 分析函数的区别 ?子查询 ?关联查询?,mob604756fc093d的博客原创的Java文章。

聚合函数 和 分析函数的区别 ?

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

0

精彩评论

暂无评论...
验证码 换一张
取 消