Oracle--3--组函数，子查询

Rizzo_yuan

于 2019-10-26 14:35:43 发布

阅读量191

点赞数

分类专栏： Oracle 文章标签： oracle组函数 oracle子查询

本文链接：https://blog.csdn.net/yz_blanks/article/details/102756376

版权

Oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文详细介绍了Oracle数据库中的组函数，包括它们如何与GROUP BY子句配合使用，以及HAVING子句在分组后的条件筛选中的作用。此外，还探讨了子查询的概念，解释了子查询如何作为判断条件嵌套在SQL语句中，以及在不同情况下的应用和注意事项。

摘要由CSDN通过智能技术生成

一、Lesson5 Group Function（组函数）

A：组函数：是指将数据按照某列的值进行分组后，然后使用组函数分别对每个分好的小组中的数据进行处理。
- 组函数一般要结合着分组关键字group来使用
- 组函数：多个列（字段）得到一个返回结果
- 分组：对多个字段得到多个结果进行分组（统计）
  - 分组会产生笛卡尔积，所以要进行having（条件筛选）

B：sql语言的各个部分组成：

select … from … where … group by … having … order by（一定在最后面） …
select … from是必须的

执行顺序：from–>select–>where–>group by–>分组函数–>having–>order by

（组函数出现的位置）：
1. select后面：组函数没有作用的字段，需要添加到group by中；
2. where后面：不可添加组函数作用的字段；
3. having后面：组函数没有作用的字段，需要添加到group by中；
4. order by后面：可以出现组函数
where和having的对比
1. 两者都是做条件筛选的；
2. where执行比having早；
3. where后面不可以出现组函数；having后面可以出现组函数；
4. where紧跟在from后面；having紧跟在group by后面；
group by和having关系
1. 相似于where和from的关系；
2. group by可以单独存在；
3. having必须跟在group by后面，不能单独出现

1、avg(字段)：计算指定字段的字段值的平均值
    select avg(salary)
    from s_emp;所有员工的平均工资
2、count(字段)：对指定字段进行计数
    select count(*)
    from s_emp;
3、max(字段)：返回指定字段的最大字段值
    select max(salary)
    from s_emp;
4、min(字段)：返回指定字段的最小字段值
    select min(salary)
    from s_emp;
5、sum(字段);返回指定字段的总和
    select sum(salary)
    from s_emp;
   

6、group by：分组
	61、通过dept_id进行分组，得到每组的salary平均值
    select dept_id,avg(salary)
    from s_emp
    group by dept_id;


	62、返回dept_id中salary最大的值
    select dept_id,max(salary)
    from s_emp
    group by dept_id;


	63、返回dept_id中salary的总和
    select dept_id,sum(salary)
    from s_emp
    group by dept_id;


	64、返回dept_id的中id个数
    select dept_id,count(id)
    from s_emp
    group by dept_id;


	65、多个组函数按照dept_id进行分组
    select dept_id,count(*),sum(salary),min(salary),max(salary),avg(salary)
    from s_emp
    group by dept_id;


	66、多个组函数返回结果按照dept_id进行分组，通过每个字段升序排序
    select dept_id,count(*),sum(salary),min(salary),max(salary),avg(salary)
    from s_emp
    group by dept_id
    order by 1;


7、having：筛选后跟筛选条件
	71、返回部门工资平均值>1400的部门的id和部门工资平均值，按照平均值进行排序
    select dept_id,avg(salary)
    from s_emp
    group by dept_id
    having avg(salary)>1400
    order by avg(salary);

	select dept_id,avg(salary) avgSal
    from s_emp
    where avgSal>1400
    group by dept_id
    order by avg(salary);

	72、返回部门工资总和>4000的部门id和部门工资总和
    select dept_id,sum(salary)
    from s_emp
    group by dept_id
    having sum(salary)>4000;


8、多表查询进行分组筛选：
	81、返回部门工资平均值>1400的部门id和部门名（等值连接，s_dept表获取），部门工资平均值，按照部门id进行排序
    select se.dept_id,sd.name,avg(salary)
    from s_emp se,s_dept sd
    where se.dept_id=sd.id
    group by se.dept_id,sd.name
    having avg(salary)>=1400
    order by se.dept_id;


	82、在having后面 等值连接
	缺点：效率比较低
    select se.dept_id,sd.name,avg(salary)
    from s_emp se,s_dept sd
    group by se.dept_id,sd.name,sd.id
    having avg(salary)>=1400 and se.dept_id=sd.id
    order by se.dept_id;

复杂查询思路：从最一开始的基础需求逐步添加,一张表查完就在添加新表进行查

