勿传网上！严禁谋利！ Oracle学习笔记

常彦博

七、SQL语句中的分支

7.1分支表达式

1）case when (then)，用于解决不同记录需要不同处理方式的问题。when后面跟条件表达式，当所有when条件都不满足时，若有else，表达式的返回结果为其后的值，否则返回null值。

2）寻找when的优先级：从上到下再多的when，也只有一个出口，即其中有一个满足了表达式expr就马上退出case。

3）else expr和return expr的数据类型必须相同。 eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用CASE WHEN实现） select base_duration,unit_cost,case when base_duration=20 then unit_cost+0.05 when base_duration=40 then unit_cost+0.03 else unit_cost end new_nuit_cost from cost; 7.2分支函数 decode，是简版的case when。 1）decode(value,if1,then1,if2,then2,??,else)标识如果value等于if1时，返回then1。如果不等于任何一个if值，则返回else。 eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用decode实现） select base_duration,unit_cost,decode(base_duration,20,unit_cost+0.05, 40,unit_cost+0.03, unit_cost) n_base_cost from cost; 15

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

八、组函数

操作在一组行（记录）上，每组返回一个结果。

8.1报表统计常用

1）avg(distinct|all|n)：平均值，参数类型只能为number。 2）sum(distinct|all|n)：求和，参数类型只能为number。

3）count(distinct|all|expr|*)：计数，参数类型为number、字符、date。 4）max(distinct|all|expr)：最大值，参数类型为number、字符、date。 5）min(distinct|all|expr)：最小值 ，参数类型为number、字符、date。 ? 注意事项： ? distinct去重复时，会保留一个。 select count(distinct base_duration)from cost;//4，distinct保留一个空，但count统计时不算 ? count（*）不管null，统计“记录”数。 ? count（列名）返回的是列中非null值的数量。 8.2缺省情况组函数处理什么值 所有的非空值。 8.3当组函数要处理的所有值都为null时 count函数返回0，其他函数返回null。 8.4行级信息和组级信息 返回的结果集包含多条记录，是行级信息；返回的结果集包含一条记录，是统计汇总信息，是组级别的信息；两者不能同时显示出来！ 处理方式：将行级信息变成组标识或进行组函数处理。 eg1：单位费用的总和、平均值、最大值、最小值个数 select sum(unit_cost) sum1,avg(unit_cost) avg1,max(unit_cost) max1, min(unit_cost) min1,count(unit_cost) cnt from cost; eg2：若null值参与运算，必须将null值转换成非null值 select avg(nvl(unit_cost,0)),sum(unit_cost)/count(*) from cost; eg3：若unit_cost列中参与运算的数据都为null，avg(unit_cost)的函数值为null，count(unit_cost)的函数值为0。 select avg(unit_cost),count(unit_cost) from cost where unit_cost is null; eg4：每台unix服务器上开通的os帐号数即开户数？ select unix_host,count(os_username) from service group by unix_host; eg5：tarena26（192.168.0.26）上开通的os帐号数即开户数？ select max(unix_host),count(os_username) cnt from service where unix_host = '192.168.0.26'; 用min(unix_host)也可 16

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

九、group by子句

将表中的记录进行分组

9.1语法和执行顺序

语法顺序：select from where group by order by 执行顺序：from where group by select order by

9.2分组过程

根据group by子句指定的表达式，将要处理的数据分成若干组（若有where子句即为通过条件过滤后的数据）。每组有唯一的组标识，组内有若干条记录，根据select后面的组函数对每组的记录进行计算，每组对应一个返回值。 9.3常见错误 若没有group by子句，select后面有一个是组函数，则其他都必须是组函数（记录（行）信息和组信息不能放一起，要么都是组函数，要么都是单行函数）。 若有group by子句，select后面跟group by后面跟的表达式以及组函数，其他会报错。 9.4多列分组 包含多列用“，”分开，分组的个数多了，每组的记录少了。 eg：根据unix服务器ip地址、开通时间统计开通的os帐号数即开户数 select unix_host,to_char(create_date,'yyyymmdd') create_date,count(os_username) cnt from service group by unix_host,to_char(create_date,'yyyymmdd'); 17

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十、having子句

对分组过滤。

10.1语法和执行顺序

语法顺序：select from where group by having order by 执行顺序：from where group by having select order by

10.2执行过程

行被分组，将having子句的条件应用在每个分组上，只有符合having条件的组被保留，再应用select后面的组函数对每组的数据进行处理。 10.3 where和having区别 1）where：过滤的是行（记录），后面可跟任意列名，单行函数，不能跟组函数（无法对应到具体记录），先执行，不允许用列别名。 2）having：过滤的是分组（组标识、每组数据的聚合结果），后面只能包含group by后面的表达式和组函数（能表达组信息的），后执行，不允许用列别名。 eg1：哪些unix服务器开通的os帐号数即开户数多于2个 select unix_host,count(os_username) cnt from service group by unix_host having count(os_username) > 2; eg2：哪些unix服务器在哪几天的开户数多于1个 select unix_host,to_char(create_date,'yyyymmdd') create_date,count(os_username) cnt from service group by unix_host,to_char(create_date,'yyyymmdd') having count(os_username) > 1; 18