当前位置:Gxlcms > 数据库问题 > 04.SQL基础-->分组与分组函数

04.SQL基础-->分组与分组函数

时间:2021-07-01 10:21:17 帮助过:22人阅读

一、分组:

分组函数可以对行集进行操作,并且为每组给出一个结果。
使用group by column1,column2,..按columm1,column2进行分组,即column1,column2组合相同的值为一个组

 

二、常用分组函数:

AVG([DISTINCT|ALL]n) -- 求平均值,忽略空值
COUNT({*|[DISTINCT|ALL]expr}) -- 统计个数,其中expr用来判定非空值(使用*计算所有选定行,包括重复行和带有空值的行)
MAX([DISTINCT|ALL]expr) -- 求最大值,忽略空值
MIN([DISTINCT|ALL]expr) -- 求最小值,忽略空值
SUM([DISTINCT|ALL]n) -- 求和,忽略空值

三、分组函数语法:*/

SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];  

/*

四、分组函数使用准则:

DISTINCT  使函数只考虑非重复值,ALL则考虑包括重复值在内的所有值。默认为ALL.
         带有expr参数的函数的数据类型可以为CHAR,VARCHAR2,NUMBER,DATE.
所有分组函数都忽略空值。可以使用NVL,NVL2,或COALESCE函数代替空值
使用GROUP BY 时,Oralce服务器隐式地按照升序对结果集进行排序。可以使用ORDER BY 更改排序结果。 

可以使用NVL 函数强制分组函数包含空值,如:
select avg(nvl(comm,0)) from emp;

  

五、GROUP BY 子句的语法:

使用GROUP BY 子句可以将表中的行分成更小的组,然后使用分组函数返回每一组的汇总信息
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
GROUP BY --group_by_expression 即为对哪些列进行分组

 

六、GROUP BY 使用准则:

SELECT 中出现的列,如果未出现在分组函数中,则GROUP BY子句必须包含这些列
WHERE 子句可以某些行在分组之前排除在外
不能在GROUP BY 中使用列别名
默认情况下GROUP BY列表中的列按升序排列
GROUP BY 的列可以不出现在分组中   

七、分组过滤:

使用having子句
having使用的情况: 行已经被分组 使用了组函数 满足having子句中条件的分组将被显示

 

八、演示:*/

--为数字数据使用AVG和SUM方法

select min(sal) as min_sal,max(sal) as max_sal,
avg(sal) as avg_sal,sum(sal) as sum_sal
from scott.emp;

idle> select min(sal) as min_sal,max(sal) as max_sal,
avg(sal) as avg_sal,sum(sal) as sum_sal
from scott.emp;
  2    3  
   MIN_SAL    MAX_SAL	 AVG_SAL    SUM_SAL
---------- ---------- ---------- ----------
       800	 5000 2073.21429      29025

 

--对于数字,字符和日期数据类型,你能使用MIN和MAX方法

 select min(hiredate) as min_hiredate,max(hiredate) as max_hiredate from scott.emp; 

idle> select min(hiredate) as min_hiredate,max(hiredate) as max_hiredate from scott.emp;

MIN_HIREDA MAX_HIREDA
---------- ----------
1980-12-17 1987-05-23

 

--使用count(*),count(expr),count(distinct expr)

--注意coung(*)包含空值、重复值,count(expr)过滤空值,count(distinct expr)即过滤空值,也过滤重复值

SQL> select count(*),count(mgr),count(distinct mgr) from emp;

idle>  select count(*),count(mgr),count(distinct mgr) from emp;

  COUNT(*) COUNT(MGR) COUNT(DISTINCTMGR)
---------- ---------- ------------------
	14	   13		       6

 

--使用NVL 函数强制分组函数包含空值

SQL>

idle>  select avg(comm) ,avg(nvl(comm,0)) from emp;

 AVG(COMM) AVG(NVL(COMM,0))
---------- ----------------
       550	 157.142857

 

--使用group by 子句来分组

SQL> select job ,avg(sal) from emp group by job;

 

idle> select job ,avg(sal) from emp group by job;

JOB	    AVG(SAL)
--------- ----------
CLERK	      1037.5
SALESMAN	1400
PRESIDENT	5000
MANAGER   2758.33333
ANALYST 	3000

 

--GROUP BY 的列可以不出现在分组中

SQL> select avg(sal) from emp group by job order by avg(sal) desc;

idle> select avg(sal) from emp group by job order by avg(sal) desc;

  AVG(SAL)
----------
      5000
      3000
2758.33333
      1400
    1037.5

  

 

--错误的用法,SELECT 中的有些列没有在GROUP BY子句中出现

SQL> select job,avg(sal) from emp;

select job,avg(sal) from emp

*

ERROR at line 1:

ORA-00937: not a single-group group function

 

--使用having子句过滤分组结果

--查询平均工资高于的部门号,及其平均工资。

 

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

 

idle> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

    DEPTNO   AVG(SAL)
---------- ----------
	20	 2175
	10 2916.66667

  

--查出平均工资在以上的工种(job)

select job,avg(sal) from emp group by job having avg(sal)>2000;

 

idle> select job,avg(sal) from emp group by job having avg(sal)>2000;

JOB	    AVG(SAL)
--------- ----------
PRESIDENT	5000
MANAGER   2758.33333
ANALYST 	3000

  

--求人数在人以上的部门

select deptno,count(*) from emp group by deptno having count(*)>5;

 

idle> select deptno,count(*) from emp group by deptno having count(*)>5;

    DEPTNO   COUNT(*)
---------- ----------
	30	    6

  

--使用分组函数的嵌套

SQL> select max(avg(sal)) from emp group by deptno;

 

idle> select max(avg(sal)) from emp group by deptno;

MAX(AVG(SAL))
-------------
   2916.66667

 

04.SQL基础-->分组与分组函数

标签:esc   oal   参数   最小   ide   演示   resid   alc   lin   

人气教程排行