9、查询s_emp表中最大的工资数,并且显示出这个最大工资的员工的名字
	91、第一步：查询出员工表中，工资最高
    select max(salary)
    from s_emp;


	92、第二步：查询每个员工的最高工资（last_name要添加到分组，不是组函数作用的字段）
    select last_name,max(salary)
    from s_emp
    group by last_name;


	93、第三步：进行筛选，选择出工资最高的员工与员工工资相等的员工（自连接：通过第二张表查找员工工资）；
	即选择出部门中最高工资的员工
    select s2.last_name,max(s1.salary)
    from s_emp s1,s_emp s2
    group by s2.last_name,s2.salary
    having max(s1.salary)=s2.salary;

10、查询s_emp表每个部门的最大工资数,并且显示出这个最大工资的员工名字以及该部门的名字和该部门所属区域,并且使用部门编号进行排序
	
	
	101、每个部门最大工资的员工的部门id，姓名，最高工资
	筛选：两张表s1,s2的多个部门的员工的dept_id相同，部门中salary最高的员工要和部门的员工salary相同
    select s1.dept_id,s2.last_name,max(s1.salary)
    from s_emp s1,s_emp s2
    group by s1.dept_id,s2.last_name,s2.salary,s2.dept_id
    having s2.salary=max(s1.salary)
    and s1.dept_id=s2.dept_id
    order by s1.dept_id;


	102、where先进行筛选（没组函数作用的字段），层次结构明显效率高
    select s1.dept_id,s2.last_name,max(s1.salary)
    from s_emp s1,s_emp s2
    where s1.dept_id=s2.dept_id
    group by s1.dept_id,s2.last_name,s2.salary
    having s2.salary=max(s1.salary)
    order by s1.dept_id;

	103、显示出这个最大工资的员工名字,显示部门名称、部门所在地区
	再关联s_dept部门表（保证dept_id和id相等），s_region区域表（region_id和id想等）
	
    select s1.dept_id,s2.last_name,sd.name,sr.name,max(s1.salary)
    from s_emp s1,s_emp s2,s_dept sd,s_region sr
    where s1.dept_id=s2.dept_id
        and s1.dept_id = sd.id
    and sd.region_id = sr.id
    group by s1.dept_id,s2.last_name,s2.salary,sd.name,sr.name
    having s2.salary=max(s1.salary)
    order by s1.dept_id;

Lesson6 Subquerie（子查询）

A：子查询：一个select语句中嵌套了另一个select语句，用另外一个查询结果当做了另一个查询的判断条件

B：子select语句中:

返回多个字段，结果不唯一：子select语句可以当做一个表；
- () t：虚表，使子select语句作为一个新表（临时表）
返回结果唯一：可以当做where和having筛选条件；

子select语句中的组函数，在外层select语句中是没有组函数总用的字段，需要添加到group by中

1、查询工资比Smith工资高的员工信息
	11、第一步：查询simth的工资为940
    select salary
    from s_emp
    where last_name = 'Smith';

	12、工资大于940的员工姓名，员工工资
    select last_name,salary
    from s_emp
    where salary>940;

	13、第三部：940为中间结果，所以第一个select语句作为第二步的select语句的子查询
    select last_name,salary
    from s_emp
    where salary>(
        select salary
        from s_emp
        where last_name = 'Smith'
    );

2、查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
	21、第一步：每个部门的平均工资
    select dept_id,avg(salary)
    from s_emp
    group by dept_id;

	22、第二步：41号部门的平均工资
    select avg(salary)
    from s_emp
    where dept_id = 41
    group by dept_id;

	23、第三步：部门平均工资>1247.5的部门
    select dept_id,
    from s_emp
    group by dept_id
    having avg(salary)>1247.5;

	24、第四部：部门平均工资>1247.5的部门的员工姓名，员工工资
    select last_name,salary
    from s_emp
    where dept_id in(10,31,32,33,35,50);

	25、替换中间结果：查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
    select last_name,salary
    from s_emp
    where dept_id in(
        select dept_id
        from s_emp
        group by dept_id
        having avg(salary)
        >(
            select avg(salary)
            from s_emp
            where dept_id = 41
            )
    );

