组函数&多列子查询

• 组函数
  组函数操作在一组行(记录)上,每组返回一个结果

函数	用途&可以使用种类
avg(distinct|all|n)	平均值 (number)
sum(distinct|all|n)	求和 (number)
count(distinct|all|expr|*)	计数 (number char date)
max(distinct|all|expr)	最大值 (number char date)
min(distinct|all|expr)	最小值 (number char date)

select sum(BASE_COST) dwfy,avg(BASE_COST) pjz,max(BASE_COST) zdz,min(BASE_COST) zxz,count(BASE_COST) geshu from COST;

默认情况下组函数处理所有的非空值.
当组函数要处理的所有值都为null,count函数返回0,其他函数返回null.

select avg(nvl(UNIT_COST,0)) from cost;

可以用nvl将null转换为0再求平均值.

select count(distinct  UNIX_HOST)  from SERVICE;
select count(all UNIX_HOST) from  SERVICE;

通常all可以省略不写,而distinct要用则要写

• group by子句

select UNIX_HOST,count(OS_USERNAME) from SERVICE group by UNIX_HOST;

有一个组函数count(OS_USERNAME)所以应该让列名UNIX_HOST变成组名.

select UNIX_HOST,count(OS_USERNAME),max(OS_USERNAME) from SERVICE where UNIX_HOST like '%192.168.0.26%' group by UNIX_HOST;

语法可能不通,但是一定要组函数和组名存在时不存在列名.

执行顺序
from ->where ->group by ->select ->order by

Having的应用场景

• 问题
  • 哪些unix服务器开通的os账号数即开户数多于2个
• 核心点
  • 根据unix_host列队servece表中的记录分组,队每组记录进行统计,根据统计结果对分组过滤,注意是对组过滤.
  • 解决方法
    • 使用having子句

having子句:语法
显示开户数多于2个的unix服务器上最早开通日期(开户数:开通的os账号数)

select UNIX_HOST ,min(CREATE_DATE) from SERVICE group by  UNIX_HOST having count(OS_USERNAME)>2;

执行顺序
from ->where ->group by ->having ->select ->order by

where和having子句的区别

• where子句过滤的是行(记录)
• having子句过滤的是分组(组标识 每组数据的聚合结果)
• Where子句后面可以跟任意列名,单行函数,不能跟组函数
• having子句只能包括group by后面的表达式和组函数
• where子句执行在前,having子句执行在后
• where和having子句都不能用列别名

单表查询
语法顺序
select from where group by having order by
执行顺序
from where group by having select order by

• 非关联子查询
  子查询就是在一条SQL语句中嵌入slelect语句

select OS_USERNAME,min(CREATE_DATE)  from SERVICE where CREATE_DATE=(select min(CREATE_DATE) from SERVICE) group by OS_USERNAME;

在where语句中可以加入(select min(CREATE_DATE)

子查询的执行过程

• 先执行子查询,子查询的返回结果作为主查询的条件,再执行主查询.
• 子查询只执行一遍
• 若子查询的返回结果为多个值,oracle会去掉重复值之后,再将结果返回主查询

多列子查询

select UNIX_HOST,OS_USERNAME,CREATE_DATE 
from SERVICE where(UNIX_HOST,CREATE_DATE) in(select UNIX_HOST,min(CREATE_DATE) 
from  SERVICE group by UNIX_HOST ) 
group by OS_USERNAME,CREATE_DATE,UNIX_HOST;

关联子查询的应用场景

• 问题
  -哪些os账号的开通天数比同一台unix服务器上的平均开通天数长.
• 核心点
  -该问题的难点在于同一天unix服务器的平均开通天数,如何表达同一台unix服务器是解决该问题的核心
• 解决方法
  -使用关联子查询

关联子查询

• 关联子查询的语法格式

子查询
非关联子查询 子查询不会引用主表中的列
关联子查询 字表一定会引用主表中的列
语句执行顺序不同

关联子查询

select UNIX_HOST,OS_USERNAME,CREATE_DATE
from SERVICE o
where trunc(Sysdate-CREATE_DATE)>
(select trunc(avg(sysdate-CREATE_DATE)
    )from  SERVICE i where i.UNIX_HOST=o.UNIX_HOST
    )
group by UNIX_HOST, OS_USERNAME, CREATE_DATE;

select REAL_NAME
from  ACCOUNT o
where exists
(select 1 from ACCOUNT i
    where o.id=i.RECOMMENDER_ID);

boolean exists 返回true或者false
当o.id=i.RECOMMENDER_ID有返回结果时,1(随便什么东西都可以代替1,别报错:)就可以作为返回值
exists就会返回true.

子查询
非关联(单列,多列)
in(not in) >all
关联(在子查询的包含两个表的列写成的条件表达式引)
exists (not exists)

in和exists的比较

• exists是用循环的方法,有outer表的记录数决定循环的次数,对于exists影响最大,所以,主表的记录数要少
• in先执行子查询,子查询的返回结果去重之后,再执行主查询,所以,子查询的返回结果越少,越适合用该方法