oracle高级查询总结

不念不忘少年蓝@ 2022-08-19 05:56 191阅读 0赞

oracle与我们常用的sqlserver、mysql相比，有非常多的优势，它可以支持多个实例同时运行，功能非常强大,主要在传统行业的数据化业务中，比如：银行、金融这样的对可用性、健壮性、安全性、实时性要求极高的业务；零售、物流这样对海量数据存储分析要求很高的业务。此外，高新制造业如芯片厂也基本都离不开Oracle；电商也有很多使用者，如京东（正在投奔Oracle）、阿里巴巴（计划去Oracle化）。而且由于Oracle对复杂计算、统计分析的强大支持，在互联网数据分析、数据挖掘方面的应用也越来越多。

最近一段时间看了一些关于oracle的高级查询资料，主要是在分组函数、连接和子查询方面的一些用法，下面用一张图来概述一下：

![Center][]

#      一、分组函数 #

##        什么是？ ##

它是作用于一组函数，并对一组数据返回一个值。

##        有哪些？ ##

常用的分组函数比如AVG(平均值)、SUM(总和)、MIN（最小值）、MAX（最大值）、COUNT（数量）、WM\_CONCAT(行转列)

##         用一用？ ##

1、求最大值和平均值

SELECT "AVG"(BID_AMOUNT),"MAX"(BID_AMOUNT) FROM T_LOAN_BID

2、行转列

SELECT USER_ID,WM_CONCAT(PRO_ID) FROM T_LOAN_BID GROUP BY USER_ID

![Center 1][]

3、count使用要注意

count会**自动忽略空值**，所以为了避免空值，可以使用NVL函数使分组函数无法忽略空值

如果不加nvl，用count()直接查询：

SELECT "COUNT"(UNBINDBANK_STATUS) FROM T_CUST_BANK

上面得到的结果是31，因为去掉了null值，而如果加上nvl

SELECT "COUNT"("NVL"(UNBINDBANK_STATUS,0)) FROM T_CUST_BANK

上面得到结果为76条

4、group by子句

这个句子主要需要注意当我们使用时，在select 后查询哪些字段，在group by中就要写上哪些字段，除非把这些函数放到分组函数中

SELECT user_id , bid_status  ,"MAX"(BID_AMOUNT) FROM T_LOAN_BID GROUP BY user_id,bid_status

如果写成：

SELECT user_id , bid_status  ,"MAX"(BID_AMOUNT) FROM T_LOAN_BID GROUP BY user_id

就会报错：\[Err\] ORA-00979: 不是 GROUP BY 表达式  
            5、having子句

效率？

having一般情况下与where子句是一样的，但是如果从效率方面考虑，我们就会优先选择where，因为where是先过滤，再分组，而having是先分组再过滤，比如我们现在要查出所有的女士，如果用where，就会先找出所有的女士，如果是having则会先将男士、女士分开，再查出女士，很显然，**where的效率要比having快很多**。

where和having的区别？

**不能在where中使用分组函数**，但havging中却可以！

#      二、连接 #

##         1、等值连接 ##

等值连接我们经常使用：

select user.id,cust.name  from t_user user ,t_customer  cust whre user.id=cust.userid

##         2、不等值连接 ##

最经常用到的就是取值范围，比如：between and,< =等

##         3、外连接 ##

外连接分为左外和右外

**  左外即不管等号左边的值始终查出来显示，右外即等号右边的值始终查出来**

下面表示右外连接

select user.id,cust.name  from t_user user ,t_customer  cust whre user.id(+)=cust.userid

下面表示左外连接

select user.id,cust.name  from t_user user ,t_customer  cust whre user.id=cust.userid(+)

我们可以看出，这两个加号与我们想象中的位置是恰恰相反的。

##         4、自连接 ##

我们经常遇到一些这样的需求，比如树状结构，这种情况下我们可以通过别名来进行区别，别名就像是我们的小名。

#      三、子查询 #

子查询，也可以认为是嵌套查询，通过层层筛选，得到想要的数据，当一个问题不能一步求解时，我们可以用子查询来解决。

SELECT
    					t3.USER_ID,
    					T4.USER_NAME,
    					t3.INVITE_USER_ID,
    					t3.BID_AMOUNT,
    					T3.operator_,
    					t3.PHONECER,
    					T3.CERTIFIED_STATUS,
    					T3.STATUS,T3."ID"
    						FROM
    						(
    							SELECT
    							t1.USER_ID,
    							t1.INVITE_USER_ID,
    							t1.OPERATOR_,
    							t2.TOTAL_BID_AMOUNT AS BID_AMOUNT,
    							t1."ID",
    							TAP.CERTIFIED_STATUS AS PHONECER,
    							TARN.CERTIFIED_STATUS,
    							T1.STATUS
    							FROM
    							T_CUST_USER_INVITE t1
    							LEFT JOIN T_CAP_USER_ACCOUNT t2 ON t1.INVITE_USER_ID = t2.USER_ID
    							LEFT JOIN T_AUTH_PHONE TAP ON TAP.USER_ID=t1.INVITE_USER_ID
    							LEFT JOIN (SELECT * FROM T_AUTH_REAL_NAME WHERE CERTIFIED_STATUS=1) TARN ON TARN.USER_ID=t1.INVITE_USER_ID
    						) t3
    					LEFT JOIN T_CUST_USER t4 ON t3.USER_ID = t4.ID

注意！！

1、子查询中的小括号不能丢

2、子查询书写要整洁，方便阅读

3、子查询使用位置：where from select having ,注：group by不可以使用子查询

4、主查询和子查询可以不是同一张表

5、子查询中不需要使用排序，即使排序了也会在它的上一层被忽略掉

[Center]: /images/20220731/7f40ea5ce79d4753ac88f15bc5721140.png
[Center 1]: /images/20220731/273bd6253afc413f8323c4d2c3d538de.png