Oracle中的join语法、子查询及行转列

最新推荐文章于 2022-12-07 23:49:05 发布

Bonjour_Yvonne

最新推荐文章于 2022-12-07 23:49:05 发布

阅读量638

点赞数

分类专栏： Oracle 数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/Bonjour_h/article/details/113355456

版权

数据库同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

Oracle

6 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL中的各种连接类型，包括交叉连接、自然连接、左连接、右连接、全连接及内连接，并通过实例展示了它们的用法。此外，还探讨了子查询的应用，包括单行和多行子查询，以及如何在查询中嵌套子查询来实现复杂的数据筛选。同时，文章通过案例展示了如何利用 decode 和 case when 进行情感分析数据的转换和聚合。

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

join语法

--cross join 等同于92语法中的笛卡尔积
select * from emp cross join dept;
--natural join 相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接,当两张表中不具有相同的列的时候，会进行笛卡尔积操作
--自然连接和92语法中国的自连接没有任何关系
select * from emp e natural join dept d;
--on子句，可以添加任意的连接条件
--相当于92语法中的等值连接
select * from emp e join dept d on e.deptno = d.deptno;
--相当于92语法中的非等值连接
select *
  from emp e
  join salgrade sg
    on e.sal between sg.losal and sg.hisal;
--left outer join,会把左表中的全部数据正常显示，右表没有对应的数据，直接显示即可
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from emp e left join dept d on e.deptno = d.deptno;
--right outer join，会把右表中的全部数据正常显示，左表没有对应的数据，直接显示即可
select * from emp e right outer join dept d on e.deptno = d.deptno;
--full outer join,相当于左外连接和右外连接的和合体
select * from emp e full outer join dept d on e.deptno = d.deptno;
--inner join等同于join
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;
--using，除了可以使用on作为连接条件以外，也可以使用using作为连接条件,此时，连接条件的列不再归属于任何一张表
select * from emp e join dept d using (deptno); --此时deptno这个字段不再归属于任意一张表
select * from emp e join dept d on e.deptno = d.deptno;
--检索雇员名字，所在单位，薪水等级
select e.name, d.loc, sg.grade
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade sg
    on e.sal between sg.losal and sg.hisal;

子查询

/*
子查询：嵌套在其他sql语句中的完整sql语句，可以称之为子查询
分类：
    单行子查询
    多行子查询
*/

--有多少人的薪水是在整个雇员的平均薪水之上的
--1、先求平均薪水
select avg(e.sal) from emp e;
--2、把所有人的薪水与平均薪水比较
select * from emp e where e.sal > (select avg(e.sal) from emp e); 
--查询在雇员中那些人是经理人
--1、查询所有经理人的编号
select distinct e.mgr from emp e;
--2、在雇员表中过滤这些编号即可 
select * from emp e where empno in (select distinct e.mgr from emp e);

--每个部门平均薪水的等级
--1、先求出部门的平均薪水
select e.deptno,avg(e.sal)from emp e group by e.deptno;
--2、跟薪水登记表做关联，求出平均薪水的等级
select t.deptno,sg.grade from salgrade sg
  join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
    on t.vsal between sg.losal and sg.hisal;

--求平均薪水最高的部门编号
--1、求部门的平均薪水
select e.deptno, avg(e.sal)from emp e group by e.deptno;
--2、求平均薪水最高的部门的薪水
select max(t.vsal)from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t;
--3、求部门编号
select t.deptno
  from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
 where t.vsal =
       (select max(t.vsal)
          from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);
--求部门平均的薪水等级
--1、求部门 每个人的薪水等级
select e.deptno,sg.grade from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
--2、按照部门求平均等级
select t.deptno, avg(t.grade)
  from (select e.deptno, sg.grade
          from emp e
          join salgrade sg
            on e.sal between sg.losal and sg.hisal) t
 group by t.deptno;
 --限制输出，limit用在MySQL中但不用于Oracle中
 --在Oracle中如果需要使用限制输出和分页的功能的话，必须要使用rownum
 --但是rownum不能直接使用，需要
--求薪水最高的前五名雇员
select * from (select * from emp e order by e.sal desc) t1 where rownum <= 5
--求薪水最高的第6到第10名雇员
--1、
select t1.*,rownum from (select * from emp e order by e.sal desc) t1 where rownum <= 10;
--2、
select *
  from (select t1.*, rownum rn
          from (select * from emp e order by e.sal desc) t1
         where rownum <= 10) t
 where t.rn > 5
   and t.rn <= 10;

行转列

create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-10','胜');
insert into tmp values('2005-05-10','负');
insert into tmp values('2005-05-10','负');


/*
          胜 负
2005-05-09 2 2
2005-05-10 1 2
*/

select rq,decode(shengfu,'胜',1),decode(shengfu,'负',2) from tmp;
select rq,
       count(decode(shengfu, '胜', 1)) 胜,
       count(decode(shengfu, '负', 2)) 负
  from tmp
 group by rq;





create table STUDENT_SCORE
(
  name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

/*
4.1得到类似下面的结果
姓名   语文  数学  英语

王五    89    56    89

*/


--decode
select ss.name,
       max(decode(ss.subject, '语文', ss.score))语文,
       max(decode(ss.subject, '数学', ss.score))数学,
       max(decode(ss.subject, '英语', ss.score))英语
  from student_score ss group by ss.name;

--case when 
select ss.name,
       max(case ss.subject
         when '语文' then
          ss.score
       end) 语文,
       max(case ss.subject
         when '数学' then
          ss.score
       end) 数学,
       max(case ss.subject
         when '英语' then
          ss.score
       end) 英语
  from student_score ss group by ss.name;

--join 
select ss.name,ss.score from student_score ss where ss.subject='语文';
select ss.name,ss.score from student_score ss where ss.subject='数学';
select ss.name,ss.score from student_score ss where ss.subject='英语';

select ss01.name 姓名,ss01.score 语文, ss02.score 数学, ss03.score 英语
  from (select ss.name, ss.score
          from student_score ss
         where ss.subject = '语文') ss01
  join (select ss.name, ss.score
          from student_score ss
         where ss.subject = '数学') ss02
    on ss01.name = ss02.name
  join (select ss.name, ss.score
          from student_score ss
         where ss.subject = '英语') ss03
    on ss01.name = ss03.name;