3、查询平均工资比 41号部门的平均工资 高 的部门中员工的信息,并且显示出当前部门的平均工资
	31、第一步：41号部门的平均工资
    select avg(salary)
    from s_emp
    where dept_id = 41;

	32、第二步：大于41号部门平均工资的部门id
    select dept_id
    from s_emp
    group by dept_id
    having avg(salary)>1247.5
    order by dept_id;

	33、第三部：子表（添加新的查询字段avg(salary)）,关联部门s_dept表（部门名sd.name）,替换中间表的查询结果，表与表之间建立连接（等值连接），否则会出现重复(笛卡尔积)
    select se.last_name,se.salary,t.avgSal,sd.name
    from s_emp se,(
        select dept_id,avg(salary) avgSal
        from s_emp
        group by dept_id
    ) t,s_dept sd
    where se.dept_id in(
            select dept_id
            from s_emp
            group by dept_id
            having avg(salary)>(
                    select avg(salary)
                    from s_emp
                    where dept_id = 41
            )
        )
        and se.dept_id = t.dept_id
        and sd.id = se.dept_id;

4、查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时显示部门的名称以及所在地区
	41、第一步：查询每个部门的工资平均值
    select avg(salary)
    from s_emp
    group by dept_id;

	42、第二步：查询比所在部门工资平均值还要高的员工last_name,员工的工资，员工部门，员工部门平均工资
    select se.last_name,se.salary,se.dept_id,t.avgSal
    from s_emp se,(
        select dept_id,avg(salary) avgSal
        from s_emp
        group by dept_id
        ) t
    where se.dept_id = t.dept_id
        and se.salary > t.avgSal
    group by se.last_name,se.salary,se.dept_id,t.avgSal
    order by se.dept_id;

	43、第三步：比第二步多查询员工所在部门名字
    select se.last_name,se.salary,se.dept_id,sd.name,t.avgSal
    from s_emp se,(
        select dept_id,avg(salary) avgSal
        from s_emp
        group by dept_id
        ) t,s_dept sd
    where se.dept_id = t.dept_id
        and se.salary >= t.avgSal
        and se.dept_id=sd.id
    group by se.last_name,se.salary,se.dept_id,sd.id,sd.name,t.avgSal
    order by se.dept_id;

	44、第四步：比第三部多查询部门所在区域
	select se.last_name,se.salary,se.dept_id,sd.name,sr.name,t.avgSal
    from s_emp se,(
        select dept_id,avg(salary) avgSal
        from s_emp
        group by dept_id
        ) t,s_dept sd,s_region sr
    where se.dept_id = t.dept_id
        and se.salary >= t.avgSal
        and se.dept_id=sd.id
        and sd.region_id = sr.id
    group by se.last_name,se.salary,se.dept_id,sd.id,sd.name,sr.name,t.avgSal
    order by se.dept_id;
    
5、查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所在地区

	51、第一步：Ngao所在部门
    select dept_id
    from s_emp
    where last_name='Ngao';

	52、第二步：Ngao所在41部门的平均工资
    select avg(salary)
    from s_emp
    where dept_id=41;

	53、第三步：替换第二步中间值
	嵌套1
    select avg(salary)
    from s_emp
    where dept_id=(
        select dept_id
        from s_emp
        where last_name='Ngao';
    );

	54、第四步：查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要 比Ngao所在部门的平均工资要高
    select se.dept_id,se.last_name,se.salary
    from s_emp se,(
        select dept_id,avg(salary) avgSal
        from s_emp
        group by dept_id
    ) t
    where se.dept_id = t.dept_id
        and se.salary> 1247.5
        and t.avgSal > 1247.5
    order by se.dept_id;

	55、第五步：替换中间结果，并且多添加部门的平均值
	嵌套2
    select se.dept_id,se.last_name,se.salary,t.avgSal
    from s_emp se,(
        select dept_id,avg(salary) avgSal
        from s_emp
        group by dept_id
    ) t
    where se.dept_id = t.dept_id
        and se.salary> (
            select avg(salary)
            from s_emp
            where dept_id=(
                select dept_id
                from s_emp
                where last_name='Ngao'
            )
        )
        and t.avgSal > (
            select avg(salary)
            from s_emp
            where dept_id=(
                select dept_id
                from s_emp
                where last_name='Ngao'
            )
        )
    order by se.dept_id;

	56、第六步：再添加显示部门名字 区域 部门人数
    select se.dept_id,se.last_name,se.salary,sd.name,sr.name,t.num
    from s_emp se,(
        select dept_id,avg(salary) avgSal,count(*) num
        from s_emp
        group by dept_id
    ) t,s_dept sd,s_region sr
 where se.dept_id = t.dept_id
        and se.salary> (
            select avg(salary)
            from s_emp
            where dept_id=(
                select dept_id
                from s_emp
                where last_name='Ngao'
            )
        )
        and t.avgSal > (
            select avg(salary)
            from s_emp
            where dept_id=(
                select dept_id
                from s_emp
                where last_name='Ngao'
            )
        )
        and se.dept_id = sd.id
        and sd.region_id = sr.id
    order by se.dept_id;