複雜查詢—子查詢

路飛小朋友發表於2020-11-16

基本概念

子查詢定義

子查詢指的是在嵌入在其他 sql 語句中的 select 語句，又稱為內部查詢、巢狀查詢
子查詢，又叫內部查詢，相對於內部查詢，包含內部查詢的就稱為外部查詢
內部查詢只能是 select 語句且可以包括任何子句
外部查詢可以是 select，insert，update，delete，set 或 do

子查詢的位置

select 中，from 後，where 中
group by 和order by 中也可以，但無實用意義

子查詢分類

單行單列：返回的是一個具體列的內容，可以理解為一個單值資料
多行單列：返回多行記錄之中同一列的內容，相當於給出了一個操作範圍
單行多列：返回一行資料中多個列的內容
多行多列：查詢返回的結果是一張臨時表

單行單列（where中）

顯示與smith同一部門的所有員工資訊，不包括smith

mysql> select * from emp where deptno=(select deptno from emp where ename='smith') and ename <> 'smith';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | ford  | analyst | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

多行單列（where中）

in 關鍵字

//查詢和部門10的工作相同的僱員的資訊，但是不含10自己的  
mysql> select * from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
+-------+-------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

all 關鍵字

<>all：等價於not in（但是=all並不等價於in）
>all：比子查詢中最大的值還要大（還包含了>=all）
<all：比子查詢中最小的值還要小（還包含了<=all）

//顯示工資比部門30的所有員工的工資高的員工資訊
mysql> select * from emp where sal > all(select sal from emp where deptno=30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.45 sec)

mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

any關鍵字

=any：表示與子查詢中的每個元素進行比較，功能與in類似（然而<>any不等價於not in）
>any：比子查詢中返回結果的最小的要大（還包含了>=any）
<any：比子查詢中返回結果的最大的要小（還包含了<=any）

//顯示工資比部門30的任一員工的工資高的員工資訊
mysql> select * from emp where sal > any(select sal from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

mysql> select * from emp where sal > (select min(sal) from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

單行多列（where中）

查詢和 allen 部門崗位完全相同的僱員資訊

//標準寫法
mysql> select * from emp where (deptno,job) = (select deptno,job from emp where ename='allen');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

//自己寫的 比較low
mysql> select * from emp where deptno = (select deptno from emp where ename='allen') and job = (select job from emp where ename='allen');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.01 sec)

在from中使用子查詢

核心思想就是把子查詢的結果當作一個臨時表來使用

顯示高於自己部門平均工資的員工的資訊

//先獲取各個部門的平均工資
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2443.750000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

//將上一步的結果當作臨時表 進行多表查詢
mysql> select * from emp,(select deptno,avg(sal) from emp group by deptno) temp where emp.deptno = temp.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | deptno | avg(sal)    |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | 2443.750000 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | 1566.666667 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | 1566.666667 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | 2443.750000 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | 1566.666667 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | 1566.666667 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | 2916.666667 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | 2443.750000 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | 2916.666667 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |     30 | 1566.666667 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | 1566.666667 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | 2443.750000 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | 2916.666667 |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
13 rows in set (0.00 sec)

//篩選結果

Laravel Query Builder 複雜查詢案例：子查詢實現分割槽查詢 partition by
2018-11-27
LaravelUI
SQL 複雜查詢
2022-03-14
SQL
SQL複雜查詢
2017-04-07
SQL
Solr複雜查詢一：函式查詢
2020-04-17
Solr函式
oracle表複雜查詢
2020-04-04
Oracle
Oracle複雜查詢（三）
2017-03-06
Oracle
子查詢-表子查詢
2017-11-14
JPA的多表複雜查詢
2019-08-03
查詢(3)--雜湊表(雜湊查詢)
2014-02-22
SQL查詢的：子查詢和多表查詢
2020-11-18
SQL
SQL學習(三）複雜查詢
2020-12-20
SQL
Laravel使用MongoDB複雜的查詢
2021-01-14
LaravelMongoDB
SQL複雜查詢多表連線
2015-04-28
SQL
複雜查詢語句的使用
2010-07-11
mysql-分組查詢-子查詢-連線查詢-組合查詢
2020-12-22
MySql
MySQL聯結查詢和子查詢
2018-02-24
MySql
select查詢之三：子查詢
2016-10-08
Elasticsearch複合查詢——boosting查詢
2021-11-17
Elasticsearch
查詢與排序01,線性查詢,時間複雜度,演算法
2014-07-26
排序時間複雜度演算法
MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]
2024-03-10
MySql筆記
相關子查詢&非相關子查詢概念
2011-09-08
MySQL子查詢
2019-11-29
MySql
子串查詢
2020-11-04
使用子查詢
2017-09-27
MYsql 子查詢
2018-05-15
MySql
oracle子查詢
2014-02-18
Oracle
查詢子串
2015-03-23
子查詢分解
2008-05-20
11子查詢
2011-01-11
sql子查詢
2007-04-02
SQL
informix子查詢
2012-03-31
ORM
微服務複雜查詢之快取策略
2021-03-15
微服務快取
老司機使用 Redis 快取複雜查詢
2016-04-19
Redis快取
Vert.x Future 解決複雜查詢
2017-12-13
Hibernate對於複雜查詢好用嗎?
2009-04-01
複雜查詢還是直接寫sql吧
2024-08-08
SQL
MySQL之連線查詢和子查詢
2021-09-04
MySql
es的複雜查詢測試，使用jest的dsl工具寫查詢語句
2020-12-19

複雜查詢—子查詢

基本概念

單行單列（where中）

多行單列（where中）

單行多列（where中）

在from中使用子查詢

相關文章