Oracle の高度なクエリについて話しましょう (詳細な例)

この記事では、グループ クエリ、複数テーブル クエリ、サブクエリなど、Oracle の高度なクエリに関する関連知識を提供します。皆様のお役に立てれば幸いです。

#Oracle Advanced Query

Advanced クエリは、グループ クエリ、複数テーブル クエリなど、データベースの開発プロセスで広く使用されています。およびサブクエリ Oracle の高度なクエリを 3 つの側面から紹介します。

グループ化クエリ

グループ化クエリは、特定のルールに従ってグループ化することです。グループ化後、データは集計されます。集計関数を使用する必要がありますが、グループ化は必要ありません集計関数を使用するために必要です。グループ化のキーワードは group by です。

一般的に使用される集計関数には、maximum max()、minimum min()、average avg()、sum sum()、統計数値 count() が含まれます。

count 関数の使用 Null 値列に名前を付けると自動的に無視されます

nvl 関数は、count が null 値を自動的に無視するのを防ぐことができます。その関数は、comm が空の場合に 0 を返すことです。空ではないため、統計の合計に含まれます。

#サブクエリによるグループ化

集計関数に含まれていない選択リスト内のすべての列をグループに含める必要があります。 by in句。

単一列のグループ化

各部門の平均給与を検索し、部門番号と部門の平均給与を表示します。

select deptno,avg(sal) from emp group by deptno order by deptno

複数列のグループ化

部門およびさまざまな役職ごとの従業員の給与総額に関する統計

select detpno,job,sum(sal) from emp group by deptno,job order by deptno

フィルター グループ

having 句の使用

where と being の違い

Where 句では集計関数を使用できません。最初にフィルターしてから group

• having sub 文中で集計関数を使用して、最初にグループ化し、次にフィルタリングすることができます。

注: SQL 最適化の観点からは、where を使用するようにしてください。グループ化されたレコードの数が減り、効率が向上します。 平均給与が 2000 を超える部門を検索

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

where 句では集計関数を使用できないため、エラーが報告される場合は、「xxx 句で十分です」に変更します。

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

グループ クエリで order by

を使用して各部門の平均給与を検索し、部門番号と部門の平均給与を次の順序で表示します。給料の昇順。

select deptno,avg(sal) from emp group by deptno order by avg(sal)

列の別名で並べ替えることもできます

select deptno,avg(sal) avgsal from emp group by deptno order by avgsal

列のシリアル番号で並べ替えることもできます。平均給与は 2 番目の列です

select deptno,avg(sal) from emp group by deptno order by 2

desc を降順に追加するだけです

select deptno,avg(sal) from emp group by deptno order by 2 desc

グループ化関数のネスト

部門の平均給与の最大値を求める

select max(avg(sal)) from emp group by deptno

グループ別ステートメントの機能強化

グループ別ステートメント レポート機能で主に使用されます

各部門、さまざまなポジションを設置し、合計給与、部門の概要、概要を確認します。

ロールアップ関数

select deptno,job,sum(sal) from emp group by rollup(deptno,job)

を使用して、表示形式を設定できます。deptno でのブレークは、同じ部門番号のみ 1 つ表示、スキップ 1 は、異なる部門番号の間に空白行があることを意味します。

レポートの表示を改善します

タイトル、ページ番号などを追加します。

ttitle 列 15 'マイ レポート'col 35 sql.pno

タイトルを設定し、レポートを表示するには 15 列を空にし、ページ番号を表示するには 35 列を空にします

col deptno 見出し部門番号

col jobHeadingposition

col sum(sal)Heading Total給与

上記 3 行の列ヘッダーを設定

deptno で中断 1

表示形式を設定します。同じ部門番号のみを表示します。 異なる部門番号の間に空行を入れて 1 つ表示します。

これらの設定を SQL ファイルに保存します (ANSI エンコードに変更する必要があることに注意してください。そうしないと文字化けが発生します)設定は無効になります）を取得し、getコマンドで読み込んで実行します。クエリ ステートメントを再度実行し、次のレポートを取得します。複数のページがある場合、それらを美しく表示するために、1 ページにさらに多くの行を表示するように設定できます。たとえば、各ページに 100 行を表示するように設定します。 set pagesize 100

#多テーブル クエリ

