分组统计
SELECT 语句具有一个可选的GROUP BY 子句,配合统计函数,可以实现分组统计查询
SELECT deptno, SUM(sal) 部门薪资小计 FROM emp GROUP BY deptno;
在使用GROUP BY 子句时,除了作为分组函数参数的列不用包含在GROUP BY子句中之外,任何在SELECT 列表中的其他列都必须包括在组函数中。
分组结果通过分组列隐式排序,也可以用ORDER BY 指定不同的顺序排序,但只能用分组函数或分组列进行排序。
SELECT deptno, SUM (sal) 部门薪资小计 FROM emp
GROUP BY deptno
ORDER BY SUM(sal);
分组时,可以让出现在GROUP BY 中的列不一定出现在SELECT 选择列中
SELECT SUM(sal) 部门薪资小计, AVG(sal) 部门平均薪资 FROM emp
GROUP BY deptno
ORDER BY SUM (sal);
对单一字段进行分组通常不能完全满足分组需求,可以通过在GROUP BY 子句中使用多个分组字段进行多层分组
SELECT deptno, job, SUM(sal) 薪资小计 FROM emp
GROUP BY deptno, job;
注:层次分组按照在GROUP BY 子句中列出的列的顺序确定结果的默认排序顺序
在使用分组函数查询时,如果在一个SELECT 语句中使用了单独的列和分组函数,比如SUM或COUNT,必须要指定一个GROUP BY 子句来指定要分组的列,否则Oracle会跳出错误提示:非单组分组函数。
因为没有为SELECT 语句使用GROUP BY,数据库会认为这不是一个单组的分组,需要进行进一步的分组。在编写分组语句时,需要特别注意如下事项:
- 在SELECT 列表中的任何列或表达式(非统计函数计算列)必须在GROUP BY 子句中。
- 在GROUP BY子句中的列或表达式不必一定出现在SELECT 列表中
HAVING子句
如果要对分组的结果进行进一步的过滤,可以使用HAVING子句
SELECT deptno,job,SUM(sal) 薪资小计 FROM emp WHERE deptno IN(20,30)
GROUP BY deptno,job
HAVING SUM(sal) > 2000;
注:HAVING子句只能在GROUP BY 子句后,不能使用WHERE子句来取代HAVING子句,否则会产生异常
DUAL 表
DUAL表是Oracle系统中对所有用户可用的一个实际存在的表,这个表不能用来存储信息,在实际应用中仅用来执行SELECT 语句,可以使用DUAL表来查询系统信息
--查询时间
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-mm-dd hh24:mi:ss') FROM DUAL;
DUAL表,可以用来计算表达式、输出静态文本、计算函数结果等。这个表是Oracle特别创建的一个表,它本身不具有任何的其他意义,仅用来计算并返回结果
ROWNUM 伪列
在Oracle中,没有类似SQL Server中TOP关键字来获取表中的前几条记录,Oracle中提供了一个更加方便的方法:使用ROWNUM伪列。ROWNUM伪列是Oracle先查到结果集之后再加上去的一个伪列,这个伪列对复合条件的结果添加一个从1开始的序列号。
SELECT ROWNUM, empno, ename, job, mgr, hiredate FROM emp
WHERE deptno = 20;
--提取员工前10条记录
SELECT ROWNUM, empno,ename,job,mgr,hiredate FROM emp
WHERE ROWNUM <=10;
ROWNUM 与ROWID最大的不同在于ROWID是物理存在的,而ROWNUM 是动态的,先查到结果后再加上取得一个列,因此必须有结果集。如果编写条件查询ROWNUM>10,当结果集生成时,Oracle首先产生1条ROWNUM为1的记录,显然不匹配ROWNUM>10这个条件,该条记录被过滤掉后,后生成的ROWNUM依然为1,因此如果ROWNUM>10,将不会得到任何结果。因此要提取记录中间的记录,必须使用子查询
SELECT recno, empno, ename, job, mgr, hiredate FROM
(SELECT ROWNUM recno, empno, ename, job, mgr, hiredate FROM emp)
WHERE recno >= 5 AND recno <= 10;
ROWID伪列
ROWID 是一种数据类型,它使用基于64位编码的18个字符来唯一标识一条记录的物理位置的一个ID,有点类似与主键,不过与主键的本质区别是ROWID 一般情况下是按照递增的顺序来排序的
--查询emp中前5条记录的ROWID,通过ROWIDTOCHAR转换为字符串显示
SELECT ROWIDTOCHAR (ROWID), ename, empno, job, hiredate FROM emp
WHERE ROWNUM<= 5 ;
ROWID 由18个基于BASE64编码的字符串组成
SELECT ROWID, x.* FROM emp x;
ROWID的另一个重要的作用是删除完全重复的两条记录,先创建一个名为emo_rowid的新表
CREATE TABLE emp_rowid AS SELECT * FROM emp;
使用CREATE TABLE-AS 创建的表具有与emp完全相同的结构,但不包含主键约束等信息,因此可以向该列表中插入重复的列
INSERT INTO emp_rowid SELECT * FROM emp;
可以通过MIN或MAX来获取记录中ROWID较大的或较小的值来作为非重复的记录输出
SELECT * FROM emp_rowid WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM emp_rowid GROUP BY empno);
这条语句用了一个分组子查询,先取出empno分组的所有ROWID中较低的值,然后查询emp_rowid去除掉这些值,即可得到非重复值。
同样,通过使用DELETE 语句可以删除重复行,使用完全相同的WHERE子句
DELETE FROM emp_rowid WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM emp_rowid GROUP BY empno);