oracleサブクエリ
11015 ワード
サブクエリ
サブクエリとは、実際にはクエリのネストである、与えられた条件に基づいて必要なデータを直接検出できない場合にサブクエリを用いる必要がある.
サブクエリが表示される場所
サブクエリが最も多い場所: where from
1 whereの後、条件として判断する.
7369と同じ部門のすべての人を検索
2 selectの後、selectの内容として使用する.
各部門の人数を問い合わせる
3 having後にパケットデータ判定の条件として用いることができる
平均賃金が20組未満の平均賃金を照会するクラス
4 fromの後に結果セットとして使用します.
最高賃金の5人の成績を調べる
サブクエリの使用方法
1サブクエリは単一の行を返します
会社の最低賃金の従業員の名前、クラス情報の照会を要求する
第一歩:会社の最低賃金を統計する
ステップ2:上には、1行の単列データが返され、数値です.さらにwhere条件判断を行う
会社が最も早く雇った従業員を照会する
雇用が最も早いのは従業員の日付が最も小さいに違いないので、MIN()関数を使用して完了します.
WHERE句で直接使用できるすべての単一列のデータを返します.
2サブクエリは1行の複数列を返します
SMITH部門と同じ職階のすべての従業員の番号と名前の情報を検索
まずSMITHの部署とポジションを調べるべきです
このとき、1行2列のデータ情報が返され、比較するときに同時に満たされる
3サブクエリは複数行の複数列を返します
WHERE句には、IN、ANY、ALLの3つの主要な演算子があります.
c 001カリキュラムがc 002カリキュラムより成績の高いすべての学生の学号を検索します.
4existsは、サブクエリが少なくとも1行のデータ を返すかどうかを調べるために使用される.サブクエリは、実際にはデータを返すのではなく、値TrueおよびFalse を返す.
c 001カリキュラムがc 002カリキュラムより成績の高いすべての学生の学号を検索します.
補足:ソート関数
rank() over指定条件検出後の進行順位 この関数を使用して、成績が同じ2人は並列で、次の学生は占めた順位を空けます.
各科の成績の上位3名の記録を調べる
rank over()を使用すると、空の値が最大になり、ソートフィールドがnullの場合、nullフィールドが一番前に並び、ソート結果に影響を与える可能性があります.
dense_rank() over
rank()overとの違いは、2人の学生の成績が並んだ後、次の学生が占める順位を空けていないことだ.
row_number
この関数は並列するかどうかを考慮する必要はなく,条件によってクエリされた数値が同じであっても連続してランク付けされる恐れがある.
サブクエリとは、実際にはクエリのネストである、与えられた条件に基づいて必要なデータを直接検出できない場合にサブクエリを用いる必要がある.
サブクエリが表示される場所
サブクエリが最も多い場所:
1 whereの後、条件として判断する.
7369と同じ部門のすべての人を検索
SQL> select empno,ename,deptno
2 from emp
3 where deptno=
4 (select deptno from emp where empno=7369);
EMPNO ENAME DEPTNO
---------- -------------------- ----------
7777 S_HH%GGH 20
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 202 selectの後、selectの内容として使用する.
各部門の人数を問い合わせる
SQL> select
2 (select count(1) from emp where deptno=10) "10",
3 (select count(1) from emp where deptno=20) "20",
4 (select count(1) from emp where deptno=30) "30"
5 from dual;
10 20 30
---------- ---------- ----------
3 6 63 having後にパケットデータ判定の条件として用いることができる
平均賃金が20組未満の平均賃金を照会するクラス
SQL> select deptno, avg(sal)
2 from emp
3 group by deptno
4 having avg(sal) >
5 (select avg(sal) from emp where deptno = 20);
DEPTNO AVG(SAL)
---------- ----------
10 2916.666674 fromの後に結果セットとして使用します.
最高賃金の5人の成績を調べる
SQL> select *
2 from (select sal from emp where sal > 0 order by sal desc)
3 where rownum < 6;
SAL
----------
5000
3000
3000
2975
2850サブクエリの使用方法
1サブクエリは単一の行を返します
会社の最低賃金の従業員の名前、クラス情報の照会を要求する
第一歩:会社の最低賃金を統計する
SQL> select min(sal) from emp;
MIN(SAL)
----------
800ステップ2:上には、1行の単列データが返され、数値です.さらにwhere条件判断を行う
SQL> select ename,job,deptno from emp
2 where sal=(select min(sal) from emp);
ENAME JOB DEPTNO
-------------------- ------------------ ----------
SMITH CLERK 20会社が最も早く雇った従業員を照会する
雇用が最も早いのは従業員の日付が最も小さいに違いないので、MIN()関数を使用して完了します.
SQL> select min(hiredate) from emp;
MIN(HIREDATE)
--------------
17-12 -80WHERE句で直接使用できるすべての単一列のデータを返します.
SQL> select empno,ename,hiredate from emp
2 where hiredate=(select min(hiredate) from emp);
EMPNO ENAME HIREDATE
---------- -------------------- --------------
7369 SMITH 17-12 -802サブクエリは1行の複数列を返します
SMITH部門と同じ職階のすべての従業員の番号と名前の情報を検索
まずSMITHの部署とポジションを調べるべきです
SQL> select deptno,job from emp
2 where ename='SMITH';
DEPTNO JOB
---------- ------------------
20 CLERKこのとき、1行2列のデータ情報が返され、比較するときに同時に満たされる
SQL> select empno,ename,deptno,job from emp
2 where (deptno,job)=(
3 select deptno,job from emp where ename='SMITH');
EMPNO ENAME DEPTNO JOB
---------- -------------------- ---------- ------------------
7777 S_HH%GGH 20 CLERK
7369 SMITH 20 CLERK
7876 ADAMS 20 CLERK3サブクエリは複数行の複数列を返します
WHERE句には、IN、ANY、ALLの3つの主要な演算子があります.
SQL> select * from sc;
SNO CNO SCORE
-------------------- -------------------- ----------
s001 c001 78.9
s002 c001 80.9
s003 c001 81.9
s004 c001 60.9
s001 c002 82.9
s002 c002 72.9
s003 c002 81.9
s001 c003 59c 001カリキュラムがc 002カリキュラムより成績の高いすべての学生の学号を検索します.
SQL> select sno
2 from sc t1
3 where t1.cno='c001'
4 and sno in
5 (select sno from sc where
6 cno='c002' and t1.score>score and t1.sno=sno);
SNO
--------------------
s0024exists
c 001カリキュラムがc 002カリキュラムより成績の高いすべての学生の学号を検索します.
SQL> select sno
2 from sc t1
3 where t1.cno='c001'
4 and exists(
5 select * from sc where
6 cno='c002' and t1.sno=sno and t1.score>score);
SNO
--------------------
s002補足:ソート関数
SQL> select * from sc;
SNO CNO SCORE
-------------------- -------------------- ----------
s001 c001 78.9
s002 c001 80.9
s003 c001 81.9
s004 c001 60.9
s001 c002 82.9
s002 c002 72.9
s003 c002 81.9
s001 c003 59
s004 c002 81.9rank() over
各科の成績の上位3名の記録を調べる
SQL> select cno,sno,score,
2 rank() over(partition by cno order by score desc) ranks from sc;
CNO SNO SCORE RANKS
-------------------- -------------------- ---------- ----------
c001 s003 81.9 1
c001 s002 80.9 2
c001 s001 78.9 3
c001 s004 60.9 4
c002 s001 82.9 1
c002 s003 81.9 2
c002 s004 81.9 2
c002 s002 72.9 4
c003 s001 59 1SQL> select cno,sno,score from
2 (select cno,sno,score,rank() over(partition by cno order by score desc) rank from sc)
3 where rank<4;
CNO SNO SCORE
-------------------- -------------------- ----------
c001 s003 81.9
c001 s002 80.9
c001 s001 78.9
c002 s001 82.9
c002 s003 81.9
c002 s004 81.9
c003 s001 59rank over()を使用すると、空の値が最大になり、ソートフィールドがnullの場合、nullフィールドが一番前に並び、ソート結果に影響を与える可能性があります.
SQL> select deptno,comm,
2 dense_rank() over(partition by deptno order by comm desc) ranks
3 from emp
4 where deptno=30;
DEPTNO COMM RANKS
---------- ---------- ----------
30 1
30 1
30 1400 2
30 500 3
30 300 4
30 0 5SQL> select deptno,comm,
2 dense_rank() over(partition by deptno order by comm desc nulls last) ranks
3 from emp
4 where deptno=30;
DEPTNO COMM RANKS
---------- ---------- ----------
30 1400 1
30 500 2
30 300 3
30 0 4
30 5
30 5dense_rank() over
rank()overとの違いは、2人の学生の成績が並んだ後、次の学生が占める順位を空けていないことだ.
SQL> select cno,sno,score,
2 dense_rank() over(partition by cno order by score desc) ranks from sc;
CNO SNO SCORE RANKS
-------------------- -------------------- ---------- ----------
c001 s003 81.9 1
c001 s002 80.9 2
c001 s001 78.9 3
c001 s004 60.9 4
c002 s001 82.9 1
c002 s003 81.9 2
c002 s004 81.9 2
c002 s002 72.9 3
c003 s001 59 1SQL> select cno,sno,score from
2 (select cno,sno,score,dense_rank() over(partition by cno order by score desc) rank from sc)
3 where rank<4;
CNO SNO SCORE
-------------------- -------------------- ----------
c001 s003 81.9
c001 s002 80.9
c001 s001 78.9
c002 s001 82.9
c002 s003 81.9
c002 s004 81.9
c002 s002 72.9
c003 s001 59row_number
この関数は並列するかどうかを考慮する必要はなく,条件によってクエリされた数値が同じであっても連続してランク付けされる恐れがある.
SQL> select cno,sno,score,
2 row_number() over(partition by cno order by score desc) ranks from sc;
CNO SNO SCORE RANKS
-------------------- -------------------- ---------- ----------
c001 s003 81.9 1
c001 s002 80.9 2
c001 s001 78.9 3
c001 s004 60.9 4
c002 s001 82.9 1
c002 s003 81.9 2
c002 s004 81.9 3
c002 s002 72.9 4
c003 s001 59 1SQL> select cno,sno,score from
2 (select cno,sno,score,row_number() over(partition by cno order by score desc) ranks from sc)
3 where ranks<4;
CNO SNO SCORE
-------------------- -------------------- ----------
c001 s003 81.9
c001 s002 80.9
c001 s001 78.9
c002 s001 82.9
c002 s003 81.9
c002 s004 81.9
c003 s001 59