##上記の例では、単一のテーブルのデータをすべてクエリします。複数のテーブルのデータをクエリすることから始めましょう。 デカルト集合を回避するには、有効な接続条件を where に追加できますが、実際の状況では、デカルト完全集合の使用は避けるべきです。

等值连接

实例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称

需要查询员工表和部门表，通过部门号进行等值连接查询，where xxx=xxx

select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno

不等值连接

示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水级别

需要查询员工表和薪水等级表，通过薪水等级上下限进行不等值连接查询。where xxx between xxx and xxx，注意：小值在between前面，大值在between后面

select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

外连接

示例：按部门统计员工人数，要求显示：部门号，部门名称，人数

需要查询部门表和员工表

以下是通过等值连接的方式查询，虽然总人数没有问题，但是少了一个部门，因为一个部门没有员工。

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname

外连接一般通过join来实现，一张图看懂SQL的各种join用法。

使用join语句重新实现示例功能

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname

自连接

示例：查询员工姓名和员工的老板姓名

核心：通过别名，将同一张表视为多张表

select e.ename 员工姓名,b.ename 老板姓名 from emp e, emp b where e.mgr=b.empno

这种方式会产生笛卡尔集，不适合大表的查询，可以使用层次查询来解决。connect by xxx start with xxx

level是层次查询提供的伪列，需要显示使用才会查询这个伪列。

select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1

子查询

子查询语法中的小括号

必须要有小括号，书写风格要清晰如下图所示：

示例：查询比FORD工资高的员工

select * from emp where sal > (select sal from emp where ename='FORD')

可以使用子查询的位置

select，from，where，having

select位置的子查询只能是单行子查询，也就是只能返回一条结果

select empno,ename,sal,(select job from emp where empno='7839') job from emp

having位置的子查询

示例：查找部门平均工资大于30号部门最大工资的部门号及其平均工资

select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30)

from位置的子查询

查询结果也可以当成表

select * from (select empno,ename,sal from emp)

增加1列年薪，使用sal*12得到年薪

select * from (select empno,ename,sal,sal*12 annsal from emp)

主查询和子查询可以不是同一张表

示例：查询部门名称是SALES的员工信息

使用子查询的方式：

select * from emp where deptno=(select deptno from dept where dname='SALES')

使用多表查询的方式:

select e.* from emp e, dept d where e.deptno=d.deptno and d.dname='SALES'

子查询的排序

一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

示例：找到员工表中工资最高的前三名，如下格式：

rownum，行号，oracle自动为表分配的伪列。

• 行号永远按照默认的顺序生成
• 行号只能使用,>=

select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<h3><strong>子查询执行顺序</strong></h3><p>一般先执行子查询，再执行主查询；单相关子查询例外。</p><p>相关子查询示例：找到员工表中薪水大于本部门平均薪水的员工</p><pre class="brush:php;toolbar:false">select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno)

单行子查询和多行子查询

单行子查询返回一个结果，只能使用单行操作符；

多行子查询返回多个结果，只能使用多行操作符。

单行操作符：

操作符	含义
=	等于
>	大于
>=	大于等于
	小于
	小于等于
	不等于

多行操作符：

操作符	含义
in	等于列表中的任何一个
any	和子查询返回的任意一个值比较
all	和子查询返回的左右值比较

单行子查询示例1：

查询员工信息，要求：

职位与7566员工一样，薪水大于7782员工的薪水

select * from emp where job=(select job from emp where empno=7566) and sal >（select sal from emp where empno=7782）

单行子查询示例2：

查询最低工资大于20号部门最低工资的部门号和部门的最低工资

select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20)

多行子查询示例：

查询部门名称是SALES和ACCOUNTING的员工信息

使用多行子查询的方式：

select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING')

使用多表查询的方式：

select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING')

子查询中的空值问题

查询不是老板的员工

注意：当子查询中包含null值时，不要使用not in。

a not in (10,20,null)

a != 10 and a != 20 and a != null, a != null 永远不成立，所以整个表达式永远返回false。

可以在子查询中把null值过滤掉再使用not in。

select * from emp where empno not in (select mgr from emp where mgr is not null)

推荐教程：《Oracle教程》

以上がOracle の高度なクエリについて話しましょう (詳細な例)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。